Count Entries after Date

sstonge

Hi,

I've been using a script to count votes for pictures. Every time somene votes it records their ip and date they voted. It adds a new line to the table for every vote. (picNum is the picture they are voting for)

$query = "INSERT INTO kicks_voting (picture, ip, date) VALUES ('" . $_POST['picNum'] . "','" . $_SERVER['REMOTE_ADDR'] . "', '" . date('m.d.y') ."')";

Then when I want to see the total votes for a picture I use this script

$query = 'SELECT ip FROM kicks_voting WHERE picture ="' . $picNum . '"';   

$result = mysql_query($query); 
mysql_num_rows($result);
echo mysql_num_rows($result);

What I need to do now is count only the votes that came in after a specific date. So I can show total votes, and then votes this month. I know there must be an easy way to do this... As you can probably tell from my code I am not the best PHP programmer 🙂

devinemke

to see all votes for the current month:

$sql = 'SELECT COUNT(*) FROM kicks_voting WHERE picture = ' . $picNum . ' AND YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE())';

sstonge

Looks like that will work got getting all the votes for the current month. But I think I worded my question wrong, sorry.

I may need more then just the votes for the current month. I need to count all votes that were recorded after a specific day.

devinemke

$sql = "SELECT COUNT(*) FROM kicks_voting WHERE picture = " . $picNum . " AND date > '2006-09-15'";

sstonge

dosen't seem to work.

Is it because my date is formated 09-15-2006 and not 2006-09-15. It is returning a lower number then the total votes, but still a lot more votes then it should

devinemke

sstonge wrote:
Is it because my date is formated 09-15-2006 and not 2006-09-15.

then it appears that you are storing your dates as strings instead of dates. you should be using one of mySQL's Date and Time Types

sstonge

Too late to change all the entries in my database. I created a new table and now each vote will be counted twice. I got the result I was look for doing that.

Thanks for the suggestions. From not on I will be sure to use the mySQL date no the PHP.

devinemke

sstonge wrote:
Too late to change all the entries in my database.

why is it too late? create a new DATE column in your table and write a simple script that loops thru each row and updates the new DATE column with the value from the old string column using [man]strtotime[/man] and [man]date[/man]