Main
Index 		Search
Posts 		Who's
Online 		Log
In

Home: Products: CMS Builder:
where not working?

 
 


Toledoh
Enthusiast

Nov 14, 2011, 7:50 PM

Post #1 of 9 (713 views)
Shortcut
where not working? Can't Post
why does this work
Code
'where'       => 'location="Manly"',

and this work
Code
'where' => " date >= TIMESTAMP( DATE(NOW()) ) ",

but this NOT work:
Code
'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 Forrest
Toledoh Enterprises
www.toledoh.com.au


Jason
Staff / Moderator


Nov 14, 2011, 8:55 PM

Post #2 of 9 (711 views)
Shortcut
Re: [Toledoh] where not working? [In reply to] Can't Post
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 - Programmer 
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/ 


Toledoh
Enthusiast

Nov 14, 2011, 9:12 PM

Post #3 of 9 (708 views)
Shortcut
Re: [Jason] where not working? [In reply to] Can't Post
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 Forrest
Toledoh Enterprises
www.toledoh.com.au


Jason
Staff / Moderator


Nov 14, 2011, 9:44 PM

Post #4 of 9 (706 views)
Shortcut
Re: [Toledoh] where not working? [In reply to] Can't Post
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 - Programmer 
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/ 


Toledoh
Enthusiast

Nov 14, 2011, 9:46 PM

Post #5 of 9 (705 views)
Shortcut
Re: [Jason] where not working? [In reply to] Can't Post
here you go... thanks Jason!
Cheers,

Tim Forrest
Toledoh Enterprises
www.toledoh.com.au
Attachments: manlyGigs.php (4.36 KB)


Jason
Staff / Moderator


Nov 15, 2011, 9:35 AM

Post #6 of 9 (683 views)
Shortcut
Re: [Toledoh] where not working? [In reply to] Can't Post
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:

Code
  $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 - Programmer 
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/ 


Toledoh
Enthusiast

Nov 15, 2011, 3:29 PM

Post #7 of 9 (678 views)
Shortcut
Re: [Jason] where not working? [In reply to] Can't Post
That's it Jason! Thanks!

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

Tim Forrest
Toledoh Enterprises
www.toledoh.com.au
Attachments: manlyGigs.php (4.39 KB)


Jason
Staff / Moderator


Nov 15, 2011, 9:35 PM

Post #8 of 9 (672 views)
Shortcut
Re: [Toledoh] where not working? [In reply to] Can't Post
Hi Tim,

You can fix this problem by checking to see if any records where returned before executing the array_combine() function. Try this:

Code
$artistsByNum = array(); 
  if ($artistRecords) {  
    $artistsByNum = array_combine(array_pluck($artistRecords, 'num'), $artistRecords); 
  }

Hope this helps
---------------------------------------------------
Jason Sauchuk - Programmer 
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/ 


Toledoh
Enthusiast

Nov 16, 2011, 1:14 PM

Post #9 of 9 (649 views)
Shortcut
Re: [Jason] where not working? [In reply to] Can't Post
That did it! Thanks a heap Jason.
Cheers,

Tim Forrest
Toledoh Enterprises
www.toledoh.com.au