Counting distinct columns in Sqlite

Databases
1

I have a script that counts the number of users online. However it uses
MySQL and deprecated mysql_ calls so I am converting it to Sqlite and
PDO. I’m mostly there but I am having trouble getting my brain around
counting the number of distinct ip’s. Can anyone help please and/or improve my code? Thanks

$t_stamp = time();
$timeout = $t_stamp - 120;       // time to reset IP address's value in seconds, 120 is 2 minutes
$ip_addr = $_SERVER['REMOTE_ADDR'];

try {
  $db = new PDO('sqlite:usersonline.sqlite');
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  // first time, create table
  $db->exec('CREATE TABLE IF NOT EXISTS usersonline (timestamp INTEGER PRIMARY KEY, ip TEXT KEY);');

  $query = 'INSERT INTO usersonline (timestamp, ip) VALUES (:t_stamp, :ip_addr);';
  $stmt = $db->prepare($query);
  $stmt->bindParam(':t_stamp', $t_stamp);
  $stmt->bindParam(':ip_addr', $ip_addr);
  $stmt->execute();

  $query = 'DELETE FROM usersonline WHERE timestamp<'.$timeout;
  $db->exec($query);

  $query = 'SELECT DISTINCT ip FROM usersonline;';
  $result = $db->query($query);
  $user = count($result);

} catch ( PDOException $e ) {
  echo $e->getMessage();
}
// Close the database
$db = null;

if ( $user == 1 )
  echo '1 user online.';
else
  echo $user,' users online.';
2

So what problems are you having? On quick glance, that looks valid.

3

The number of users always seems to be 1. It’s a bit difficult to check but I have browsed to the page once in my normal browser and once with Tor.

4

How frequently are you running this? You’re deleting every 120 seconds. Or if you’re the one testing, your IP may never change.

5

That’s why I used Tor to give me a second IP address. I’ve also downloaded the database and checked there were 2 different IP addresses. If the code looks right I’ll see if I can try some other way to get more users. I wasn’t sure about

  $query = 'SELECT DISTINCT ip FROM usersonline;';
  $result = $db->query($query);
  $user = count($result);

Thanks for your help.

6

I’ve just double-checked, downloaded the sqlite db and looked at it with Firefox Sqlite Manager. There are 5 entries with 2 distinct IP addresses, but the answer still come up with 1 user online…

7

Okay, so I have found an answer. It is intelligent to say the least, but it seems to work. The count($result) didn’t seem to do the job, returning 1 each time so I now have:

  $query = 'SELECT DISTINCT ip FROM usersonline;';
  $result = $db->query($query);
  $user = 0;
  foreach ( $result as $row ) {
    $user++;
  }

which as I say seems to work but is a bit naff. Any ideas how to improve this would be most welcome…

8

If you var_dump($result) is it a multidimensional array?

9

With just one user online var_dump($result) gives me:

object(PDOStatement)#3 (1) {
  ["queryString"]=>
  string(36) "SELECT DISTINCT ip FROM usersonline;"
}
10

Should have caught this but I don’t do php that often. This should work…

$user = $result->num_rows;
11

Thanks but that gives me:
Notice: Undefined property: PDOStatement::$num_rows in users.inc.php on line 32
and it returns a null value.
I’m on PHP 5.5.15 and PDO Sqlite 3.8.4.3 if that makes any difference…

I read somewhere that a query such as:

$query = 'SELECT DISTINCT ip, COUNT(ip) FROM usersonline GROUP BY ip;';

might do the trick but I don’t know how to get the result if that’s true.

12

Huh. Quick research into SQLLite seems to indicate that num_rows doesn’t work. Suggestions are to use a fetchall() to load the dataset into an array, then a count of the array to get your count.

That query won’t solve your problems, that just adds how many of each ip you find.

13

Dave many thanks for all your help!

14

[quote=“gandalf458, post:11, topic:114519, full:true”]
$query = ‘SELECT DISTINCT ip, COUNT(ip) FROM usersonline GROUP BY ip;’;
[/quote]FYI, the DISTINCT here is completely redundant

GROUP BY produces distinct results by definition

15

Thanks R

16

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.