Adding criteria to a 'where' clause

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

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/

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

Jason,

Thanks for catching that.

Yes they are identical, and I get a completely different (and correct) result with one removed.

Seems that the OR tells the logic to test for condition A, and then test for statement A AND B combined, and if either OR is true, list the records.

As if there were parentheses around the 2 "anded" conditions.

Anyway, the short answer is that it works now. (I still needed to change the double quotes to single quotes)

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