'Where' clauses syntax problem

4 posts by 4 authors in: Forums > CMS Builder
Last Post: October 30, 2008   (RSS)

By benedict - October 28, 2008

Hi guys,

Can you tell me what I'm doing wrong here.

To summarise what I want, I have a list of courses at a college, and for this page I only want Hospitality Courses to be presented on the page. Then I want to be able to further sub divide the VCE VET Courses of Hospitality into another section on the page.

When I put in the Where clauses to do this, no results come up, when I remove them I get all the results back (i.e. no restriction of Hospitality or VCE Vet courses) :

<?php
require_once "/home/jstockst/public_html/ects/cmsAdmin/lib/viewer_functions.php";

list($coursesRecords, $coursesMetaData) = getRecords(array(
'tableName' => 'courses',
'loadUploads' => '0',
'allowSearch' => '0',
'where' => 'area_of_interest = "Hospitality"',
));

list($coursesVceRecords, $coursesVceMetaData) = getRecords(array(
'tableName' => 'courses',
'where' => 'course_type = "VCE VET Course"',
));

list($course_home_pagesRecords, $course_home_pagesMetaData) = getRecords(array(
'tableName' => 'course_home_pages',
'where' => whereRecordNumberInUrl(1),
'limit' => '1',
));
$course_home_pagesRecord = @$course_home_pagesRecords[0]; // get first record


?>

Then my code down below is:

<?php foreach ($coursesVceRecords as $record): ?> <a href="<?php echo $record['_link'] ?>"><?php echo $record['course_title'] ?></a><br/>
<?php endforeach; ?>
<h3>POST SECONDARY COURSES </h3>
<?php foreach ($coursesRecords as $record): ?> <a href="<?php echo $record['_link'] ?>"><?php echo $record['course_title'] ?></a><br/>
<?php endforeach; ?>
<h3>SHORT COURSES</h3>
<?php foreach ($coursesRecords as $record): ?> <a href="<?php echo $record['_link'] ?>"><?php echo $record['course_title'] ?></a><br/>
<?php endforeach; ?>
<p>&nbsp;</p>
<p>
<?php if (!$coursesRecords): ?>
No records were found!<br/>
<br/>
<?php endif ?>


Now to go through what I have checked - the table name is correct, the field names are correct and the values in the Where clause are identical (I checked them in PHPMyAdmin to make sure I wasn't going insane).

Re: [benedict] 'Where' clauses syntax problem

By Perchpole - October 29, 2008

Hi, Benedict -

I'm no expert but I think your "where" code is too complex. Try this...

[font "Verdana"] [font "Verdana"]'where' => 'area_of_interest = "Hospitality"' && 'course_type = "VCE VET Course"',


:0)

Perchpole

Re: [benedict] 'Where' clauses syntax problem

By _kate_ - October 29, 2008

Could it be your placement of quotation marks?

Here's how yours are compared to examples of this type given in other threads:

'where' => 'area_of_interest = "Hospitality"',

'where' => " myfield = 'community' ",

Re: [benedict] 'Where' clauses syntax problem

By Dave - October 30, 2008

Hi benedict,

Are the area_of_interest or course_type fields multiselect fields? If so they might be padded with tab \t characters which is why they're not matching.

In any case, have a closer look at the data, either by removing the where or looking in phpMyAdmin. Something isn't matching quite right.

Try this more broad query that will match "Hospitality" anywhere in the content:

'where' => ' area_of_interest LIKE "%Hospitality%" ',

Let me know the results.
Dave Edis - Senior Developer
interactivetools.com