MYSQL query optimisation

Question

the problem I am having I will describe it as best as I can....The user chooses three selections and the image appears on the screen, and the selections they made are sent and stored in the database, the PHP function below then finds the image for the selection and returns them as variables so the latest selection they made and the images corresponding to the selection is still displayed in the placeholders after the page is refreshed, the SQL code I have below I want to incorporate into one Query but I need it so where the data for $driver1 is found it is placed into the $image1 variable and the same for $driver2 into $image2, and $driver3 to $img3 so I can return them to the page and echo the correct images in the correct placeholders. The images for the selections they made need to appear in the placeholders that they originally appeared in. It works fine as it is now, but I am sure that the SQL querys can be shortened to one? to save lines of code.

The code I have posted below is the main important bits so you can hopefully understand what I am asking.

Any help would be great, thank you.

//HTML CODE
<?php
list($img1, $img2, $img3) = checkteam();
?>

<img onclick="return removedriver1(this)" id="advert" src="images/delete.gif"  border="0"/>
    <img id="placeholder1" src="<?php echo "$img1";?>" alt="" />
<img onclick="return removedriver2(this)" id="advert src="images/delete.gif" border="0"/>
    <img id="placeholder2" src="<?php echo "$img2";?>" alt="" />
<img onclick="return removedriver3(this)" id="advert" src="images/delete.gif" border="0"/>
        <img id="placeholder3" src="<?php echo "$img3";?>" alt="" />


//PHP CODE
function checkteam(){

$sql="SELECT image FROM drivers WHERE drivers_id = '$driver1'"; 
                $result=mysql_query($sql);
                $row = mysql_fetch_array($result);

                $image1=$row[image];

$sql="SELECT image FROM drivers WHERE drivers_id = '$driver2'"; 
                $result=mysql_query($sql);
                $row = mysql_fetch_array($result);

                $image2=$row[image];

$sql="SELECT image FROM drivers WHERE drivers_id = '$driver3'"; 
                $result=mysql_query($sql);
                $row = mysql_fetch_array($result);

                $image3=$row[image];

return array ($image1, $image2, $image3);
}

Vinay Pai · Accepted Answer · 2011-08-23 00:32:29Z

2

The best way to do this in my opinion is to stick them all into an associative array:

$sql = "SELECT drivers_id, image FROM drivers WHERE drivers_id IN ($driver1, $driver2, $driver3)";
$result = mysql_query($sql);
$images = array();
while (($row = mysql_fetch_assoc($result)) {
    $images[$row["drivers_id"]] = $row["image"];
}

You can then print out the appropriate image with $images[$driver1] etc.

answered Aug 23, 2011 at 0:32

Vinay Pai

7,8171 gold badge20 silver badges28 bronze badges

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

4 Comments

Ali Over a year ago

So I can reference the array with the $images[$driver1]?? and it will echo the data retrieved for that $driver1 variable?. How does this work exactly? sorry to be a pest.

Vinay Pai Over a year ago

I promise I'm not trying to be condescending, but arrays are a pretty basic concept in PHP (and programming in general). If you don't understand how they work you should definitely find an introductory tutorial that covers stuff like before trying to work on a real project or it will be an uphill battle.

Ali Over a year ago

haha no I understand how arrays work, but I thought what you where trying to tell me something different, what your explaining to me will not work because the values will always change, so when they come back as $images[4], $images[7], $images[5], I can place them accordingly on the page, but what happens when someone changes there selection? it will no longer be the same because there is 24 different selections they can make and can re-enter there selection at any point.

Ali Over a year ago

but I could use the array and a if condition to place them into the array where I want them.

IOrlandoni · Accepted Answer · 2011-08-23 01:12:48Z

0

You could do one query only:

$sql = "SELECT drivers_id id, image FROM drivers WHERE drivers_id in ($driver1, $driver2, $driver3)";

You would need to myqsl_fetch_array three times, of course.

while ($row = mysql_fetch){
    if ($driver1 == $row['id'])
     $image1 = $row['image'];
    else if ($driver2 == $row['id'])
     $image2 = $row['image'];
    else if ($driver3 == $row['id'])
     $image3 = $row['image'];
}

edited Aug 23, 2011 at 1:12

answered Aug 23, 2011 at 0:20

IOrlandoni

1,82813 silver badges31 bronze badges

2 Comments

Ali Over a year ago

So even if I do the mysql_fetch_array three times, how do I make sure that the data for $driver1 is placed into $image1? because I have tried it with the query you said but it retrieves the data in the order that the matches are found. Thanks

Ali Over a year ago

Apart from the double == signs below the If's, should just be one. Nice one.

buritos · Accepted Answer · 2011-08-23 00:27:47Z

0

select drivers_id, image from drivers where drivers_id in ($driver1, $driver2, $driver3)

or

select drivers_id, image from drivers where drivers_id = $driver1 or drivers_id =  $driver2 or drivers_id = $driver3

loop through the results and match drivers_id to echo the correct image in the correct placeholder.

answered Aug 23, 2011 at 0:27

buritos

5962 silver badges9 bronze badges

1 Comment

Ali Over a year ago

Yeah this would work, so just loop through and match the drivers_id to the $driver1 variable and so on?? thanks :).

Collectives™ on Stack Overflow

MYSQL query optimisation

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related