Results 1 to 3 of 3
Mar 10, 2010, 17:22 #1
mysql_fetch_assoc error while using foreach
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;
[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
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
Mar 10, 2010, 17:25 #2
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.
Mar 10, 2010, 17:31 #3
- Join Date
- May 2006
- Lancaster University, UK
- 2 Post(s)
- 0 Thread(s)
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.Jake Arkinstall
"Sometimes you don't need to reinvent the wheel;
Sometimes its enough to make that wheel more rounded"-Molona