Ok so I have been trying to get this fixed for a while now, I dont ussally ask for help til I can no longer find the problem. I have no clue why the peice of code below does not work.
The php code below goes through my product db's and gets the daily totals. I have a cron that runs at 12:30 am my time that runs this script. The script works except the "all-in-one" database, it produces an error for the "all-in-one" db. If I took that db out then it will work fine.
I actually have this script running on other pages and it is running fine without any errors, same exact code to, so I have no clue why its not working.
Below is the main code:
$tables = array('networks', 'bookmarks', 'content', 'directory', 'increase', 'articles', 'forums', 'monthly_billing', 'sales_letter_forums', 'squidoo', 'wordpress', 'all-in-one');
foreach ($tables as $table)
$sql = "select sum(total) as earnings from $table where `date` = '$trimmed'";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
$earnings = round($row['earnings'], 2);
// Add the earnings from this product to the grand total.
// @ operator to avoid complaints of the variable not existing
// when we access it the first time and create it.
@$total += $earnings;
My errors logs:
[10-Mar-2010 15:40:39] PHP Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/out/public_html/cron/track-daily.php on line 20
[10-Mar-2010 15:40:39] PHP Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/out/public_html/cron/track-daily.php on line 37
And finally, this is what the script error reports:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'all-in-one where `date` = '2010-03-09'' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'all-in-one where `date` BETWEEN '2010-03-01' AND '2010-03-10'' at line 1
Any help with getting this fixed would be awesome, thanks in advance
Ok nevermind I figured it out. I added
around the $table part in the sql and now it works fine. That was a stupid fix haha, mods can delete this sorry for wasting your time.
No reason to delete this
It was a worthy lesson and one someone may learn from in the future.
Thanks for giving us an update though, that will save people some time and will help others looking for answers.
As you seemingly figured out, the issue is that some of those table names have characters in which may invalid the query - so
backticking them ensures that the database takes the table name literally.