How to Loop Through a MySQL Query via Pdo in PHP

How do I loop through a MySQL query via PDO in PHP?

Here is an example for using PDO to connect to a DB, to tell it to throw Exceptions instead of php errors (will help with your debugging), and using parameterised statements instead of substituting dynamic values into the query yourself (highly recommended):

// connect to PDO
$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "password");

// the following tells PDO we want it to throw Exceptions for every error.
// this is far more useful than the default mode of throwing php errors
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// prepare the statement. the placeholders allow PDO to handle substituting
// the values, which also prevents SQL injection
$stmt = $pdo->prepare("SELECT * FROM product WHERE productTypeId=:productTypeId AND brand=:brand");

// bind the parameters
$stmt->bindValue(":productTypeId", 6);
$stmt->bindValue(":brand", "Slurm");

// initialise an array for the results
$products = array();
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $products[] = $row;
}

PDO query in loop

You could completely avoid all of that code by just doing this:

$db->query("UPDATE videos SET vkey = MD5(CONCAT(vkey, 'video'))");

(Or you could do this query in your backend like PHPMyAdmin UPDATE videos SET vkey = MD5(CONCAT(vkey, 'video')))

---

However, if you for some reason want to loop through your database, you could do this:

$sql = "SELECT id FROM videos";

//no reason to use prepare() because you aren't passing variables.
$stmp = $db->query($sql);
$stmp->execute();
$results = $stmp->fetchAll(PDO::FETCH_ASSOC);

//prepare UPDATE query outside of loop, this way you don't send 2 requests to your database for every row
$stmp = $db->prepare("UPDATE videos SET vkey = :vkey WHERE id = :id");

foreach($results as $result) {
    $vkey = md5($result['id']."video");
    $stmp->execute(
        array(
            ":vkey" => $vkey, 
            ":id" => $result['id']
        )
    );
}

Also, it's usually a good idea to check the return values inside the loop to make sure there were no errors, you could probably do this by using something like $stmp->rowCount() to check if there were any rows effected.

Looping through query results with fetch(PDO::FETCH_ASSOC)

As discussed in the comments, I was looking for an alternative to mysqli_num_rows() and found rowCount().

The following now works for me:

$stmt = DB::run("SELECT * FROM admin WHERE username='$manager' AND password='$password' LIMIT 1");
$existCount = $stmt->rowCount();
if ($existCount == 1){

loop through the data in PDO

The solution is to change select to return all rows , since fetch() only return a single row at the time.

Option 1:

$result = array();
while($row = $smtp->fetch(PDO:: FETCH_ASSOC)){    
  $result[]=$row;
}             
return $result;

option 2:

$result = $smtp->fetchAll(PDO:: FETCH_ASSOC);
return $result;

use your function like this

$rows = select($conn, 'user', 'cart', $user,':user','*');
foreach($rows as $row){
 //do something with $row
}

Loop results PDO PHP

Well, you only call $sth->fetch once. You need to loop over the results.

while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
   echo $row['First_Name'] . ' ' . $row['Surname'] . "\n";
}

Also don't call array string indexes without braces. This way PHP checks if the key is a CONSTANT, then casts it to string. This is just bad practice and might lead to unexpected bahavior.

If this returns only one row, you probably have only one row in the database (or the result set). Show us more code!

How to loop through PDO Statement and write data on if statements

Sorry, I didn't get your code at first.

Make it this way

$res = $stmt->fetchAll(PDO::FETCH_GROUP);
foreach($res as $user_id => $user_data) {
    $mydate = date('m/d/Y');
    $dateadd = date('m/d/Y', strtotime($mydate. ' + 3 days'));  
    $html_table = '<div>Week Ending: ' .$mydate. '<br>Payroll Issued: ' .$dateadd. '</div><br>';
    $html_table .= '<table border="1" cellspacing="0" cellpadding="2" width="100%"><tr><th>Date</th><th>Customer Name</th><th>Address</th></tr>';
    foreach ($user_data as $result) {
        $html_table .= '<tr><td>' .$result['saledate']. '</td><td>' .$result['custname']. '</td><td>' .$result['straddr']. ' ' .$result['city']. ' ' .$result['state']. ' ' .$result['zip']. '</td></tr>';
    }
    $html_table .= '</table>'; //ends HTML table
    $mpdf = new mPDF();
    $mpdf->SetTitle('DPL Galion Sales');
    $mpdf->WriteHTML($html_table);
    if(is_dir('../reports/'.$user_id.'')) {
        $mpdf->Output('../reports/'.$user_id.'/DPLOH/'.date('m-d-Y').'_DPLOH_SalesID_'.$user_id.'.pdf','F');
    }
}

PDO looping through and printing fetchAll

It should be

while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
  $id = $row['id'];
  $n = $row['n'];
  $k = $row['k'];
}

If you insist on fetchAll, then

$results = $query->fetchAll(PDO::FETCH_ASSOC);
foreach($results as $row) {
   $id = $row['id'];
   $n = $row['n'];
   $k = $row['k'];
}

PDO::FETCH_ASSOC fetches only column names and omits the numeric index.

php pdo loop through row skips 1st row

<?php
 // your first error is here. You are fetching the first row
 $row = $query->fetch(PDO::FETCH_ASSOC)
 // And here you start from the second, since you already did ones above
 while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
  //...rest of oyur code
 }
?>

you have two way to accomplish your task

  <?php
  // Just add the PDO::FETCH_ASSOC constant while you are looping
  while($row = $query->fetch(PDO::FETCH_ASSOC)){
   //...Code here
  }

  // another way is adding the constant before using it 
  $query->setFetchMode(PDO::FETCH_ASSOC);
  while($row = $query->fetch()){
   //...Code here
  }
  ?>

---

Related Topics