Having issues with JOIN ... anyone care to help?

[Jax2]

Hi all,

 

I have 2 tables set up, one is for recipes the other is for categories. The pertinent information is stored as such:

 

Table Recipes:

 

recipeID - Unique - Auto increment

recipe_name

 

Table Categories:

 

ID - unique, auto inc.

category_name

 

Now, I am trying to do an sql query to show the recipes.  I need to show: Recipe ID - Recipe Name - Category Name, in that order.  Here is what I have come up with (and is not working):

 

$sql = "SELECT 
".$prefix."recipes.*,
".$prefix."categories.ID,
".$prefix."categories.category_name
FROM ".$prefix."recipes  
JOIN ".$prefix."recipes 
ON ".$prefix."recipes.category = ".$prefix."categories.ID 
order by ".$prefix."recipes.recipeID asc";
$result = mysql_query($sql, $db);
if (!$result) {
echo "There were no records";
die();
};

It is not returning any results.

 

It should be showing me:

 

Recipe ID, Recipe Name, Category Name

1, French Toast, Breakfast

2, French Toast 2, Breakfast

 

Can't figure out what I did wrong! Sorry for all the $prefix 's ... it's a pain, but needed

 

[PFMaBiSmAd]

if (!$result) {

That conditional test only checks of the query executed or not. It does not test if there were any matching rows.

 

Start by finding out why the query failed (for all we know, the $db connection does not exist) -

if(!$result){
// the query failed, for debugging purposes, echo the query and mysql_error() to find out why
echo "The query failed: $sql<br />Because: " . mysql_error();
die();
}

[Jax2]

Okay, makes sense. I added that and this is what I am seeing:

 

The query failed: SELECT ts_recipes.*, ts_categories.ID, ts_categories.category_name FROM ts_recipes JOIN ts_recipes ON ts_recipes.category = ts_categories.ID order by ts_recipes.recipeID asc
Because: Not unique table/alias: 'ts_recipes'

 

Which I guess i don't understand. I'm looking right at the "ts_recipes" table in phpMyAdmin

[PFMaBiSmAd]

FROM ts_recipes JOIN ts_recipes

 

You are joining the table to itself, rather than to the second table name.

[Jax2]

Duh.  :-\

 

JOIN ts_categories fixed that, now it works perfectly. Thank you so much!