In the middle of writing a quick support page email notification system for Touchwork, I realised that I needed to add a check which would limit a user from submitting a query or bug report to a certain amount of entries per hour.
The easiest solution as it turned out was to run the check using a clever little SQL string, which looked like this:
SELECT * FROM `touchwork-support` WHERE DATE_SUB(NOW(),INTERVAL 1 HOUR) <= `ts-timestamp`
ts-timestamp is nothing more than a datetime column in the table, populated with the time of submission on every record submit. The SQL itself calculates the date time left over when subtracting an hour from the current date time, which is obviously then compared against the timestamp column to grab query-satisfying records.
With the result in hand, you simply check the num_rows value of the record set and if it is larger than the cut-off you imposed, return a suitable error message.
Couldn’t be easier! :)
For a more complete example:
$sql = “SELECT * FROM `touchwork-support` WHERE DATE_SUB(NOW(),INTERVAL 1 HOUR) <= `ts-timestamp`”;
$result = mysql_query($sql);
if (mysql_num_rows($result) >= 4)
{
echo ‘Oopsie!’;
}
You might also enjoy:
-
Adding an hour to the current time turns out to be pretty easy thanks to the fantastic strtotime PHP function. For example, should we wish to grab the ho ... -
Sometimes it is quite handy to see which records based on a particular key appear more than once in a database. The trick here is that we are focusing on a ... -
I have to process some large CSV files that generate a lot of SQL statements that need to be executed. Naturally, trying to parse any of the files almost al ... -
That's what I love about Google. They're like Microsoft, only going about their grab for absolute power more covertly, making the masses love them instead o ...
-
If you are working on an Ubuntu server installation that comes in the command line only variation, you might at any point in time want to see just what exac ...
