How to count records that have been entered within the last 24hours?

On one of my websites I am storing the IP addresses and the time of visit of each user, I am doing this successfully using a MySQL database with PHP. However, I want to retrieve how many times a user has visited within the last 24hours.

The MySQL table is called "Traffic" which has three fields; trid(Unique Traffic ID), logip(The logged IP address) and visittime(a DATETIME field that stores as 'now()' when the user visits).

All of the above works perfectly, but I don't know how to calculate the amount of visits each IP has made within the last 24hours! Here's what I was thinking;

$logip = $_SERVER['REMOTE_ADDR']; //User's IP Address

$query = "SELECT * FROM Traffic WHERE (visittime BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW()) AND logip='$logip'"; //Retrieve all entry's of User's IP Address within the last 24hours
$result = mysql_query($query) or die();
$num = mysql_num_rows($result); //count visits from user within last 24hours

PHP:

I know the code is PHP and this is posted within the MySQL forum, but it's the MySQL query I need help with

 

Okay, it seems the code in my original post actually DOES work.

The problem was with my PHP coding further down the page!

if ($num = 0) {
.............
}

Code (markup):

I only put one "=" in the code above! Silly me!

Either way, I simplified the MySQL query too;

I realised there wouldn't be any records that have a later date than now() in the table so I just made it search for records that are ">= DATE_SUB(now(), INTERVAL 1 DAY)"

Here it is now;

$logip = $_SERVER['REMOTE_ADDR']; //User's IP Address

$query = "SELECT * FROM Traffic WHERE visittime >= DATE_SUB(NOW(), INTERVAL 1 DAY) AND logip='$logip'"; //Retrieve all entry's of User's IP Address within the last 24hours
$result = mysql_query($query) or die();
$num = mysql_num_rows($result); //count visits from user within last 24hours

PHP:

It works

 

If you just wish to retrieve COUNT for the number of times a user has visited, use following query.

$query = "SELECT COUNT(*) AS visit_count FROM Traffic WHERE visittime >= DATE_SUB(NOW(), INTERVAL 1 DAY) AND logip='$logip'";

PHP:

Doing SELECT * will fetch all the records for give ip and for that matter, all columns of all records which is very heavy data just to know only count.

 

Why do I need the "AS visit_count" in that statement?

How do I translate the MySQL variable "visit_count" into a PHP variable?

--Mike

 

It is always good to use alias that is why i used "AS visit_count", you may however avoid it if wish to.

 

Why is it "always good"?

 

Say for example you were selecting 3 columns as is from table and 2 columns were derived using formula, while referring derived columns from PHP you either will have to provide its ID in retrieved array OR name (which in this case is exact formula written in mysql). For some reason, your query remained unchanged for long time and there after you happened to change it.

What will happen?
You will have to go and change the derived part everywhere you referred, OR if position of derived column is changed, then make appropriate changes in php code as well.

Whereas if you have alias, you never have to worry about any kind of changes in query, because what you gonna refer from PHP is not gonna change the way it is referred by so easily.

Not sure if above was good explanation, feel free to ask any questions you may have.

 

You need "AS visit_count" in that statement to make it easier to translate the count into a PHP variable. Doing that essentially assigns a field name to the value you calculated which makes it easy to reference when you pull it out of MySQL.

$result=mysql_query($query);
$row = mysql_fetch_array($result);
echo $row['visit_count'];

PHP:

'visit_count' will now be an element of your query results and it holds the value you queried the database for.