Adding criteria to a 'where' clause

5 posts by 2 authors in: Forums > CMS Builder
Last Post: January 25, 2011   (RSS)

By gkornbluth - January 24, 2011

Hi All,

I have a where clause that limits event records returned to those records where the start date of the events occur during the next 7 days:

<?php
list($eventsRecords, $eventsMetaData) = getRecords(array(
'tableName' => 'events',
'where' => '((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00")) OR ((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00"))',

'orderBy'=> 'start_date ASC',
));

?>
To this I need to add the additional criteria of only returning event records where the checkbox field 'performance_or_competition' is checked (= 1)

I tried:
<?php
list($eventsRecords, $eventsMetaData) = getRecords(array(
'tableName' => 'events',
'where' => '((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00")) OR ((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00")) AND $performance_or_competition = 1',

'orderBy'=> 'start_date ASC',
));

?>

'where' => '((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00")) OR ((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00")) AND $performance_or_competition = 1',
but obviously this returned an undefined index. Leaving off the $ didn't seem to help either.

I looked through a number of posts for an answer, and I guess I don’t really understand how to build where clauses.

Thanks for your help.

Jerry Kornbluth
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Re: [gkornbluth] Adding criteria to a 'where' clause

By Jason - January 24, 2011

Hi Jerry,

When building where clauses, you're dealing directing with the field names in your database. So, if the name of your checkbox is performance_or_competition then the clause:

'where' => "((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00")) OR ((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00")) AND performance_or_competition = 1",

should work. You definitely would want the $ as that is referring to a PHP variable. If you run the above where clause do you get an error message? What is the message you get?

If you're getting an "Unknown Column" error, double check to make sure you're using the correct column name.

Let me know.
---------------------------------------------------
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] Adding criteria to a 'where' clause

By gkornbluth - January 24, 2011 - edited: January 24, 2011

Jason,

Thanks for diving in.

Here are my results so far.

(This is a multi record editor if that makes any difference)

With the code as you gave it to me I get a blank page.
<?php
list($eventsRecords, $eventsMetaData) = getRecords(array(
'tableName' => 'events',
'where' => "((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00")) OR ((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00")) AND performance_or_competition = 1",

'orderBy'=> 'start_date ASC',

));
?>
When I change the double quotes to single quotes I get:

the page with all listings. It doesn't seem to matter if the checkbox is checked or not. the result is the same

<?php
list($eventsRecords, $eventsMetaData) = getRecords(array(
'tableName' => 'events',
'where' => '((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00")) OR ((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00")) AND performance_or_competition = 1',

'orderBy'=> 'start_date ASC',

));
?>


Whe I add the “?”
<?php
list($eventsRecords, $eventsMetaData) = getRecords(array(
'tableName' => 'events',
'where' => '((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00")) OR ((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00")) AND $performance_or_competition = 1',

'orderBy'=> 'start_date ASC',

));
?>
I get: MySQL Error: Unknown column '$performance_or_competition' in 'where clause' but I recopied and pasted the field name and it is correct.

Thanks,

Jerry
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Re: [gkornbluth] Adding criteria to a 'where' clause

By Jason - January 25, 2011

Hi Jerry,

Once thing I noticed is that the two clauses you have separated by an OR are actually the same, so you can remove one of them. Try this instead:

'where' => "((NOW() + INTERVAL 7 DAY) >= start_date AND start_date >= TIMESTAMP(CURDATE(), "00:00:00")) AND performance_or_competition = 1",

If this is bringing up no information, try adding 'debugSql' => true. You may have special fields in CMS Builder that are adding to your where clause or fields coming in through the URL string.

Hope that 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/