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() php.net/manual/en/pdostatement.rowcount.php — mic
– mic, Commented Jul 14, 2014 at 15:00
you should look at this stackoverflow.com/questions/6041886/… — George G
– George G, Commented Jul 14, 2014 at 15:12
Ok gonna try that fetchColumn againagain.. Could not make it work earlier.. Maybe because its not standard for most databases? — mowgli
– mowgli, Commented Jul 14, 2014 at 15:12
fetchColumn() outputs nothing. Not a zero or number, just nothing — mowgli
– mowgli, Commented Jul 14, 2014 at 15:19

Félix Adriyel Gagnon-Grenier · Accepted Answer · 2014-07-14 15:46:00Z

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, 2014 at 15:46

Félix Adriyel Gagnon-Grenier

8,93510 gold badges58 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mowgli Over a year ago

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 (!)"

Félix Adriyel Gagnon-Grenier Over a year ago

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?

mowgli Over a year ago

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

Félix Adriyel Gagnon-Grenier Over a year ago

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.

mowgli Over a year ago

Ok I just use rowCount then ;) And while() instead of for-each/fetchAll (not needed when I have rowcount). Thanx

Nicolas · Accepted Answer · 2014-07-14 15:25:16Z

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, 2014 at 15:25

answered Jul 14, 2014 at 14:58

Nicolas

1,1531 gold badge17 silver badges31 bronze badges

5 Comments

mowgli Over a year ago

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"

Nicolas Over a year ago

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.

mowgli Over a year ago

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

Nicolas Over a year ago

I understand what you are saying. You only want to print out the div if the results aren't empty?

mowgli Over a year ago

Yes, and perhaps more than just those div's. But I really need the result to serve different layout

Collectives™ on Stack Overflow

Checking result/count before outputting while loop

2 Answers 2

5 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related