SQL Query based on last 48 hours?

[Marco van Herwaarden]

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]

Originally Posted by 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]

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

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.

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]

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]

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.

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]

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]

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

Regards,