CODEWITHSUNDEEP
phpmysql
user1372896
user1372896

Reputation: 534

Checking the last 5 records in MySQL rather than the last

I cannot seem to get my head around this.

I currently have:

$checkRunningSQL = $odb -> prepare("SELECT time,date FROM `api` WHERE `key` = :key  AND `time` + `date` > UNIX_TIMESTAMP()");
$checkRunningSQL -> execute(array(':key' => $_GET['key']));
$countRunning = $checkRunningSQL -> fetchAll();
$stillrunning = ($countRunning[0][time] + $countRunning[0][date]) - time();
if($stillrunning >= 1)
{
echo '<p><strong>ERROR: </strong>You currently have more than your allocated running.</p>';
die();
}

This currently checks in a table such as this:

enter image description here

This is working fine if they have 1 current one running. However I was wanting to make a limit per key. Just now if they have one currently running say with 10 seconds, they cannot launch another one until those 10 seconds are up. But what I was wanting is so that they can launch 5, and if they try and launch a 6th it will echo the error.

When they request the page, they specify arguments which then gets entered into the MySQL:

$insertLogSQL = $odb -> prepare("INSERT INTO `api` VALUES (:key, :ip, :port, :time, :method, UNIX_TIMESTAMP())");
$insertLogSQL -> execute(array(':key' => $_GET['key'], ':ip' => $_GET['host'], ':port' => $_GET['port'], ':time' => $_GET['time'], ':method' => $_GET['method']));

I cannot seem to get my head around how to limit them to 5 instead on one single one. Any ideas throws at this would be brilliant.

Upvotes: 0

Views: 84

Answers (2)

Jason Larke
Jason Larke

Reputation: 5599

Could you just (prior to the insert) do a check on the current amount of running instances for the API key? Something like:

$SESSION_LIMIT = 5;
$precheck = $odb->prepare("SELECT count(*) AS `active_sessions` FROM `api` WHERE `key` = :key AND (UNIX_TIMESTAMP() BETWEEN date AND (date + time))");
$precheck->execute(array(':key' => $_GET['key']));
if (($result = $precheck->fetch()) && $result['active_sessions'] >= $SESSION_LIMIT) {
    // too many sessions
    echo "Can't create any more sessions at this time";
}

Upvotes: 1

Paul
Paul

Reputation: 27423

Before returning any page, but only on valid requests, insert session_id, unix_time into a usage table.

Then when requesting a page, do a select count(*) from the usage table where session_id=my_session_id and time>time_now-10 seconds. If the count is greater than 5, return an error message (system is busy now, please wait 10 seconds).

Upvotes: 0

Related Questions