PHP Mysql where clause using foreign key

Question

I have this select statement in my php application:

$result = mysqli_query($con,"SELECT * FROM apps WHERE device.DeviceName = $os ");

I'm trying to select all "apps" that have the device name of the variable "$os" all the apps are in one table with a device id, and the device table holds the device name...

Even though you are using mysqli, you are still vulnerable to SQL injection. See [How can I prevent SQL injection in PHP?](http://stackoverflow.com/q/60174) — Madara's Ghost, Mar 15 '14 at 10:37

domdomcodecode · Accepted Answer · 2014-03-15T10:45:21.083

0

Join your device table, otherwise you can't use it in the WHERE clause

SELECT *
FROM apps A
    INNER JOIN device D
        ON D.id = A.deviceID
WHERE D.DeviceName = '$os'

As the comments suggests, this is vulnerable to injection though, so be careful.

edited Mar 15 '14 at 10:45

answered Mar 15 '14 at 10:38

domdomcodecode

2,355
4
19
27

`SELECT * FROM apps A INNER JOIN device D D.DeviceID = A.DeviceID WHERE D.DeviceName = $os` I've used this as they're the exact names for my columns but it still doesn't show anything – Carla Dessi Mar 15 '14 at 10:44
Sorry, left out the `ON` there. – domdomcodecode Mar 15 '14 at 10:45
It's ok, I've fixed it, turns out the $os needed to be in speech marks – Carla Dessi Mar 15 '14 at 10:48

score 0 · Answer 2 · edited May 23 '17 at 12:27

You are looking for JOIN. Take a look at this article for some more help.

Essentially a JOIN sticks two tables together (you can have many joins) based on the criteria you specify. The most often used criteria is the foreign key.

The join you would be looking for is this:

SELECT * FROM apps 
INNER JOIN device ON apps.deviceID = device.id 
WHERE device.DeviceName = $os

Also, you are open to SQL injection vulnerabilities by putting the parameter directly in the query. Read this great stack overflow question.

PHP Mysql where clause using foreign key

2 Answers2