CODEWITHSUNDEEP
phpmysql
Mark
Mark

Reputation: 111

Execute query after 1 hour

Attached the table sample. enter image description here

Every hour I want to get the most recent items added to my table. The table is populated from another source, and I need to track what the last id I received was and get all new items added since.

$check5 = "select * from media_ids";
$rs5 = mysqli_query($con,$check5);
if(mysqli_num_rows($rs5)==1)
{
$row = mysqli_fetch_assoc($rs5);
$media_id=$row['media_id'];
}

// execute query

Upvotes: 0

Views: 965

Answers (1)

Sean
Sean

Reputation: 1304

  1. You need to create a cronjob that runs hourly.

crontab -e and then add 0 * * * * php /path/to/script/script.php

  1. Add a column to your table called created, set the type to TIMESTAMP set the default value to CURRENT_TIMESTAMP

3a. -- Fetch everything in last hour script.php should look like:

<?php

$current = time() - 3600;
$currentMediaQuery = "select media_id from media_ids where created > {$current}"
$mediaQuery = mysqli_query($con, $currentMediaQuery);

if (mysqli_num_rows($mediaQuery) > 0) {
    while ($row = mysqli_fetch_row($mediaQuery)) {
         ... do stuff
    }
}

3b. Fetch 10, maintain a pointer as requested

<?php

$lastPointer = “select pointer from pointer_tracking”;
$lastPointerResult = mysqli_query($con, $lastPointer);
$lastPointer = mysqli_fetch_row($lastPointerResult)
$lastPointer = $lastPointer[0];

$currentMediaQuery = “select * from media_ids where id > {$lastPointer} limit {$lastPointer}, {$lastPointer +10}”;

$mediaQuery = mysqli_query($con, $currentMediaQuery);

if (mysqli_num_rows($mediaQuery) > 0) {
    while ($row = mysqli_fetch_row($mediaQuery)) {
        do stuff…
    }
}

if (($lastPointer + 10 > 40) {
    $lastPointer = 1;
} else {
    $lastPointer += 10;
}

mysqli_query($con, “update pointer_tracking set pointer = {$lastPointer}”);

You'll need to create a table called pointer_tracker with an id and pointer column, both integer types

Upvotes: 4

Related Questions