Forum Moderators: coopster
¦ voteID ¦ vote1 ¦ vote2 ¦ ipAddress ¦ sessionID ¦ browserInformation ¦ time ¦
I need time to be a current timestamp inserted by MySQL does anyone know how to do that in the create table command.
Also, I need to select from the table where time is equal to some time variable + 5 minutes.
The psuedo would look like this.
Select count(*) from tempVoteTable where (time < aTimeVariable + 5 minutes)
What I mean is lets say there are 10 votes that happened between 10:10 and 10:15 I would want my count to return 10.
Any ideas? thanks,
It is pretty easy to work with timestamps
You could just use mktime() for your insert. Then, sionce a timestamp is seconds, you can just add 5 mins (300 seconds) for your selects.
though someone could post the queries for using mysql built ins for the same thing ;)
Sorry I can't format it better.
Basically the second to last column is the unix timestamp.
So if I was checking this let's say tomorrow and I wanted to start with the first row and grab the count of every vote that happened within 5 minutes after that how would I do that with MySQL.
pseudo something like this
select count(*) from tempVotes where time <+ time + 5 minutes;
[edited by: jatar_k at 6:01 pm (utc) on Oct. 30, 2008]
[edit reason] specifics and sidescroll [/edit]
/edit..
Sorry, missed what you needed.
You'll need 2 queries if you want to get the number of queries within 5 minutes of the last entry.
query 1 gets the last entry
query 2 counts the votes using the last entry in the WHERE clause
Not tested but:
$query1 = mysql_query("SELECT `time` FROM table ORDER BY `time` DESC LIMIT 1");
$row1 = mysql_fetch_row($query1);
$last_time = $row1[0];
$query2 = mysql_query("SELECT count(*) FROM table WHERE `time` <= ($last_time - (60*5))");
$row2 = mysql_fetch_row($query2);
$count = $row2[0]
//this function checks all of the ips that
//need to be checked to see if there are
//more than 5 votes in 1 minute by that ip
//if there are then we mark all votes from
//that ip as bad
function checkIpTime(){
//the function that returns ips that need to be checked
$ipCheckArr = ipReturn();
$ipBadArrTime = array();
foreach($ipCheckArr as $i){
//and array to store all of the times
$tempTimeArr = array();
$query = "select time from tempVotes where ipAddress = '{$i}';";
$result = queryDB(connectDB(), $query);
while($row = $result->fetchRow(DB_FETCHMODE_ASSOC)){
array_push($tempTimeArr, $row['time']);
}//while
//now we run a check on the timestamps
foreach($tempTimeArr as $j){
//add 60s (1 min) to the time and count how many
//match that criteria
$tempTime = $j + 60;
$query = "select count(*) from tempVotes where (ipAddress = '{$i}' and time < {$tempTime} and time >= {$j});";
$result = queryDB(connectDB(), $query);
while($row = $result->fetchRow(DB_FETCHMODE_ASSOC)){
//if the count is bad store the ip in a bad array
if($row['count(*)'] >= 5){
array_push($ipBadArrTime, $i);
}//if row
}//while
}//foreach tempTimeArr
}//foreachipCheckArr
$ipBadArrTime = array_unique($ipBadArrTime);
//now set the bad ips
foreach($ipBadArrTime as $i){
$query = "update tempVotes set good = 'b' where ipAddress = '{$i}';";
$result = queryDB(connectDB(), $query);
}//foreach ipBadArrTime
}//checkIpTime
