CODEWITHSUNDEEP
phpsqlmysqli
Ali
Ali

Reputation: 27

Is there a more efficient way to express this query

I have a query for my mysql database but I just want to know if there is more of a simpler way of achieving something, here is my code:

$sql="SELECT value 
  FROM drivers 
  WHERE drivers_id = '$driver1' OR drivers_id =  '$driver2' OR drivers_id = '$driver3'"; 

$result = mysql_query($sql);

$i = 1; 
while ($row = mysql_fetch_assoc($result)) { 
  ${'value'.$i} = $row['value']; 
  $i++; 
}

This is how I want my code as it is shorter, but the problem I am having is that I need to know that where the $driver1 variable is found in the database the data retrieved for that is placed inside the value1 variable, and the retrieved information for $driver2 is placed inside $value2. However it grabs the data from the database in the order that it comes across the matches. I don't want to have to write 3 different queries for this because I am sure it can be done in one.

Upvotes: 0

Views: 81

Answers (2)

Ben Mosher
Ben Mosher

Reputation: 13381

$sql="SELECT drivers_id, value FROM drivers WHERE drivers_id IN ('$driver1','$driver2','$driver3')"; 

$result=mysql_query($sql);
while($row = mysql_fetch_assoc($result)) { 
  ${'value_for_'.$row['drivers_id']}=$row['value']; 
}

Boom.

Though personally, instead of on-the-fly variables, I'd use an array w/ the id's as keys:

...
$resultArray = array();
while($row = mysql_fetch_assoc($result)) { 
  $resultArray[$row['drivers_id']]=$row['value']; 
}

Upvotes: 3

jches
jches

Reputation: 4527

If you want to change the order of the results, you can add an order by clause to the query:

$sql = "SELECT drivers_id, value FROM drivers WHERE drivers_id IN ('$driver1','$driver2','$driver3') ORDER BY drivers_id";
...

Upvotes: 0

Related Questions