SQLite3 count rows

Question

I am trying to count the number of rows returned from a query

This is currently the code I have

function products()
{
$loggedin = loggedin();
$db = db();

$stmt = $db->prepare('SELECT game_id, name, developer, price FROM game   WHERE quantity > 0 ORDER BY game_id DESC');
$result = $stmt->execute();
$row1 = $result->fetchArray();
$rows = count ($row1); 

if ($rows == 0)
    {
    echo "Sold out";
    }
else
    {
    while ($row = $result->fetchArray()) {
    echo '<p>'.$row['name'].'</p>';

    }
    }
}

At present it will only return the first item in the table, even though all rows match

if I change my code to

function products()
{
$loggedin = loggedin();
#$page = 'index.php';
$db = db();
$stmt = $db->prepare('SELECT game_id, name, developer, price FROM game   WHERE quantity > 11 ORDER BY game_id DESC');
$result = $stmt->execute();
while ($row = $result->fetchArray()) {
    echo '<p>'.$row['name'].'</p>';

}

Then I get all the result from the table correctly

if I change the query to a value outside the quantity like

quantity > 20

then I get the error

Warning: count(): Parameter must be an array or an object that implements Countable

what I would like it to do is if row1 is == 0 the show sold out other wise display all the rows

so if a row does not match the query it will not be included in the results I can not use PDO, and I have not been able to find out to use count on https://www.php.net/manual/en/book.sqlite3.php

You get a warning and not an error, but it seems to work no? — txemsukr, Sep 16 '19 at 11:53
fetchArray fetches one single record from the result set, so what you are trying to count there in the first snippet makes little sense to begin with - that is never going to result in more than 1. You should be using https://www.php.net/manual/en/sqlite3result.numcolumns.php — 04FS, Sep 16 '19 at 11:56
@txemsukr Just noticed I made a mistake in my code as I was testing, will fix that up now, it is only returning the warning — Ausghostdog, Sep 16 '19 at 12:01
@04FS thanks will try with numcolumns, so that will count all the rows in the column in the database? — Ausghostdog, Sep 16 '19 at 12:02
You can use numcolumns if you just need to determine whether you got any rows, or not. If you need the actual count - https://stackoverflow.com/questions/601600/how-to-get-the-number-of-rows-of-the-selected-result-from-sqlite3 — 04FS, Sep 16 '19 at 12:04
@u_mulder using if ($result->numColumns() && $result->columnType(0) != SQLITE3_NULL) { while ($row = $result->fetchArray()) { echo '
'.$row['name'].'
'; } } else { echo "Sold out"; } that returns sold out only no matter what I change query to — Ausghostdog, Sep 16 '19 at 12:16

nikoksr · Accepted Answer · 2019-09-16T13:58:33.327

1

That should do it.

function products() {
    $loggedin = loggedin();
    $db = db();
    $games = $db->query('SELECT game_id, name, developer, price FROM game WHERE quantity > 0 ORDER BY game_id DESC');

    if (empty($games->fetchArray())) {
        echo "Sold out";
        return;
    }

    // Reset the result back to the first game
    $games->reset();

    while ($game = $games->fetchArray(SQLITE3_ASSOC)) {
        echo '<p>'.$game['name'].'</p>';  
    }
}

edited Sep 16 '19 at 13:58

answered Sep 16 '19 at 12:29

nikoksr

342
4
10

OK if the is set to quantity > 20 I get sold out as expect, changing that to quantity > 0 I get Illegal string offset 'name' for the rows, https://stackoverflow.com/questions/9869150/illegal-string-offset-warning-php – Ausghostdog Sep 16 '19 at 12:41
if I change code foreach($games as $game) { echo '
'.$games['name'].'
'; it prints the last item for the total amount of rows – Ausghostdog Sep 16 '19 at 12:48
@Ausghostdog my bad, can't test my code right now. I edited my answer, does it work now? – nikoksr Sep 16 '19 at 13:03
Call to undefined function sqlite_num_rows, I am working with sqlite3 – Ausghostdog Sep 16 '19 at 13:14
@Ausghostdog okay, edited it again. What about now? – nikoksr Sep 16 '19 at 13:59

score 1 · Answer 2 · answered Sep 16 '19 at 15:16

I'd do something like:

function products() {
    $db = db();
    $stmt = $db->prepare('SELECT game_id, name, developer, price FROM game WHERE quantity > 0 ORDER BY game_id DESC');
    $result = $stmt->execute();
    $count = 0;

    while ($row = $result->fetchArray()) {
        echo '<p>'.$row['name'].'</p>';
        $count++;  
    }

    if ($count == 0) {
        echo "<p>Sold out</p>";
    }
}

The only way to find out how many rows were returned by a query is to fetch all of them (and you don't actually care how many rows were returned, just that at least one was). So just increment a counter once per row returned, and after stepping through all rows, if that counter is still 0, it means no rows were returned, and you can display the relevant message.

SQLite3 count rows

2 Answers2