Mysql select multiple queries inside eachother

Question

I am having some trouble with selecting data from my database using data from a previous select. What I want to do is. First I let mysql select all vechiles from my database from the table vechiles. I echo them and inside the while loop I try to select the picture for the vechile from a diffrent table by select the picture with the vechile_id:

Now my first question is. Is this the right way to do it? I assume there must be a more neat way but I cant find how.

    <?php
            $sql = "SELECT id, brand FROM vechiles";
            $result = $conn->query($sql);

            if ($result->num_rows > 0) {
                while($row = $result->fetch_assoc()) {

                    $sql1 = "SELECT * FROM fotos WHERE vechiles_id =". $row['id'];
                    $result1 = $conn->query($sql1);

                    echo '<li><p class="title">'. $row['brand'] .'</p>
                            <p class="type">type: 2387</p>
                            <p class="ez">ez: 1987</p>';

                            if ($result1->num_rows > 0) {
                             // output data of each row
                            while($row1 = $result1->fetch_assoc()) {
                                echo '<img src="admin/'. $row1['url'] .'" atl="tractor 1"/>';
                            }}else{
                                echo "there are no pictures";
                            }

                            echo '<div class="info">
                            <p class="prijs">Prijs: 1800,- EX BTW</p>
                            <p class="msg"> Prijzen onder voorgehoud</p>
                            </div>
                            <a class="button" href="#">Meer info</a></li>';
                }
            } else {
                echo "0 results";
            }
            $conn->close();
        ?>

Noe the above example works fine but I want to only select the first picture instead of all uploaded pictures. So to this code:

 $sql1 = "SELECT * FROM fotos WHERE voertuig_id =". $row['id'];

I added

 $sql1 = "SELECT TOP 1 * FROM fotos WHERE voertuig_id =". $row['id'];

And I also tried

 $sql1 = "SELECT * FROM fotos WHERE voertuig_id =". $row['id'] . "LIMIT 1";

But when I do that it suddenly says there are no pictures. What am I doing wrong!

appreciate all the help!

You should do it in one query, with a join

pala_
– pala_

2015-05-01 10:16:00 +00:00
Commented May 1, 2015 at 10:16 — pala_
– pala_, Commented May 1, 2015 at 10:16

pala_ · Accepted Answer · 2015-05-01 10:54:06Z

1

You don't need to loop and execute another query for each vehicle, you can do it with a simple join.

select *
  from vehicles v
    left join fotos f
      on f.voertuig_id = v.id;

If there is no matching photo for that vehicle, it will have a null value in your returned array.

This will change your php to something like this:

$last_vehicle = null;
if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    if($row['brand'] != $last_vehicle) {
      if($last_vehicle != null) {
        echo '<div class="info">
                <p class="prijs">Prijs: 1800,- EX BTW</p>
                <p class="msg"> Prijzen onder voorgehoud</p>
              </div>
              <a class="button" href="#">Meer info</a></li>';
       }
       echo '<li><p class="title">'. $row['brand'] .'</p>
             <p class="type">type: 2387</p>
             <p class="ez">ez: 1987</p>';
             $last_vehicle = $row['brand'];                        
      if ($row['url'] != null) {
        echo '<img src="admin/'. $row['url'] .'" atl="tractor 1"/>';
      }
      else {
        echo "there are no pictures";
      }       
    }
  } 
}
else {
  echo "0 results";
}

edited May 1, 2015 at 10:54

answered May 1, 2015 at 10:16

pala_

9,0101 gold badge18 silver badges32 bronze badges

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

8 Comments

Merijndk Over a year ago

Aah thanx. Im trying it out now. But now instead of selecting all vechiles and print them out (4 vechiles) it now prints out the vechile for every picture. So when voertuig_id has 10 fotos. it will echo's the vechile 10 times.

Merijndk Over a year ago

So what I want to do is select al vechiles (4) from the vechile table, echo them. and select the first picture that belongs to every vechile

pala_ Over a year ago

just track the last vehicle you printed, and only print it again if it changes

pala_ Over a year ago

@Merijndk I've added some php that should be close to what you need -- might have some syntax errors - sorry

verhie Over a year ago

If you only want one picture per vehicle, you can do a join on a subquery: select * from vehicles v left join (select * from fotos where voertuig_id = v.id [order by whatever*] limit 1) f

|

Collectives™ on Stack Overflow

Mysql select multiple queries inside eachother

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related