How do I use JOIN to query mysql from PHP?

Question

I have this code on my game script (php). This checks if the player has a vehicle equipped, and that it still exists. My code:

if ($rs[vehicle] != 0) {
    $sql_result2 = mysql_query("SELECT * FROM vehicles WHERE id='$rs[vehicle]'", $db);
     if (mysql_num_rows($sql_result2) == 0) { mysql_query("UPDATE mobsters SET vehicle = 0 WHERE id ='$id'"); }
}

I want to edit this code so that if it does exist, it will also check the db_vehicles table and find out details of the said vehicle (name, speed etc). A sloppy way of doing that is:

if ($rs[vehicle] != 0) {
    $sql_result2 = mysql_query("SELECT * FROM vehicles WHERE id='$rs[vehicle]'", $db);
     if (mysql_num_rows($sql_result2) == 0) {
         mysql_query("UPDATE mobsters SET vehicle = 0 WHERE id ='$id'"); 
         } else {
         $ = mysql_fetch_array($sql_result2); 
         $sql_result3 = mysql_query("SELECT * FROM db_vehicles WHERE id='$[type]'", $db);
         $ = mysql_fetch_array($sql_result3);
         $name = $[name]; $speed = $[speed];
         }
     }

Obviously I'd like to do this with a JOIN to use as few queries as possible. What kind of join should I use?

Your code is prone to SQL injections; please use PDO and prepared queries instead. — Kevin Ji, Nov 27 '11 at 00:21

score 1 · Answer 1 · edited May 23 '17 at 12:03

1

Your code should use only one select statement and another for update, this is the select with join:

$sql_result3 = mysql_query("Select dbv.Name, dbv.speed, dbv.type 
                            from db_Vehicles dbv inner join vehicles v 
                             on v.id = dbv.id  WHERE dbv.id='$rs2[type]'"
                            , $db);

BTW as @mc10 said your code is prone to sql injection so you will find alot of stuff here in SO about how to prevent it like This and This

edited May 23 '17 at 12:03

Community

1
1

answered Nov 27 '11 at 00:33

Mahmoud Gamal

78,257
17
139
164

and then `if (mysql_num_rows($sql_result3) == 0) { mysql_query("UPDATE mobsters SET vehicle = 0 WHERE id ='$id'"); }` ?? – user1022585 Nov 27 '11 at 00:35
Yes, you can't do the two things in one statement so you need two distinct sql statements. – Mahmoud Gamal Nov 27 '11 at 00:39

How do I use JOIN to query mysql from PHP?

1 Answers1