array with sql JOIN??

[YellowCheese]

Hi,

I have two sql tables which i have joined using JOIN.

The tables and fields of each :

Items
id, name, price

Presents
id, name, type

the select statement is:

$sql = "SELECT
*
FROM
items
JOIN
presents
ON
items.name=presents.name ORDER BY items.id ASC";

$present_type = mysql_query($sql)or die(mysql_error());

now i have used following mysql_fetch_assoc:

$row = mysql_fetch_assoc($present_type)

When i do $row['id']; I get the id from presents table but i want to fetch the id from the items table, do i use $row['items.id']; ??

I think i may be wrong, can somebody please help me.

Thanks in advance for any help.

[BIOSTALL]

Try amending your SQL statement as follows:

$sql = "SELECT
    `items`.`id`
FROM
    `items`
JOIN`
    `presents
ON
    `items`.`name`=`presents`.`name` 
ORDER BY 
    `items`.`id` ASC";

Then you'll be able to access the item ID by using $row['id'].

Hope that helps 🙂

[YellowCheese]

thanks for that, but will that still allow me to fetch other fields from the table and not just items.id?

the statement populates a dropdown list with a list of the items.

[BIOSTALL]

You can either get just the fields that you need as follows:

$sql = "SELECT
    `items`.`id`,
    `items`.`name`,
    ...
    ...
FROM
    `items`
JOIN`
    `presents
ON
    `items`.`name`=`presents`.`name` 
ORDER BY 
    `items`.`id` ASC";

Or you you can get all the fields and refer to item.id as something else:

$sql = "SELECT
    *,
    `items`.`id` AS item_id
FROM
    `items`
JOIN`
    `presents
ON
    `items`.`name`=`presents`.`name` 
ORDER BY 
    `items`.`id` ASC";

With this second option you can then get the item ID by using $row['item_id'].

Let me know if you need any more help. Good luck!