Is a join the right choice?

[sinrise]

I have a DB with 2 tables: ‘products’ and ‘categories.’ The products table contains a category_id field, which I use as my foreign key. I use the categories table to populate a combo box on the edit product page. The combo box displays correctly with the proper choice selected depending on what category id is stored in the products table.

All the other create, read, update, delete functionality works for both tables. I use an inner join in the query in the get_all_products function and a while loop to display the products in an HTML table.

The problem I’m having is when I delete a category, and then refresh the products display page, any product record that referred to the category I just deleted no longer shows up in the list of products. It’s not deleted from the db because I can still see in phpmyadmin, it just doesn’t appear in the list.

Is that because of the join? I figure the join is only returning records which have matching category_ids. Is there a better join to use or, for such a simple db, should I be using joins at all or just creating functions to handle displaying the category name in the products display?

Obviously, I’m still learning php and mysql, so I apologize if this question has been asked a million times. I just don’t know what to search for in the forums to see if there’s already an answer. I appreciate any help. Thanks!

[php]
function get_all_products() {
global $connection;
$query = "SELECT * ";
$query .= "FROM products ";
$query .= "INNER JOIN categories ON products.category_id=categories.category_id ";
$result_set = mysql_query($query, $connection);
return $result_set;
}

function products_list() {
$product_set = get_all_products();
while ($product = mysql_fetch_array($product_set)) {
echo “

”;
echo “{$product[“category”]}”;
echo “{$product[“size”]}”;
echo “{$product[“color”]}”;
echo “{$product[“description”]}”;
echo “{$product[“history”]}”;
echo “{$product[“itemnum”]}”;
echo “{$product[“isFeatured”]}”;
echo “<a class=“editButton” href=“edit-product.php?id={$product[“product_id”]}”>edit<a class=“deleteButton” href=“delete-product.php?id={$product[“product_id”]}”>delete”;
echo “”;
}
}
[/php]

[sinrise]

As usual, I figured out a fix less than an hour of posting the question.

So, I changed INNER JOIN to LEFT JOIN and is displays all the records from the products table regardless of whether the category_id fields exist in both tables or not.

Of course, I don’t know if this is really good programming or not but my DB CRUD works 100% now.