PHP PDO query within while loop only returns first row

[johnc81]

Hello,

I am building a script to be able to update item data, so I want to be able to display the list of items. Within the while loop to display the list, I have some dropdown select menus which the user can select from. These have their own queries and while loops. I had this working with mysqli, but now I move to PDO, I can only get it to display the first item in the table.

Please can someone have a look at my code and see where I am going wrong?

echo '<div class="divTable">
			<div class="divTableBody">';
				$stmt = $pdo->query("
				SELECT
					itemID
					,itemNumber
					,categoryID
					,itemDescription
					,colourID
				FROM 
					tbl_items
				WHERE 
					itemActive = 1
				ORDER BY
					categoryID
					,itemDescription ASC");
				$rowcount = $stmt->rowCount();
				if($rowcount === 0){
					echo '<div class="divTableRow" style="background-color: '.$colourrow1.';">
						<div class="div100px">0 Records</div>
						<div class="div150px"></div>
						<div class="div200px"></div>
						<div class="div600px"></div>
						<div class="div250px"></div>
						<div class="div100px"></div>
						<div class="div100px"></div>
					</div>';
				} else {
					while ($row = $stmt->fetch()) {
						$colour == $colourrow1 ? $colour=$colourrow2 : $colour=$colourrow1;
						echo '<form name="managerecords" enctype="multipart/data" method="POST">
							<div class="divTableRow" style="background-color: '.$colour.';">
								<div class="div100px">'.$row['itemID'].'</div>
								<div class="div150px"><input id="input150px" type="text" name="itemNumber" value="'.$row['itemNumber'].'" required /></div>
								<div class="div200px">
									<select name="categoryID" class="select200px" required>
										<option name="" value="">Select...</option>';
										$stmt = $pdo->query("SELECT categoryID, categoryName FROM tbl_categories WHERE categoryActive = 1 ORDER BY categoryID ASC");
										while ($row2 = $stmt->fetch()) {
											echo '<option value="'.$row2['categoryID'].'" '.($row['categoryID']==$row2['categoryID'] ? 'selected' : '').'>'.$row2['categoryName'].'</option>'; 
										}
									echo '</select>
								</div>
								<div class="div600px"><input id="input600px" type="text" name="itemDescription" value="'.$row['itemDescription'].'" required /></div>
								<div class="div250px">
									<select name="colourID" class="select250px" required>
										<option name="" value="">Select...</option>';
										$stmt = $pdo->query("SELECT colourID, colourName FROM tbl_colours WHERE colourActive = 1 ORDER BY colourName ASC");
										while ($row2 = $stmt->fetch()) {
											echo '<option value="'.$row2['colourID'].'" '.($row['colourID']==$row2['colourID'] ? 'selected' : '').'>'.$row2['colourName'].'</option>'; 
										}
									echo '</select>
								</div>
								<div class="div100px"><input id="submitbtn" type="submit" name="update" value="Update" /></div>
								<div class="div100px"><input id="submitbtn" type="submit" name="archive" value="Archive" /></div>
							</div>
							<input type="hidden" name="itemID" value="'.$row['itemID'].'" />
						</form>';
					}
				}
			echo '</div>
		</div>';

I know it is something to do with the dropdown select menus for category and colour because when I comment those out, the full item list is displayed.

Many thanks for your time,

J

[Barand]

You create a statement object $stmt and loop through the results.

While you are looping through these results you create another statement object $stmt thus overwriting the one you are currently processing.

Running any queries inside a loop is a bad idea to start with, this takes it to another level.

[ginerjm]

1 - never put a query inside of a loop.

2 - you are doing #1 and thus are wiping out your $stmt contents from the first quer by using it for your second query.

[johnc81]

Thank you for the responses.

If I am not supposed to put the query inside the loop, how do I display the list of values to the user and also display the value they have selected for each row from the main query? What is best practice for how to do this?

Do I just execute the SQL outside the loop and then have nested while loops?

Thanks,

J

Edited December 3, 2022 by johnc81

[Barand]

Create the option lists before the loop

$options = "<option value='1'>One</option>
            <option value='2'>Two</option>
            <option value='3'>Three</option>";

while ... {
    ...
    ...
    echo "<select ... >
            $options
          </select>";
    ...
}

 

[johnc81]

Hi,

The options are pulled from a DB table and the user can create their own. I really don't want to have to hard code them in the HTML

I just tried putting the SQL queries outside the main while loop but I only get the values for the select boxes for the first row, all the others default to "Select...."

Thanks,

J

[Barand]

3 minutes ago, johnc81 said:

I really don't want to have to hard code them in the HTML

No one is suggesting you hard code them, just that they should not be created inside the loop.

Create them as you do now, but before the loop, and store the options to use inside the loop.

[johnc81]

Hi,

I am trying to find an example online and failing. I can put the query and while loop before the main query but I have 2 parts I am struggling with.

Firstly, I have no idea how to generate a single $options variable with all the options from a while loop.

*Edit* I have done this part:

echo '<div class="divTable">
			<div class="divTableBody">';
				$stmt = $pdo->query("
				SELECT
					itemID
					,itemNumber
					,categoryID
					,itemDescription
					,colourID
				FROM 
					tbl_items
				WHERE 
					itemActive = 1
				ORDER BY
					categoryID
					,itemDescription ASC");
				
				$categoryoptions = '';
				$stmt2 = $pdo->query("SELECT categoryID, categoryName FROM tbl_categories WHERE categoryActive = 1 ORDER BY categoryID ASC");
				while ($row2 = $stmt2->fetch()) {
					$categoryoptions .=  '<option value="'.$row2['categoryID'].'">'.$row2['categoryName'].'</option>'; 
				}
				
				$colouroptions = '';
				$stmt3 = $pdo->query("SELECT colourID, colourName FROM tbl_colours WHERE colourActive = 1 ORDER BY colourName ASC");
				while ($row3 = $stmt3->fetch()) {
					$colouroptions .=  '<option value="'.$row3['colourID'].'">'.$row3['colourName'].'</option>'; 
				}
				
				$rowcount = $stmt->rowCount();
				if($rowcount === 0){
					echo '<div class="divTableRow" style="background-color: '.$colourrow1.';">
						<div class="div100px">0 Records</div>
						<div class="div150px"></div>
						<div class="div200px"></div>
						<div class="div600px"></div>
						<div class="div250px"></div>
						<div class="div100px"></div>
						<div class="div100px"></div>
					</div>';
				} else {
					while ($row = $stmt->fetch()) {
						$colour == $colourrow1 ? $colour=$colourrow2 : $colour=$colourrow1;
						echo '<form name="managerecords" enctype="multipart/data" method="POST">
							<div class="divTableRow" style="background-color: '.$colour.';">
								<div class="div100px">'.$row['itemID'].'</div>
								<div class="div150px"><input id="input150px" type="text" name="itemNumber" value="'.$row['itemNumber'].'" required /></div>
								<div class="div200px">
									<select name="categoryID" class="select200px" required>
										<option name="" value="">Select...</option>';
										echo $categoryoptions; 
									echo '</select>
								</div>
								<div class="div600px"><input id="input600px" type="text" name="itemDescription" value="'.$row['itemDescription'].'" required /></div>
								<div class="div250px">
									<select name="colourID" class="select250px" required>
										<option name="" value="">Select...</option>';
										echo $colouroptions;
									echo '</select>
								</div>
								<div class="div100px"><input id="submitbtn" type="submit" name="update" value="Update" /></div>
								<div class="div100px"><input id="submitbtn" type="submit" name="archive" value="Archive" /></div>
							</div>
							<input type="hidden" name="itemID" value="'.$row['itemID'].'" />
						</form>';
					}
				}
			echo '</div>
		</div>';

Secondly, I have no idea how to get the selected value to display for each row.

I am sorry if this is really basic, I am just trying to piece stuff together from YouTube and forum posts and just when I think I am getting somewhere, I find out it is completely wrong.

Thanks,

J

Edited December 3, 2022 by johnc81

[Barand]

Like this

$stmt = $pdo->query("SELECT categoryID
                          , categoryName
                     FROM tbl_categories 
                     WHERE categoryActive = 1 
                     ORDER BY categoryID
                     ");
$cat_options = '';
foreach ($statement as $row) {
    $cat_options .= "<option value='{$row['categoryID']}'>{$row['categoryName']}</option>";
}


// main loop here
while (..) {
    
    echo "<select name='categoryID' class='select200px' required>
             <option value="">Select...</option>
             $cat_options
          </select>";
}

 

[johnc81]

Thank you for your example. I just compared against mine (I edited my previous comment) and it looks pretty similar apart from I use a while loop and you used foreach.

I am not sure how I use this to get the original value from the main query and show that as the selected value. For example, if the operator selected the colour "Black" when creating the record, I want to show that as the selected value when echoing out the select values. Right now, it shows "Select....." for every row.

Thank you for all your help so far,

J

[Barand]

Slightly different approach then. Store the category query results in an array.

$stmt = $pdo->query("SELECT categoryID
                          , categoryName
                     FROM tbl_categories 
                     WHERE categoryActive = 1 
                     ORDER BY categoryID
                     ");
$cat_options = [];
/// store cat data in array
foreach ($statement as $row) {
    $cat_options[$row['categoryID']] = $row['categoryName'];
}


// main loop here
while ($row = $stmt->fetch()) {
    
    echo "<select name='categoryID' class='select200px' required>
             <option value=''>Select...</option>";
    foreach ($cat_options as $id => $cat) {
        $sel = $row['categoryID'] == $id ? 'selected' : '';
        echo "<option $sel value='$id'>$cat</option>";
    }
    echo "</select>";
}

 

[johnc81]

Hi,

Thank you so much for your help, it works perfectly now. I will go through my code remove all queries in loops.

Many thanks,

J