Marc DG Marc DG - 6 months ago 15
PHP Question

PHP Myqsl PDO code efficiency - Foreach loop within a foreach loop

Hello to all!
I am new to coding and will be studding in class in next September. Looking forward to that....:-)

I am making a modification page for my data base. It's composed of inputs and a drop down list to modify the content of the DB. At least for now...till I learn more.

I am coding for my self and was wondering if the following was the correct way to do this? In my mind it's not, because the inner query gets executed every time the outer loop goes through...But it work!?!?

It's the only way I could find to make the inner FOREACH loop ($familylist) work with the Mysql query. If the query for the inner loop is outside the outer loop ($plantList)...it does not work. The first drop down list gets populated with the content but the following rows do not, at least only with the first option, it does not get populated with the content from the query.

Any help is welcome and appreciated!

<?php //more code here....

$plantQuery = "SELECT id, genre, espece, famille FROM plante ORDER BY genre";

$plantList = $dbconnect->query ($plantQuery);
?>

<table>
<thead>
<tr>
<th>ID</th>
<th>GENRE</th>
<th>ESP√ąCE</th>
<th>FAMILLE</th>
</tr>
</thead>
<tbody>
<?php foreach ($plantList->fetchAll(PDO::FETCH_ASSOC) as $plant) : ?>
<form method="post">
<tr>
<td><input name="id" value="<?php echo $plant["id"] ;?>" readonly></td>
<td><input name="genre" value="<?php echo $plant["genre"] ;?>"></td>
<td><input name="espece" value="<?php echo $plant["espece"] ;?>"></td>
<td><select name="familleList" >
<option value="0" >Choisir une famille</option>
<?php
$familyQuery = "SELECT id, famille FROM famille ORDER BY id ASC";
$familyList = $dbconnect->query ($familyQuery);
?>
<?php foreach ($familyList->fetchAll(PDO::FETCH_ASSOC) as $family):?>
<option value="<?php echo $family["id"] ;?>" <?php if ($plant["famille"] <> 0 && $plant["famille"] == $family["id"] ) {echo "selected"; }?>><?php echo $family["id"] . " - " . $family["famille"] ;?></option>
<?php endforeach ;?>
</select>
</td>
<td><button name="modifier" type="submit">Modifier</button></td>
<td><button name="supprimer" type="submit">Supprimer</button></td>
</tr>
</form>
<?php endforeach ;?>
</tbody>
</table>

Answer

Instead of calling the database multiple times, for the same data inside the loop. A simple solution would be to call it once. At the top of the file do something like this.

$families = $familyList->fetchAll(PDO::FETCH_ASSOC);

And then you can chance your foreach loop to.

foreach ($families as $family)

This will make the query execute once, and therefor avoid multiple queries to the database. And loop over the already fetched data in the loops.

Comments