print list + categories w/ SELECT statement BUT, can't keep category w/o members

[xtian]

I have a SELECT statement that works only when the second table has a matching member.

 

I've used in_array() function to check if a category in the first table is grabbed with my SELECT statement when there are no members in the related second table; it is not. I thought to reverse the order of the SELECT statement (below), but this made no difference.

 

What it is I wish to achieve=

This is a simple dynamic web page displaying a 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.

 

Overview of what i got to work with=

mysql server v5.1.41

two 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

 

$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.

 

 

[fenway]

You need a LEFT JOIN, not an (implicit) inner join:

 

SELECT list_items.*, categories.* FROM categories LEFT JOIN list_items ON list_items.cat_id=categories.id ORDER BY list_items.cat_id

 

 

[xtian]

That worked to order the list but there is a serious disconnect with the PHP output. 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.

 

Less important to the above is a strange effect. Not sure if its a problem yet, but it is strange. Each of the empty categories (I made two empty for my test) have what appears to be an empty row, because at what would be the end of the row are the two administration links, edit and delete. If you roll over the edit and delete links at the end of these blank rows they show an ID in the URL mapped to the category. But these ID numbers are wrong anyway since each list item should have a unique ID and right now they are using all the same category IDs as described above.

 

I will read more about the join and its left, right versions. But, right now I only have a cookbook and the official tar MySQL refman. I'm looking for a good MySQL reference. Until then I appreciate any advise here given that leads to a solution.

 

Xtian

[fenway]

I don't know anything about php output... does the query produce the expected result?

[xtian]

Its really straight forward. The php code goes through an associative array of the contents of the SELECT statement. The first query I got from the forum. The second is simply the mysql_fetch_array () function that accepts the variable that accepted the results of the SELECT statement, and instruction to generate an associative (keyword) array. This function is set to a second variable. The while loop goes through the second variable each time for each row.

 

The trouble is the row array is not returning the ID value of the list item, and instead gives the category ID even though I used the AS statement to change the ID to ref_cat_id. I understand this to change the keyword the array uses to label that column of data. $row['ref_cat_id'] should access the categories ID, and $row['id'] should access the list item's ID column keyword.

 

Thus I have something like this in PHP:

$myResults = mysql_query (the mysql SELECT statement........) /* the whole of the list_items table joined with categories*/

$row = mysql_fetch_array ($MyResults, associative array) /*an array that returns each row of the above */

$row['keyword'] /* access to an individual field in a given row; where keyword is the name of a column in both the list_items and categories tables */

 

Chris

[fenway]

Well, then maybe someone else will step in -- if you can't confirm that the mysql query return the correct recordset, I'm afraid I can't help you in terms of the php.

[xtian]

I got it! Thanks Fen, You got me going in the right direction. I had time to look up the AS statement and realized I was using it differently then it was described. Then I looked at how you typed the select statement and it made me think to test my query at the MySQL command prompt before writing the PHP--and your response about the correct results did light a fire under my drawers. I saw that the AS statement was not working as I wanted and I rewrote it to get the unique ID number I needed.

 

Again thanks for getting me going in the right direction.

Chris