How to extract CATEGORY from database

[boidski]

Hi guys, I have a problem.

 

This code pulls out the product name from the dbase:

 

    $sql2 = "SELECT `products_name` FROM `products_description` WHERE products_id = '$id' LIMIT 1";
$sql2_result = mysql_query($sql2,$connection) or die("Couldn't execute query.");
$row2 = mysql_fetch_array($sql2_result);
   $name = $row2["0"];

// -- OUTPUT --

<NAME><?php echo $name;?></NAME>

 

This works fine. I am stumped as to how to get the category name, for a given product

 

// -- XML OUTPUT --

<NAME><?php echo $name;?></NAME>
<CATEGORY><?php echo cat;?></CATEGORY>

 

Can anyone give me any solution to this?

 

Here is what I have...

 

	$id = $row["products_id"];
$cat_id = $row["categories_id"];


$sql_cat = "SELECT `products_to_categories.products_id`, `products_to_categories.categories_id` FROM `products_to_categories` WHERE products_to_categories.products_id = '$id' AND products_to_categories.categories_id = '$cat_id LIMIT 1";
$cat_id_2_cat_name_transform = "SELECT `categories_name` FROM `categories_description` WHERE categories_id = '$cat_id' LIMIT 1";
$sql_cat_result = mysql_query($cat_id_2_cat_name_transform,$connection) or die("Couldn't execute query.");
$row7 = mysql_fetch_array($sql_cat_result);
   $cat = $row7["0"];    

 

All that does is give a blank line where the result should be:

<CATEGORY><?php echo $cat; ?></CATEGORY>

 

Any suggestions? I am really stumped, hopefully some one can see an error where I can't, or can provide a simpler solution.

 

 

Thank you.

 

Brian

[boidski]

I am still stuck on this.

 

Is there a more appropriate section to post under? I need this line of code so I can submit to search comparison sites. Until I do that, I am stuck.

 

Thank you.

[boidski]

Trying a different query string gives the same result: NOTHING.

 

$cid = $row["categories_id"];
$lan_id = $row["language_id"];

// -- EXTRACT CATEGORY NAME FOR A PRODUCT

  $cat_query = "SELECT `categories_name` FROM   `categories_description` WHERE categories_id = '$cid' AND language_id = '$lan_id'";

$sql5_result = mysql_query($cat_query,$connection) or die("Couldn't execute query.");
$row5 = mysql_fetch_array($sql5_result);
$cat = $row5["0"];

 

please someone help me, I am tearing my hair out over this. Someone MUST know hoe to formulate this query..

 

Please, please help.

[bluepony]

Here's the SQL statement to do it. You'll need to include the language id in there too if you use more than one language.

 

 

"SELECT `categories_description`.`categories_name`
FROM `categories_description`
LEFT  OUTER  JOIN `products_to_categories` ON `products_to_categories`.`categories_id` = `categories_description`.`categories_id`
LEFT OUTER JOIN `products` ON `products`.`products_id` = `products_to_categories`.`products_id`
WHERE `products`.`products_id` = '$id' LIMIT 1";