1

I am having a strange issue with mysqli_num_rows. Searching for this issue, I've only found people having issues where NULL is returned no matter what. I also checked the official documentation for the function, and it says it returns an integer of the number of rows returned by the query. Whenever my query returns 1 row (it never should return more), it behaves as I expect. When the query returns 0 rows, I expect the function to return 0, but it returns NULL. Why doesn't it return 0?

I know that my database connection is good and my query works correctly, because when I look for a record that's in the database, I get an integer back. I just can't figure out why this is returning NULL rather than 0.

function getArtistID($name) {
    global $conn; 
    $query = "SELECT artist_id FROM artist WHERE artist_name LIKE '${name}'";
    $result = mysqli_query($conn, $query);
    if ($result->num_rows) {
        $row = mysqli_fetch_assoc($result);
        return $row['artist_id'];
    } else {
        return 0;
    }
}
Marcin Orlowski
  • 72,056
  • 11
  • 123
  • 141
Blake F.
  • 153
  • 13
  • 4
    How do you know? Given this code, the behavior whether it returns `0` or `null` should be the same. I have a feeling that `mysqli_query` is the one returning null, not `mysqli_num_rows`. – tkausl Mar 04 '19 at 00:04
  • 2
    Don't use `global`. That's clear indicator of bad design if your code needs it. – Marcin Orlowski Mar 04 '19 at 00:07
  • @tkausl yes, that is true. But I'm curious why it doesn't return 0. The docs don't mention it returning NULL ever. – Blake F. Mar 04 '19 at 00:11
  • 2
    I _really_ believe you're misinterpreting some information. If it _really_ returns null, please show how you got to that conclusion exactly, including any code with `var_dump`s and the corresponding output. – tkausl Mar 04 '19 at 00:12
  • @MarcinOrlowski ok, I'll look in to avoiding it. Why is it bad design? I am doing this because I have a php file that establishes a connection to the database that I have used an include statement on, and when I tried it without global, it couldn't find the connection variable. – Blake F. Mar 04 '19 at 00:13
  • @tkausl I simply used an echo statement to display the results of calling this function. When it got a record, it returned 1, when it didn't it returned NULL. What am I misinterpreting? – Blake F. Mar 04 '19 at 00:15
  • 1
    People often say that using `global` is bad, because it makes it hard to write unit tests. You can't test a function in limited scope if it references global variables. It's awkward to create mock objects for globals. See https://phpunit.readthedocs.io/en/8.0/fixtures.html#global-state – Bill Karwin Mar 04 '19 at 00:19
  • Since PHP is bad design from the scratch, `global` does not make it much worse. When building based on a framework using multiple extension modules from different third party distributors, globals could introduce conflicts. In a fully self-written project it can make things easier to code. Professional code should neither use globals nor PHP. Gloabals in general are also considered as a security issue, however, in PHP we cannot prevent anything from being found and accessed. So globals only make it easier for malicious codes. – Pinke Helga Mar 04 '19 at 00:28
  • @blakeF. because it simply breaks code isolation. If your method, function needs something, pass it to it. Do not make it fetch that thing by itself. Unit tests will be painful (same with "new" operator vs. using dependency injection). I assume this is far ahead of you, so for now: ditch `global`, pass `$conn` as argument to `getArtistID()`. BTW: it should be `getArtistId()` not `getArtistID()` if you follow camel case. – Marcin Orlowski Mar 04 '19 at 00:48

2 Answers2

4

Here's some code that I used to reproduce a case where num_rows seems to be NULL:

<?php
error_reporting(E_ALL);
$conn = new mysqli('127.0.0.1', 'root', null, 'test');
$sql = "SELECT * FROM dual";
$result = $conn->query($sql);
if ($result === false) {
  print "Error: {$conn->error}\n";
}
$n = $result->num_rows;
echo "Dump the num_rows property: ";
var_dump($n);

Output:

Error: No tables used

Notice: Trying to get property of non-object in /Users/bkarwin/Documents/SO/my.php on line 14
Dump the num_rows property: NULL

The notice is because it's invalid to access an object-oriented property of a variable that is not an object. This is a frequent source of confusion for PHP developers, and it's a byproduct of the fact that PHP is a loosely typed language, and functions like query() can return either a result object, or a boolean scalar.

The query() function actually returned a false as $result because of some error. In my code, I checked for this error, and you didn't.

When you run mysqli::query() or mysqli::prepare() or mysqli_stmt::execute(), you must check for error conditions every time.

Something about your query caused an error. It's up to you to check for the error and report it.

Update: I edited some text above to make the explanation better, but it might make some comments below seem out of place.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Ok, I will add the check. But when I run the query in phpmyadmin, I get no errors. I also, don't get any odd behavior when it returns rows. My table does have columns. I'm not really understanding your point here. – Blake F. Mar 04 '19 at 00:19
  • 1
    My query is just one example of a query that generates an error. I'm saying that your query probably generates some different error. The point is that you're not checking for errors. – Bill Karwin Mar 04 '19 at 00:20
  • I see. I guess my thought process was that checking if ($result->num-rows) was a sufficient check because it would be false if anything went wrong. But I see how this is better. – Blake F. Mar 04 '19 at 00:27
  • 1
    I guess you are also not calling `error_reporting()` to enable the notice you should get you call `$result->num_rows` when `$result` is the boolean value **false**. You can't request an object-oriented property from a variable unless it's an object. When the variable is a scalar, you should get a notice, when you configure the error reporting level correctly. That's shown in my code example. – Bill Karwin Mar 04 '19 at 00:31
  • In my example, when I try to set `$n = $results->num_rows;` the notice interrupts that assignment, and `$n` gets no value at all, that's why when I dump `$n` it is NULL, because it never got a value. – Bill Karwin Mar 04 '19 at 00:32
  • 1
    When you don't configure PHP to output notices, and you don't check for errors yourself, you get lots of confusing effects, because you have no idea when errors have occurred. – Bill Karwin Mar 04 '19 at 00:33
  • 1
    It should be required reading for PHP developers to study https://phptherightway.com/ and practice all the recommendations. – Bill Karwin Mar 04 '19 at 00:35
  • _" you must check for error conditions every time"_ you can configure MySQLi to throw exceptions so you don't have to do these checks. [How to get MySQLi error information in different environments](https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-information-in-different-environments) – Phil Mar 04 '19 at 00:55
  • @Phil, Yes that's true, but given a newbie PHP developer who isn't even aware of `error_reporting()`, exception-handling is usually too advanced for them. – Bill Karwin Mar 04 '19 at 01:13
  • Who's talking about exception _"handling"_? Given the right error reporting, uncaught exceptions are hard to miss – Phil Mar 04 '19 at 01:16
  • I know, but spewing excepts and stack traces doesn't make for a good user experience. The developer has to learn how to catch them and do something sensible like logging, recovery, retries, etc. – Bill Karwin Mar 04 '19 at 01:19
  • I'm in doubt too whether or not I should give the advice to throw exeptions to beginners. I expect they do not distinguish between development and productive system and will expose possible sensitive information to the public. On the other hand it is a real advantage on debugging. – Pinke Helga Mar 04 '19 at 23:55
1

I just can't figure out why this is returning NULL rather than 0.

We can only guess without seeing the log output; but, it is likely the return value is null because it raised an error instead.

You need to ensure that errors are handled when calling a function, before attempting to use the return value.

bignose
  • 30,281
  • 14
  • 77
  • 110