How to Properly Use While Loop in Pdo Fetchall

how to properly use while loop in PDO fetchAll

With fetchAll() you don't have to use while at all. As this function returns an array, you have to use foreach() instead:

function getContent() {
    $db = PDOconn();
    $query = "SELECT * FROM posts ORDER BY id DESC LIMIT 0,3";
    $sql = $db->prepare($query);
    $sql->execute();
    return $sql->fetchAll();
}

$data = getContent();
foreach($data as $row) {
    $id = $row['id'];
    $content = $row['content'];
}

Convert PHP while loop to use PDO

The standard documentation in the PHP manual is usually pretty helpful. There is an example of executing a for loop with PDO in the PHP manual, PDO Details.

function getFruit($conn) {
    $sql = 'SELECT name, color, calories FROM fruit ORDER BY name';
    foreach ($conn->query($sql) as $row) {
        print $row['name'] . "\t";
        print $row['color'] . "\t";
        print $row['calories'] . "\n";
    }
}

With a few changes, the example can be made to use a prepared statement.

function getFruit($conn) {
    $query = $conn->prepare('SELECT name, color, calories FROM fruit WHERE kind=:kind ORDER BY name');
    $query->execute(array(':kind' => 'drupe'));
    // alternatively you could use PDOStatement::fetchAll() and get rid of the loop
    // this is dependent upon the design of your app
    foreach ($query as $row) {
        print $row['name'] . "\t";
        print $row['color'] . "\t";
        print $row['calories'] . "\n";
    }
}

You can also use a while loop and PDOStatement::fetch to get each row.

function getFruit($conn) {
    $query = $conn->prepare('SELECT name, color, calories FROM fruit WHERE kind=:kind ORDER BY name');
    $query->execute(array(':kind' => 'drupe'));
    // alternatively you could use PDOStatement::fetchAll() and get rid of the loop
    // this is dependent upon the design of your app
    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
        print $row['name'] . "\t";
        print $row['color'] . "\t";
        print $row['calories'] . "\n";
    }
}

The PHP manual remains quite helpful in providing all the necessary information to create the latter two versions.

Explanation of the last version: assuming $conn is a valid PDO object. $conn->prepare($sql) returns a PDOStatement object if successful, false on failure OR an exception based on your error handling. So, assuming success we would want to actually get the data from the object. We can use $query->fetch() in a loop or $query->fetchAll() to get the data dependent upon your app. Passing in the class constant PDO::FETCH_ASSOC will return, you guessed it, an associative array of data.

Functionally, the foreach and while implementations are equivalent. Conceptually, a foreach is more appropriate, as a while loop has connotations of looping while a static condition holds, whereas foreach loops over elements of a collection. Read "Differences between a while loop and a for loop in PHP?" for part of the story.

Be sure to read the php.net reference on PDO

PHP PDO while loop isn't returning anything

$query->fetch() already fetches a record. So next call to fetch() fetches next record or nothing if there're no records. In your case with one record second fetch() fetches nothing, so while never starts.

You can change your code to:

if($count){?>
<table>
    <tr>
        <th>Ontvanger</th>
        <th>Saldo</th>
        <th></th>
    </tr>
<?php
    while($result = $query->fetch()){
        // show row
    }?>
</table>
} else {
    // echo that no rows found
}

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.

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
}

while loop and PDO statements

Remove the line:

$row = $stmt->fetch();

It puts the first line inside $row

Then when you do:

while($row = $stmt->fetch()){ ...

Its starts fetching from the second line onwards, and the first line you fetched gets lost which is why you are always missing only one line.

mysqli_query while loop to PDO

You don't need to use fetchAll() just because you're using PDO. If the query returns a large amount of data, this could slow things down because it has to collect it all into memory. You can use the same kind of loop as in your mysqli code:

$res = $pdo->query("SELECT * FROM forum_index WHERE forum_over='yes'");
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
    ...
}

As to your second question, you should use a parametrized query, not substitute variables.

$stmt = $pdo->prepare("SELECT * FROM forum_threads WHERE f_id= :id");
$stmt->bindParam(':id', $_GET['id']);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    ...
}

PDO run query inside while loop showing only 1 result

Try a Join like this

SELECT
    c.name,
    sc.*,
    c.order_number
FROM
    tbl_sub_category AS sc
INNER JOIN
    tbl_category AS c ON c.id = sc.cat_id

You can test this one here
https://www.db-fiddle.com/f/vX8Z6jPbaweBDjGtvPwRdP/0

