where not working?
9 posts by 2 authors in: Forums > CMS Builder
Last Post: November 16, 2011 (RSS)
By Toledoh - November 14, 2011
'where' => 'location="Manly"',and this work
'where' => " date >= TIMESTAMP( DATE(NOW()) ) ",but this NOT work:
'where' => 'location="Manly" AND date >= TIMESTAMP( DATE(NOW()) ) ',I get an error: MySQL Error: 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 ')) ORDER BY artist' at line 3
Tim (toledoh.com.au)
Re: [Toledoh] where not working?
By Jason - November 14, 2011
Can you use the
'debugSql' => true,
and post the results? That should give us a better idea of what the issue you're running into is.
Hope this helps
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] where not working?
By Toledoh - November 15, 2011
I now get the error:
SELECT SQL_CALC_FOUND_ROWS `gigs_events`.* FROM `cms_gigs_events` as `gigs_events` WHERE (location="Manly" AND date >= TIMESTAMP( DATE(NOW()) ) ) ORDER BY date MySQL Error: 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 ')) ORDER BY artist' at line 3
Tim (toledoh.com.au)
Re: [Toledoh] where not working?
By Jason - November 15, 2011
The query you posted looks okay. One thing I noticed is that that query uses ORDER BY date, and the error message references ORDER BY artist. This leads me to believe that the error is actually occuring in a different query on that page. Could you attach your .php file so I can see all the code on the page?
Thanks
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] where not working?
By Toledoh - November 15, 2011
Tim (toledoh.com.au)
Re: [Toledoh] where not working?
By Jason - November 15, 2011
My best guess is that the error is occurring in the query of the artist table. The only problem I can see is if there is no value for artist in $gig_eventRecords. That would mean that $artistNums would have no value, causing an error when used in the MySql IN statement.
Try the code below to handle that event:
$artistNums = join(',', array_pluck($gigs_eventsRecords, 'artist'));
if (!$artistNums) { $artistNums = "0"; }
list($artistRecords,) = getRecords(array(
'tableName' => 'artist',
'where' => "num IN ($artistNums)",
'allowSearch' => false,
));If this happens, there will be no artist records returned, but it won't cause a MySQL error.
Give that a try and let me know what you find. If that doesn't work, please do a "debugSql" on the artist query as well, so we can see what's happening there.
Hope this helps
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] where not working?
By Toledoh - November 15, 2011
However, if there are no artists, I get a new error
Warning: array_combine(): Both parameters should have at least 1 element in /home/murraysb/public_html/manly/manlyGigs.php on line 36
Any way I can eliminate that error?
Tim (toledoh.com.au)
Re: [Toledoh] where not working?
By Jason - November 16, 2011
You can fix this problem by checking to see if any records where returned before executing the array_combine() function. Try this:
$artistsByNum = array();
if ($artistRecords) {
$artistsByNum = array_combine(array_pluck($artistRecords, 'num'), $artistRecords);
}Hope this helps
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] where not working?
By Toledoh - November 16, 2011
Tim (toledoh.com.au)