0

I want to check if a successful query has returned no results.

When testing this script locally, I make sure that the database does not contain any values the query is seeking, but

mysqli_num_rows($res) != 0

returns true. 

mysqli_num_rows($res)

returns the integer 1, and 

echo json_encode($res)

returns 

{"current_field":null,"field_count":null,"lengths":null,"num_rows":null,"type":null}

script.php

$sql    = "SELECT AVG(numUsers) FROM attendance 
           WHERE date = '".$currDate."' 
           AND HOUR(time) = '".$currTime."' ";

$res    = mysqli_query($conn, $sql)or die(mysqli_error($conn));

if(mysqli_num_rows($res) != 0) {
    while($row = mysqli_fetch_array($res)) {
        $numUsers = $row['AVG(numUsers)'];
    }
    // Round this average value to the nearest integer
    $numUsers = round($numUsers);
}else{
    echo "No values have yet been inserted into 'attendance' during the current hour.";
}

// Insert $numUsers into another table

What this means is that once this result array is returned, the numUsers variable remains empty, and

round($numUsers)

returns zero, which is an illegitimate value that is later inserted into the database.

How can I most reliably check that the successful query has returned a valid result, and that numUsers has been set accordingly?

Callum
  • 315
  • 4
  • 18
  • 2
    Use an alias `SELECT AVG(numUsers) as avgusers ...` to make the code a little easier to work with So you can use `$row['avgusers']` – RiggsFolly Jan 17 '17 at 15:21
  • 3
    Only as note `SELECT AVG(numUsers)` will allways have an result, maybe nothing is calulated, but it returns a value (e.g. NULL). And it will always be one result row. – JustOnUnderMillions Jan 17 '17 at 15:24
  • I'd think that this query will always return 1 row even if the content is `NULL` so strict check if the result is null (e.g. `$row['AVG(numUsers)'] !== null` instead of num_rows). – apokryfos Jan 17 '17 at 15:24
  • As you will only ever get ONE ROW returned you also dont need the WHILE loop – RiggsFolly Jan 17 '17 at 15:29
  • You could also do all that in SQL by doing `SELECT ROUND(AVG(numUsers)) as avgusers FROM attendance .....` – RiggsFolly Jan 17 '17 at 15:34

0 Answers0