PHP left join not displaying results

Question

I am trying to left join to tables in PHP. I am a total noob to left join and I can't figure out what I'm doing wrong!

$value=$_GET['value'];
$storeid=$_GET['store'];
$id=$_GET['id'];
$latitude=$_GET['lat'];
$longitude=$_GET['long'];

$result = mysqli_query($con,"SELECT carlist.id, carlist.vin, link_qr.qr, link_qr.vin
FROM link_qr, carlist LEFT JOIN link_qr.vin ON carlist.vin
WHERE qr="$value";");

while($row = mysqli_fetch_array($result)) {
    echo $row['id']; 
    echo $row['vin'];
    echo $row['qr'];
}

Here is the table structure

Table: link_qr

id------vin---------qr------webid---------other

Table: carlist

id---stknum---vin----vt----stat---other---store_id---web_code---qrcode

When all done I would like to have the following.

I would like to join the carlist and the link_qr where the vins are equal to each other and then I need it to return the carlist id where that vin is equal to qr.

Here are the errors I'm getting:

**Notice: Undefined index: store in /api/app_request/left_join.php on line 13
Notice: Undefined index: id in /api/app_request/left_join.php on line 14
Notice: Undefined index: lat in /api/app_request/left_join.php on line 15
Notice: Undefined index: long in /api/app_request/left_join.php on line 16
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in api/app_request/left_join.php on line 22**

Could you please describe the problem you are having? Simply stating "its not working" does not give people a lot of information to go on. It also implies lazy debugging on your part. Do you have a SQL error? Are the results returned by MySQL not what you expect? Is your PHP code wrong? — thatidiotguy
– thatidiotguy, Commented Feb 17, 2015 at 16:08
SELECT carlist.id, carlist.vin, link_qr.qr, link_qr.vin FROM link_qr left join carlist on (link_qr.vin = carlist.vin and link_qr.vin="$value"); i guess this is what you needed — ɹɐqʞɐ zoɹǝɟ
– ɹɐqʞɐ zoɹǝɟ, Commented Feb 17, 2015 at 16:10
put single quotes to the $value, and if you still get blank screen then print the query and test it manually — ɹɐqʞɐ zoɹǝɟ
– ɹɐqʞɐ zoɹǝɟ, Commented Feb 17, 2015 at 16:29
WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation to accomplish this because you will create severe SQL injection bugs. — tadman
– tadman, Commented Feb 17, 2015 at 16:43

Navarr · Accepted Answer · 2025-01-27 20:51:35Z

1

There are a couple things to note:

First, your SQL query is incorrect:

SELECT carlist.id, carlist.vin, link_qr.qr, link_qr.vin 
FROM carlist 
LEFT JOIN linkqr ON linkqr.vin=carlist.vin 
WHERE qr="$value"; -- Do not do this, it is insecure.

Should be the correct format so long as those tables and columns exist. Secondly, however, you should not be querying a database with an unescaped value. This leads to SQL Injection. More appropriately you could write your query like:

$query = <<<SQL
SELECT carlist.id, carlist.vin, link_qr.qr, link_qr.vin 
FROM carlist 
LEFT JOIN linkqr ON linkqr.vin=carlist.vin 
WHERE qr=?
SQL;

$stmt = mysqli_prepare($query);
mysqli_bind_param($stmt, "s", $value); // this sets the ? in the sql query to $value
mysqli_execute($stmt);
$result = mysqli_get_result($stmt);
while($row = mysqli_fetch_array($result)) {
    echo $row['id']; 
    echo $row['vin'];
    echo $row['qr'];
}

edited Jan 27 at 20:51

answered Feb 17, 2015 at 16:49

Navarr

3,7947 gold badges35 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tadman Over a year ago

Since you've converted to placeholders here, which is great, you really should omit the now weaponized version of the query.

HugoNumber1 · Accepted Answer · 2015-02-17 16:38:31Z

0

Why not do:

"SELECT C.PrimaryId, Field, AnotherField
 FROM tablename AS C
 LEFT JOIN tablename AS L ON C.matching_id = T.matching_id
 WHERE tablename.fieldname = :fieldname

?

answered Feb 17, 2015 at 16:38

HugoNumber1

3310 bronze badges

4 Comments

tadman Over a year ago

If you're using PDO, which is really a good idea, that would be the best way. mysqli doesn't support named placeholders, only uses ? instead.

HugoNumber1 Over a year ago

Ah didn't see it was mysqli...not my area of expertise there

Kelly Hansen Over a year ago

@tadman im not familiar to the code above but i will try and give it a shot.

HugoNumber1 Over a year ago

@KellyHansen It uses PDO which boosts the security of your sites data and by comparison, minimizes code, in order to do it, you'd have to re-do your whole site around MYSQL and PDO

Collectives™ on Stack Overflow

PHP left join not displaying results

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related