vb.org Archive - SQL Query based on last 48 hours?

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - SQL Query based on last 48 hours? (https://vborg.vbsupport.ru/showthread.php?t=178131)

PhilMcKrackon

05-04-2008 09:38 PM

SQL Query based on last 48 hours?

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

Code:

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

to accomplish this?

Any help would be appreciated!

Thanks,

MoT3rror

05-04-2008 11:18 PM

Is there a time column in your table?

PhilMcKrackon

05-04-2008 11:27 PM

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

05-05-2008 12:08 AM

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

05-05-2008 12:26 AM

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

Eikinskjaldi

05-05-2008 12:51 AM

More easily done within mysql

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

PhilMcKrackon

05-05-2008 01:46 PM

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

05-06-2008 10:43 AM

Try:

PHP Code:


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

PhilMcKrackon

05-06-2008 02:06 PM

Quote:

Originally Posted by Marco van Herwaarden (Post 1510259)

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

Quote:

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] ---------------

Quote:

Originally Posted by Eikinskjaldi (Post 1508786)

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 -

Quote:

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

05-07-2008 03:33 AM

"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]

Marco van Herwaarden

05-07-2008 08:21 AM

Quote:

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

Please post the entire part of the code where this query is executed and the rows are read.

PhilMcKrackon

05-07-2008 09:48 AM

Quote:

Originally Posted by Eikinskjaldi (Post 1511242)

That was my fault, I posted the code before I had edited it. Here is the code that I used and I still get the invalid MYSQL result resource in the fetch_array.

PHP Code:


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

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

while ($urlrow = mysql_fetch_array($urls)) {

fwrite($file," ");

fwrite($file, $urlrow[1]);

fwrite($file, " | "); 

fwrite($file," ");

fwrite($file, $urlrow[0]);

fwrite($file," \n");

}

fclose($file); 

?>

And the error

Quote:

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

I'm wondering if I have the edquotes in the wrong postion? The commented '//' out line is the original query line that works. I made sure that there was new data in the table during the last 48 hours so that the query should always yield a result. If I add 'dateline' to the query and add a fwrite for $urlrow[2] the date in the table is written to the file so dateline is the correct field.

Quote:

Please post the entire part of the code where this query is executed and the rows are read.

Marco, I used the same code above with the SQL query you supplied.

Thanks,

Marco van Herwaarden

05-07-2008 10:00 AM

PHP Code:


		
			
$urls = mysql_query("SELECT linkid,linkurl FROM adv_links ORDER by linkid ASC WHERE dateline > " . (TIMENOW - (2 * 24 * 60 * 60))); 
while ($urlrow = mysql_fetch_array($urls)) { 
fwrite($file," "); 
fwrite($file, $urlrow[1]); 
fwrite($file, " | ");  
fwrite($file," "); 
fwrite($file, $urlrow[0]); 
fwrite($file," \n"); 
} 
fclose($file);  
?>

Should work, providing that you have a valid MySQL database connection. (maybe post the entire script).

PhilMcKrackon

05-07-2008 10:18 AM

Thanks again but I still get the same error. I'm sure the DB connection is good as I can use the original query with no problems. I'm going to post the entire code also. Sorry to be of so much trouble.

Quote:

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

PHP Code:


		
			
<?php

#!/usr/lib/php

// Creates Text Input for PageVisualize

$host = "localhost";

$mysql_user = "*****";

$mysql_password = "*****";

$sdatabase = "******";

$slink = mysql_connect("$host", "$mysql_user", "$mysql_password") or die('Database Connection Failed. Wait a moment.');

mysql_select_db ("$sdatabase", $slink);

$file = fopen("TestPhpD.txt", "w");

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

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

while ($urlrow = mysql_fetch_array($urls)) {

fwrite($file," ");

fwrite($file, $urlrow[1]);

fwrite($file, " | ");

fwrite($file," ");

fwrite($file, $urlrow[0]);

fwrite($file," \n");

}

fclose($file);

?>

Marco van Herwaarden

05-07-2008 10:48 AM

Lol, mistake (in all these examples!!) you get by editing code and not read good what the result is.

The following is valid:

PHP Code:


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

- ORDER must be after the WHERE clause (can't believe everyone, including myself, made this same mistake)
- TIMENOW is only defined within a vBulletin environment, so replace by time()

PhilMcKrackon

05-07-2008 10:57 AM

Sucess! I was not aware that ORDER must always follow WHERE. I thank you ALL very much!

Regards,

All times are GMT. The time now is 05:26 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01908 seconds Memory Usage 1,805KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (10)bbcode_php_printable (9)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (16)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: