How to return number of found rows from SELECT query

Question

I wrote a function which is supposed to return the number of rows found in a SELECT query but it always either seems to return 0 or an array. I have been messing around with this for about an hour now and I still can't figure it out! I'm sure I'm doing something stupidly wrong.

The MySQL Table

+--------+-------------+---------------------+
| postid |     ip      |        time         |
+--------+-------------+---------------------+
|   1234 | 192.168.0.1 | 2014-01-29 14:27:02 |
|   5678 | 192.168.0.2 | 2014-01-29 14:27:02 |
+--------+-------------+---------------------+

PHP

### Search for IP in database
function postviews_get_ip($id, $ip) {
    global $post, $wpdb;

    $wpdb->get_results("SELECT * FROM $wpdb->wp_postviews_ips WHERE postid = $id AND ip = $ip");

    $rowcount = $wpdb->num_rows;

    return $rowcount;
}

postviews_get_ip($id, $_SERVER['REMOTE_ADDR']);
//both $id and $_SERVER['REMOTE_ADDR']) return the values I'm searching for in the database

Answer 1

If you are merely trying to get a count, $wpdb->get_var(); along with using COUNT() in your sql will be better:

### Search for IP in database
function postviews_get_ip($id, $ip) {
    global $post, $wpdb;

    $rowcount = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->wp_postviews_ips WHERE postid = $id AND ip = '$ip'");

    return $rowcount;
}

postviews_get_ip($id, $_SERVER['REMOTE_ADDR']);
//both $id and $_SERVER['REMOTE_ADDR']) return the values I'm searching for in the database

As to what went wrong in your previous example, you weren't assigning your $wpdb->get_results() instance to a variable, and without it $wpdb->num_rows; is just going to return zero as it isn't actually pulling from the instance of the query, but rather the global $wbdb object.

If you do want to use get_results():

### Search for IP in database
function postviews_get_ip($id, $ip) {
    global $post, $wpdb;

    $ipquery= $wpdb->get_results("SELECT * FROM $wpdb->wp_postviews_ips WHERE postid = $id AND ip = '$ip'");
    $rowcount = $ipquery->num_rows;
    return $rowcount;
}

postviews_get_ip($id, $_SERVER['REMOTE_ADDR']);
//both $id and $_SERVER['REMOTE_ADDR']) return the values I'm searching for in the database

But I wouldn't see the need for that unless you needed the results, in which case I would just return the $ipquery object and use num_rows on it when I needed it:

### Search for IP in database
function postviews_get_ip($id, $ip) {
    global $post, $wpdb;

    $ipquery = $wpdb->get_results("SELECT * FROM $wpdb->wp_postviews_ips WHERE postid = $id AND ip = '$ip'");
    return $ipquery;
}

$someVariable = postviews_get_ip($id, $_SERVER['REMOTE_ADDR']);
//both $id and $_SERVER['REMOTE_ADDR']) return the values I'm searching for in the database

echo $someVariable->num_rows;

Answer 2

Seems the query is wrong. $ip is string so you should put single quote around that as below

$wpdb->get_results("SELECT * FROM {$wpdb->prefix}postviews_ips WHERE postid = $id AND ip = '$ip'");