Jump to content

PHP PDO query within while loop only returns first row


johnc81
Go to solution Solved by Barand,

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by johnc81
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by johnc81
Link to comment
Share on other sites

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>";
}

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Solution

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>";
}

 

  • Great Answer 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.