xtiansimon — 2010-01-16T15:55:06-05:00 — #1
Help me write a MySQL SELECT statement to grab something I wish to get into a dynamic page.
This is a simple dynamic web page list of parts (about 20) with categories (about 3). It works as you would expect. When the list is formatted as a web page, there is a simple test if the item from the parts list is under a new category. If yes, then print the category title before continuing to print the list.
This works well so far. However I'm missing a functionality I wish to have. I wish my list to print the category name even if there are no parts using under the category.
I've used in_array() function to check if a category is grabbed with my SELECT statement when there are no members; it is not. I thought to reverse the order of the SELECT statement (below), but made no difference.
Two MySQL tables=
list_items (id, item, part_number, doc_url, cat_id)
categories (id, cat_name, cat_desc)
list_items.cat_id relates to categories.id
In my PHP script is this query=
$myResult = mysql_query('SELECT list_items., categories. FROM list_items, categories WHERE list_items.cat_id=categories.id ORDER BY list_items.cat_id', $connectID) or die ("Unable to select from database");
As I said, i reversed the order in this SELECT statement, but it made no difference.
r937 — 2010-01-16T16:10:43-05:00 — #2
this is an inner join, using implicit join syntax (tables listed in the FROM clause, join conditions specified in the WHERE clause)
you need to change it to an outer join, using explicit JOIN syntax
ON categories.id = list_items.cat_id
xtiansimon — 2010-01-21T01:50:28-05:00 — #3
That worked to order the list but there is a serious disconnect with the PHP output. The ID values of the list items is supposed to be mapped to each item. Instead the category IDs are getting mapped to the items in the list.
I add the contents of the SELECT to the mysql_fetch_array() function and use a while() loop to write the TDs of the table. The fetch array is held in a variable I'll call $row. When I use $row['id'] to grab the ID of the current item from the list_items table and assign this to the listed xhtml item, instead I'm getting the category ID! I tried to use MySQL AS statement to remap the categories.id to ref_cat_id like this:
SELECT list_items.*, categories.*, categories.id AS ref_cat_id FROM categories LEFT JOIN list_items ON list_items.cat_id=categories.id ORDER BY list_items.cat_id
Thinking that this would ensure the row array would not confuse the two different id fields. It didn't work fully.
xtiansimon — 2010-01-25T23:52:09-05:00 — #4
I fiddled with it and had time to look up the AS statements usage. But the real clincher was to look at the results at the MySQL command line. This showed me I was using the AS statement incorrectly. I made the change and i'm aces. Thanks r9! You got me going in the right direction. Chris