CODEWITHSUNDEEP
sql
user961882
user961882

Reputation: 237

sql query simplify?

I have to pull data from a database table based on the result from another table. First I'll mention the tables and what they're for.

db_vehicles - This table is a database of all the vehicles. It contains name, shortname, speed, and other info. (Eg, name=Sports Car, shortname=sports, speed=10)

Sample;

id        name       shortname      speed    
 1    'Sports Car'    'sports'        7

items_vehicles = This table keeps a record of the players cars, each column being a shortname for each car in db_vehicles. (eg trucks=5, sports=8, bikes=3)

Sample;

id    player    bike    sports    truck
1       1        5        7         0

Basically what I need to do is check that the player actually owns the vehicle he has 'equipped' ($vehicle). This checks the items_vehicles table, but to find the column in that table it needs to check the db_vehicles table first, to get the 'shortname'.

Heres the code I currently use:

$vehicle = "Sports Car"; // example

if ($vehicle != "") { //if the players has a vehicle 'equipped'

    $sql_result2 = mysql_query("SELECT shortname FROM db_vehicles WHERE name='$vehicle'", $db);  
    $rs2 = mysql_fetch_array($sql_result2);

    //we have the shortname, now check to see if he has any

    $sql_result3 = mysql_query("SELECT * FROM items_vehicles WHERE player='$id'", $db);
    $rs3 = mysql_fetch_array($sql_result3);

    // now if he has none, make $vehicle empty

    if ($rs3[$rs2[shortname]] < 1) { 
        $vehicle="";
        }

Is there a better way of doing this? Maybe with one query using subqueries or anything?

The reason I need one is because I do this with four times on each page load, it checks vehicles, weapon, equipment, and melee. (Its for a game script)

update

I tried using something like this, but doesnt work:

 $sql_result2 = mysql_query("SELECT * FROM items_vehicles WHERE items_vehicles.(SELECT shortname FROM db_vehicles WHERE name='$vehicle')=db_vehicles.shortname WHERE items_vehicles.player='$id'", $db);

Upvotes: 0

Views: 108

Answers (1)

competent_tech
competent_tech

Reputation: 44921

I'm not sure about the exact database that you are using, but a general SQL solution is an inner join (this will be using SQL Server syntax):

SELECT * 
  FROM items_vehicles
 WHERE player='$id'
   AND 1 >= CASE (SELECT shortname 
                   FROM db_vehicles
                  WHERE name='$vehicle') 
                WHEN 'sports' THEN sports
                WHEN 'bike' THEN bike
                // add more here
                ELSE 0
           END

Upvotes: 1

Related Questions