Counting rows, and executing while loop only if more than 0 rows with prepared PDO

Question

I am trying to do a while loop with a prepared PDO statement, but I only want it to execute if there are any rows. Currently I am using this, but it seems to be missing the first result, presumably because its moving the pointer.

What is the right way to do this?

$stmt = $pdo->prepare('SELECT * FROM products p 
INNER JOIN products_to_categories c 
ON p.products_id = c.products_id
WHERE c.categories_id = ? 
AND products_status=? 
ORDER BY p.products_sort_order,p.products_name');
$stmt->execute([$categories_id,1]);
if(($category_row = $stmt->fetch(PDO::FETCH_ASSOC)) != null) { 
    $no_results = count($stmt->fetch(PDO::FETCH_ASSOC));
    while ($products_row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // show row info
    }
}

I cant start fetching in a while loop unless there is at least 1 row, otherwise I need to do a different function — Source, Jul 01 '17 at 20:22

Aakash Verma · Answer 1 · 2017-07-01T20:20:57.430

2

What about something like:

$stmt = $pdo->prepare('SELECT * FROM products p INNER JOIN products_to_categories c ON p.products_id = c.products_id
    WHERE c.categories_id = ? AND products_status=? ORDER BY p.products_sort_order,p.products_name');
$stmt->execute([$categories_id,1]);
$products_row="some_random_string";
while ($products_row = $stmt->fetch(PDO::FETCH_ASSOC) && $product_row!="some_random_string" && $product_row!=false) {
    // show row info
}

Since you talked about row count,

$result = $con->prepare("SELECT count(*) FROM `products`"); 
$result->execute(); 
$number_of_rows = $result->fetchColumn();

edited Jul 01 '17 at 20:20

answered Jul 01 '17 at 19:45

Aakash Verma

3,705
5
29
66

Just to cover all fetch types, I took an initial string value. – Aakash Verma Jul 01 '17 at 19:48
so am I right then? Every time I do `$stmt->fetch(PDO::FETCH_ASSOC)` its moving the pointer? How can I do a row count, i.e. mysqli_num_rows? – Source Jul 01 '17 at 20:16
Read this https://stackoverflow.com/questions/883365/row-count-with-pdo . I hope it will help :) – Aakash Verma Jul 01 '17 at 20:18
@Source Yes, you are correct. There is no reliable way to count from an already existing function. – chris85 Jul 01 '17 at 20:20
rowCount() returns the number of rows affected by the previous SQL execution..not the row count of the table. – Aakash Verma Jul 01 '17 at 20:25

LSerni · Answer 2 · 2017-07-01T21:20:09.803

0

You do not need to check the row count. Simply rewrite:

$stmt = $pdo->prepare('...');
$stmt->execute([$categories_id, 1]);
$rows = 0;
while ($products_row = $stmt->fetch(PDO::FETCH_ASSOC) {
    // You can increment $rows only if some other condition is met if you want
    $rows++;
    // show row info
}
switch ($rows) {
    case 0:
        // No rows were retrieved.
        // run the 'different function' you mentioned in the comments
        break;
    case 24:
        print "There are two dozen rows in your results";
        break;
}

As requested, the while loop will never execute if there are no results. And if there are, it will loop every one of them.

edited Jul 01 '17 at 21:20

answered Jul 01 '17 at 20:24

LSerni

55,617
10
65
107

What if I want to execute some code if there are $rows, that is not in the while loop. i.e. There are 24 products that match your results: // start loop – Source Jul 01 '17 at 20:35
How would you be able to predict e.g. 24 rows? That wont work? – Source Jul 01 '17 at 22:04
Sorry, I thought you were asking '*suppose there are 24 rows...*'. That's why I put a switch with `case 24` in there. If you need to know the total number of rows returned by the query, even before fetching anything, then you can use `$stmt->rowCount();` (but check out the manual). – LSerni Jul 01 '17 at 22:43

Counting rows, and executing while loop only if more than 0 rows with prepared PDO

2 Answers2