Page 1 of 1

MySQL left join confusion

Posted: Sat Apr 04, 2009 9:53 am
by ben.artiss
Hi all,

I'm having some trouble joining a value between two tables. I got my head around the LEFT JOIN x ON y=z a few weeks ago but I've come back totally confused! What I have is two tables, menu_cat and menu_items. Each row in menu_items has a cat_id which relates to the cat_id in menu_cat, where I am trying to get the title.

I swear this worked before but it's not now. What I want is to join the menu_cat.title to menu_items.cat_id, so instead of getting a numeric value I get a string. This is the query I'm using:

Code: Select all

$sql ="SELECT `menu_items`.`title`,`menu_items`.`cat_id`,`menu_items`.`description`,`menu_items`.`cost`,`menu_cat`.`title` FROM `menu_items` LEFT JOIN `menu_cat` ON `menu_items`.`cat_id`=`menu_cat`.`cat_id` WHERE `menu_cat`.`status`!=0"
But when I loop through using mysql_fetch_assoc, $row['cat_id'] is still numeric. Can anybody spot what I'm doing wrong?

Thanks, Ben

Re: MySQL left join confusion

Posted: Sat Apr 04, 2009 10:02 am
by requinix
The cat_id is always going to be numeric.

JOINs will add fields to a query. Look for the title, not the cat_id.

Re: MySQL left join confusion

Posted: Sat Apr 04, 2009 10:13 am
by ben.artiss
Genius thanks man, but one last thing, I accessed that through $row['title'] but I also have $row['title'] in menu_items table (and am therefore losing the most important part!), is this where I would use SELECT AS or possible JOIN AS? I can feel a lot of mySQL tutorials coming...