where not working?

9 posts by 2 authors in: Forums > CMS Builder
Last Post: November 16, 2011   (RSS)

By Toledoh - November 14, 2011

why does this work
'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
Cheers,

Tim (toledoh.com.au)

Re: [Toledoh] where not working?

By Jason - November 14, 2011

Hi Tim,

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

Thanks Jason,

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
Cheers,

Tim (toledoh.com.au)

Re: [Toledoh] where not working?

By Jason - November 15, 2011

Hi Tim,

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

here you go... thanks Jason!
Cheers,

Tim (toledoh.com.au)
Attachments:

manlygigs.php 5K

Re: [Toledoh] where not working?

By Jason - November 15, 2011

Hi Tim,

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: [Toledoh] where not working?

By Jason - November 16, 2011

Hi Tim,

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

That did it! Thanks a heap Jason.
Cheers,

Tim (toledoh.com.au)