Checking result/count before outputting while loop

Question

I'm looking for a way to SELECT from database, then check the result, and then output rows in a while loop (IF the result was above zero)

I really want to avoid using a separate count query

Right now I use this:

$sql = 'SELECT id, username, usercity, usercountry FROM siteusers WHERE userage > 50';
$STH = $conn->query($sql);
$arr = $STH->fetchAll();

if (count($arr) > 0) {

    echo '<div id="users">';

    foreach ($arr as $row) {

       echo '<h1>'.$row['username'].</h1>';

    }

    echo '</div>';

}

It works. But isn't there a way I can check result/numrows and loop the rows, without using fetchAll and custom for-each loop?

Or does it not matter at all? (is for-each just as good as while loop?)

If I do it like this, the first row is not included in the while loop:

$sql = 'SELECT id, username, usercity, usercountry FROM siteusers WHERE userage > 50';
$STH = $conn->query($sql);

if ($row = $STH->fetch()) {

    echo '<div id="users">';

    while ($row = $STH->fetch()) {

       echo '<h1>'.$row['username'].</h1>';

    }

    echo '</div>';

}

EDIT: I DO need to check the result, for dynamic layout purposes

if you goto example #2 on the php.net site it says use `$STH->fetchColumn()` http://php.net/manual/en/pdostatement.rowcount.php — mic, Jul 14 '14 at 15:00
you should look at this http://stackoverflow.com/questions/6041886/count-number-of-rows-in-select-query-with-pdo — George G, Jul 14 '14 at 15:12
Ok gonna try that fetchColumn againagain.. Could not make it work earlier.. Maybe because its not standard for most databases? — mowgli, Jul 14 '14 at 15:12
fetchColumn() outputs nothing. Not a zero or number, just nothing — mowgli, Jul 14 '14 at 15:19
This outputs an array. With print_r($STH->fetch(PDO::FETCH_NUM)) I see all the data from ONE row, and the number 1. I suppose this is only useful on a separate COUNT(*) query (which I want to avoid) — mowgli, Jul 14 '14 at 15:26
Guess I'll just stick with my first code part. I would rather use while loop, but it works — mowgli, Jul 14 '14 at 15:33
EDIT: @Nicolas. Yes, rowCount works.. But the manual says "If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications." And I would rather use a bulletproof solution. Oh well.. ;) — mowgli, Jul 14 '14 at 15:34
Yeah, your first piece of code isn't bad. Your creating one more variable which isn't a big deal. What is your reasoning for wanting a while loop rather than a for each? — Nicolas, Jul 14 '14 at 15:35
Well I guess it's mostly habit and wanting to use something that was created for that use, instead of making my own (and re-setting an array) ;) — mowgli, Jul 14 '14 at 15:41

score 1 · Accepted Answer · answered Jul 14 '14 at 15:46

1

You can use the PDO method rowCount to verify before your foreach if there are rows

$STH = $conn->query($sql);
if ($STH->rowCount())
 {echo '<div id="users">';
  foreach ($STH->fetchAll() as $row)
   {
    echo '<h1>'.$row['username'].'</h1>';
    }
  echo '</div>';
  }

http://php.net/manual/en/pdostatement.rowcount.php

note that this uses up a lot of memory as all your results are loaded at once in memory with fetchAll(). if you have very large result sets, consider using a while instead of the foreach

while ($row = $STH->fetch())
 {// foo with $row
  }

answered Jul 14 '14 at 15:46

Félix Adriyel Gagnon-Grenier

8,422
10
52
65

As I said in upper comments: Yes rowCount works for me (mysql). But the manual says: "If the last SQL statement executed by the associated PDOStatement was a SELECT statement, SOME databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications (!)" – mowgli Jul 14 '14 at 15:49
mysql is notably one of the interfaces which returns the rowcount. are you up for multiple databases, or will your code run on mysql database? – Félix Adriyel Gagnon-Grenier Jul 14 '14 at 15:50
For now I stick with mySQL.. I just worry a bit if my code will work always, also in the future. Well, maybe I should just use rowcount, and deal with any problems later. Just want to do the best thing from start – mowgli Jul 14 '14 at 15:53
this is rather simple; there are two ways, either you fetch your rows and count them manually, either you use `rowCount()` and stay with mysql. – Félix Adriyel Gagnon-Grenier Jul 14 '14 at 15:55
1

Ok I just use rowCount then ;) And while() instead of for-each/fetchAll (not needed when I have rowcount). Thanx – mowgli Jul 14 '14 at 16:04

Nicolas · Answer 2 · 2014-07-14T15:25:16.470

0

$row is being set to the first row of your results inside of your if statement. This means that your while loop will start at the second row.

$sql = 'SELECT id, username, usercity, usercountry FROM siteusers WHERE userage > 50';
$STH = $conn->query($sql);

    echo '<div id="users">';

    while ($row = $STH->fetch()) {
       echo '<h1>'.$row['username'].</h1>';
    }

    echo '</div>';

The while loop will run if there is any results to fetch, and if there aren't, then respectively it won't run.

edited Jul 14 '14 at 15:25

answered Jul 14 '14 at 14:58

Nicolas

1,125
1
15
31

But I don't want to output the html (divs etc) for that section if no rows was found. In other situations I want to tell the viewer "sorry, no users was found" – mowgli Jul 14 '14 at 15:00
This won't output anything if no users were found.. If you use that if statement it will set $row equal to your first row. That's why it is skipping the first one when you use your while loop. – Nicolas Jul 14 '14 at 15:00
See my question again. It has surrounding div's inside if statement ;) The design for that element (users) should not be shown (or be different) if no users was found. Yes I do need to check the result – mowgli Jul 14 '14 at 15:03
I understand what you are saying. You only want to print out the div if the results aren't empty? – Nicolas Jul 14 '14 at 15:06
Yes, and perhaps more than just those div's. But I really need the result to serve different layout – mowgli Jul 14 '14 at 15:07

Checking result/count before outputting while loop

2 Answers2