There is no need to pull, c.id (it would be ambiguous, anyway ) and you already have it as sc.cat_id. So, what we really need is everything from the sub-category table (alias as sc) and just the category name and order_number from the category table (aliased as c).

If you need main categories without a sub-category then you can do a Left Join. You'll have to account for some null values, which would be the case no matter what.

SELECT
    c.name,
    sc.*,
    c.order_number
FROM
    tbl_category AS c
LEFT JOIN
     tbl_sub_category AS sc ON c.id = sc.cat_id

And this one here
https://www.db-fiddle.com/f/kdgD9K5TYnD79boUdaC57k/0

I intentionally left sub-category 6 & 7 out, to show how it works for the left join, this way cat 4 doesn't have any sub-categories. It's a good Idea to have a proper test case if you need to account for those.

After you get your query sorted out (one call instead of O{n} calls), you'd follow pretty standard procedure, structure the data then output it. Like so:

<?php
$data = $conn->query($sql)->fetchAll(PDO::FETCH_GROUP);
?>

First thing to do is group the data by category, so we can have a item for category and several items for sub-categories. The category name $row['name'] works great for this, because we need it outside of the inner foreach loop.
As mentioned by @YourCommonSense, in the comments, we can easily do this by using PDO::FETCH_GROUP and putting the name column as the first column in our selection list.

Which will give you something like this:

$data = [
    'Allgem' => [
        ['id'=>'1','cat_id'=>'1','sub_name'=>'Alter','name'=>'Allgem','order_number'=>'1'],
    ],
    'Vorauss' => [
        ['id'=>'2','cat_id'=>'2','sub_name'=>'Trainingsalter','name'=>'Vorauss','order_number'=>'2'],
        ['id'=>'3','cat_id'=>'2','sub_name'=>'Kader','name'=>'Vorauss','order_number'=>'2'],
        ['id'=>'4','cat_id'=>'2','sub_name'=>'Wettkampfsystem','name'=>'Vorauss','order_number'=>'2'],
    ],
    'Support' => [
        ['id'=>'5','cat_id'=>'3','sub_name'=>'Trainingsort','name'=>'Support','order_number'=>'3'],
        ['id'=>'6','cat_id'=>'3','sub_name'=>'Team','name'=>'Support','order_number'=>'3'],
    ]
];

As you can see, now we have a structure grouped by the cat ID. This will work much better for what we need to do next. Because we pre-format the data the way we want it makes our code simpler, more concise and easier to read.

<ul class="list-unstyled components">
    <p style="text-align: center;font-weight: 600">Categories</p>
    <?php
        $i = 0;
        foreach ($data as $cat_name => $sub_categories):
    ?>
        <li class="">
            <a href="#homeSubmenu<?php echo $i;?>" data-toggle="collapse" aria-expanded="false"><?php echo $cat_name ; ?></a>
            <?php foreach ($sub_categories as $row): ?>
                <ul class="collapse list-unstyled" id="homeSubmenu<?php echo $i;?>">
                    <li><a href="?cat=Allgem&sub=Beschreibung"><?php echo $row['sub_name']; ?></a></li>
                </ul>
            <?php endforeach; ?>
        </li>
    <?php
        $i++;
        endforeach;
    ?>
</ul>

You can test it here, but it's near impossible to find a phpSandbox that allows you to mix HTML, PHP, save it, and output in HTML. So it's just the raw source html, but you can see it's syntactically correct and error free.

http://sandbox.onlinephpfunctions.com/code/1030bfd98c73d12b718077363d30ac587166c6cb

On the topic of errors, you also had several other issues. Like the sub-link had a static address <a href="?cat=Allgem&sub=Beschreibung">. You had $row['cat_name'] which dosn't exist in your table schema (its just tbl_category.name). Another one is this <p> tag between the first <ul> and <li> I didn't see a reasonable way to fix that without changing the structure, because well the structure is the issue. See below:

<ul class="list-unstyled components">
    <p style="text-align: center;font-weight: 600">Categories</p> <!-- p tags shouldn't be here -->
    ....
     <li class="">

Personally, I would recommend using a dictionary list, or <dl>

https://www.w3schools.com/tags/tag_dl.asp

Which would let you do something like this

<dl class="list-unstyled components">
    <dt><p style="text-align: center;font-weight: 600">Categories</p></dt
     <dd class=""><!-- instead of the li tag --></dd>
</dl>

Or just remove the <p> tag altogether? Anyway, there may be other issues with your original HTML that I didn't see. But I figure this should get you on the right track.

Cheers.

---

Related Topics