Getting a shared parameter in a JOIN query

Question

Using PHP, I the following query:

"SELECT * 
 FROM `items` 
      JOIN `categories` 
         ON `items`.`category`=`categories`.`cat3` 
 WHERE `items`.`user_id` = '".$UID."'"

I'm trying to access the items.id field from the associative array. Problem is, $res['id'] seems to be giving me the id for the categories table.

How can I alter the code to get the ID from the items table?

**By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. — Andy Lester, Sep 27 '13 at 22:19
For future code: You don't need to quote/backtick your column & table names. They only add visual clutter and are just one more way for you to make syntax errors. The only reason you need them is if you have a name that is a reserved word, or you have embedded spaces or punctuation in them, and those are terrible practices to avoid anyway. — Andy Lester, Sep 27 '13 at 22:20
@AndyLester thanks but I took the precautions in `$UID` to make sure the query is safe. I prefer the backticks as I generally write very linear queries. — I wrestled a bear once., Oct 01 '13 at 08:46

score 1 · Accepted Answer · answered Sep 27 '13 at 21:50

1

Add an extra column in your sql statement, eg.

SELECT *,
       items.id AS `itemID`
FROM   ....

and access it like,

$res['itemID']

answered Sep 27 '13 at 21:50

John Woo

258,903
69
498
492

score 0 · Answer 2 · answered Sep 27 '13 at 22:00

You are selecting every column from both tables items and category. This will give you trouble accessing the columns that have the same name on both tables.

In these cases you should always use aliases in your select statement.

SELECT `items`.`id` as item_id,...,`categories`.`id`,...
 FROM `items` 
      JOIN `categories` 
         ON `items`.`category`=`categories`.`cat3` 
 WHERE `items`.`user_id` = '".$UID."'"

You just have to add the rest of the columns you need in the ... for each table.

If you have other columns with same name, don't forget to create aliases for them too.

Getting a shared parameter in a JOIN query

2 Answers2