Update data in db based on last update time from a table

Question

I have been searching a lot in the past few days on listing fb likes in database. I have got help from here too. Now i know how to get likes data from fb, read the results and update it in database.

When I get likes of 100+ fb pages in a loop and if my end users are more, it puts a load on the database and the code runs very low. So I made two links. first link (update.php) updates the data in database and second (index.php) shows the figures to user which runs fast. Now I want to add an auto update feature every one hour.

I know the logic but dont know how to convert it in syntax. I thought of making a 'update' table with only one field which would be of datatime type. When the end user visits the link, it will first read the datetime value from the 'update' table. If the difference between current time and last updated time from the table is more than one hour then run a function (i already wrote one) else run the remaining part of the code without running the function.

Please advice if this logic is correct and help me with the syntax.

Editing below after being put on hold

I have a code (in update.php) that reads 'Likes' from fb pages and puts it in my database fbdb in table fbtable under the field fblikes. This is working. I have another code (in index.php) that reads data from fbtable and shows the fblikes field values in front end. The end user will always see old Likes... that is, the data available in fbtable. The updated likes will be shown to user only if i run the update.php file. I am not merging both the files because if I have 100+ pages, the page loads very slow and that is why I wanted a function that will check the last updated datetime in 'update' table and if the difference of current time and last updated time is more than 1 hour then the update.php should run to ensure that the data is updated every 1 hour.

<?php
include "conn.php";
?>

<html>
<head>
.
.
</head>

<body>
read datetime from 'update' table
if current time - last updated time > 1 hour
run the function
else
do below task
.
.
show data from database to end user
.
.
</body>

</html>

Thank you

edited my question for more info

Mumbai CabinCrew
– Mumbai CabinCrew

2014-09-20 10:30:30 +00:00
Commented Sep 20, 2014 at 10:30 — Mumbai CabinCrew
– Mumbai CabinCrew, Commented Sep 20, 2014 at 10:30

Ian Kenney · Accepted Answer · 2014-09-25 09:29:53Z

1

First up, you could look at scheduling the update task to run each hour using your operating system, but if you want to follow the approach you suggest...

assuming a last_update table (simplified)

create table last_update ( updated_at datetime);

then you could send a query to look for updates in the last hour

select max(updated_at) last_updated
from last_update 
where updated_at > now() - interval 1 hour

this will return the last update time if an update happened in the last hour or no rows if it did not

or

select count(*) up_to_date 
from last_update 
where updated_at > now() - interval 1 hour

this will return the number of updates that happened in the last hour or no rows if it did not

you can test the result of the query to decide whether to run your update function

http://sqlfiddle.com/#!2/7cf706/2

answered Sep 25, 2014 at 9:29

Ian Kenney

6,4561 gold badge27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update data in db based on last update time from a table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related