SQL Query based on last 48 hours?

[PhilMcKrackon]

I have a PHP file that I run as a cron task every 24 hours. Can some one help with a tweak to the query to only select matches that have occurred between the time the query is executed and through 48 hours before?

PHP Code:

$urls = mysql_query("SELECT linkid,linkurl FROM adv_links ORDER by linkid ASC");
while ($urlrow = mysql_fetch_array($urls)) {
fwrite($file," ");
fwrite($file, $urlrow[1]);
fwrite($file, " | "); 
fwrite($file," ");
fwrite($file, $urlrow[0]);
fwrite($file," \n");
} 


How would I change

$urls = mysql_query("SELECT linkid,linkurl FROM adv_links ORDER by linkid ASC");

to accomplish this?

Any help would be appreciated!

Thanks,

[MoT3rror]

Is there a time column in your table?

[PhilMcKrackon]

Yes there is - its "dateline" and as an example under PHPMyAdmin the field looks like "1207498258" for a particular entry. I am unsure how to read that with the SQL query and translate that to what I need.

Thanks for the reply,

[MoT3rror]

PHP Code:

$time  = mktime(date('H'), date('i'), date('s'), date("m"), date('d')-2, date('Y'));

$data = $db->query_read("SELECT * FROM table WHERE timecolumn > '". date('Y-m-d H:i:s', $time) . "'"); 


[PhilMcKrackon]

Thank you very much for the help. I'll give it a try.

[Eikinskjaldi]

More easily done within mysql

...where unix_timestamp() - timecolumn <= 3600*48

[PhilMcKrackon]

After many frustrating hours I'm still having a problem. I have tried multiple ways of writing the SQL query line but it still is not correct. I am using this code

PHP Code:

$time  = mktime(date('H'), date('i'), date('s'), date("m"), date('d')-2, date('Y'));
$urls = mysql_query("SELECT linkid,linkurl FROM adv_links WHERE dateline > '". date('Y-m-d H:i:s', $time) . "' ORDER by linkid ASC"); 


But my query is still returning all results from the beginning, not just the last two days. The query does sort by the correct order though. Any help would again be appreciated.

Thanks,

[Marco van Herwaarden]

Try:

PHP Code:

$urls = mysql_query("SELECT linkid,linkurl FROM adv_links ORDER by linkid ASC WHERE dateline > " . (TIMENOW - (2 * 24 * 60 * 60))); 


[PhilMcKrackon]

Originally Posted by Marco van Herwaarden

Try:

PHP Code:

$urls = mysql_query("SELECT linkid,linkurl FROM adv_links ORDER by linkid ASC WHERE dateline > " . (TIMENOW - (2 * 24 * 60 * 60))); 


Thanks for the help but that bit of code gives me the error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/*****/public_html/pagevisD.php on line 19

on the line after the SQL query call. I edited the SQL cal that works and changed linkid,linkurl to linkid,linkurl,dateline - then added fwrite($file, $urlrow[2]); to the output file as a test (and the date shows up) so I know it's not the dateline table entry that is the problem.

Other thoughts?

--------------- Added [DATE]1210086427[/DATE] at [TIME]1210086427[/TIME] ---------------

Originally Posted by Eikinskjaldi

More easily done within mysql

...where unix_timestamp() - timecolumn <= 3600*48

Sorry I missed this post, could you elaborate?

Thanks,

--------------- Added [DATE]1210108992[/DATE] at [TIME]1210108992[/TIME] ---------------

PHP Code:

$urls = mysql_query("SELECT linkid,linkurl FROM adv_links ORDER by linkid ASC WHERE unix_timestamp() - timecolumn <= 3600*48"); 


Eikinskjaldi, tried this as the query and I get the same PHP error as above -

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/*****/public_html/pagevisD.php on line 19

I have tried several variations of both syntaxes above and the error persists. I'll keep trying...

[Eikinskjaldi]

"timecolumn" is whatever field in your database contains the dateline data

its kicking an error because you dont actually have a field called "timecolumn"


It looks like your field is called dateline, so the sql is

[sql]
SELECT linkid,linkurl FROM adv_links
ORDER by linkid ASC
WHERE unix_timestamp() - dateline <= 3600*48
[/sql]