SQL where clause

4 posts by 2 authors in: Forums > CMS Builder
Last Post: April 11, 2011   (RSS)

By JLynne77 - April 8, 2011

I'm attempting to use the 'where' clause in the php header code to filter the results on my page to a specific set of results. I do have a work around that much clunkier and gives me bad meta data for the pagination function, so I want to use the where clause to make sure people aren't clicking through blank pages to find their results.

This is what I have so far:
<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php


// load viewer library
$libraryPath = 'cmsAdmin/lib/viewer_functions.php';
$dirsToCheck = array('/usr/www/users/mathermd/mather/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

// load records
list($eventsRecords, $eventsMetaData) = getRecords(array(
'tableName' => 'events',
'perPage' => '25',
'where' => 'category = "Fundraising Raffles"',
));

?>
and
<?php foreach ($eventsRecords as $record): ?>
<tr>
<td valign="top" width="20%"><?php echo date("F j, Y", strtotime($record['date'])) ?><?php if ($record['end_date']): ?> - <?php echo date("F j, Y", strtotime($record['end_date'])) ?><?php endif ?></td>
<td valign="top" width="20%"><?php if ($record['time']): ?><?php echo $record['time'] ?><?php endif ?></td>
<td valign="top"><a href="<?php echo $record['_link'] ?>"><strong><?php echo $record['title'] ?></strong></a></td>
<td valign="top" width="75"><?php foreach ($record['picture'] as $upload): ?><?php if ($upload['hasThumbnail']): ?><img src="<?php echo $upload['thumbUrlPath'] ?>" width="<?php echo $upload['thumbWidth'] ?>" height="<?php echo $upload['thumbHeight'] ?>" alt="" /><?php endif ?><?php endforeach ?></td>
</tr>
<tr>
<td colspan="5">&nbsp;</td>
</tr>
<tr>
<td colspan="5" bgcolor="#999999"><img src="images/spacer.gif" height="1" /></td>
</tr>
<tr>
<td colspan="5">&nbsp;</td>
</tr>
<?php endforeach ?>
and
<?php if ($eventsMetaData['prevPage']): ?><a href="<?php echo $eventsMetaData['prevPageLink'] ?>">&lt;&lt; prev</a><?php else: ?>&lt;&lt; prev<?php endif ?>
- page <?php echo $eventsMetaData['page'] ?> of <?php echo $eventsMetaData['totalPages'] ?> -
<?php if ($eventsMetaData['nextPage']): ?><a href="<?php echo $eventsMetaData['nextPageLink'] ?>">next &gt;&gt;</a><?php else: ?>next &gt;&gt;<?php endif ?>
Everything appears to be display properly here, but I'm just not getting any results when I know I've selected "Fundraising Raffles" from the checkbox list I created for that category field.

Thank you in advance for your help. :)
-----
~Jessica Sullivan, Crystal Realm Designs.

Re: [Duches77] SQL where clause

By Dave - April 9, 2011

Hi Duches77,

Is the 'category' field a multi-value list field? If so it stores multiple values in a tab separated list, so the where needs to be a little different:

'where' => " category LIKE '%\tFundraising Raffles\t%' ",

Or, if it works to have that in the url you can make use of the automatic searching features with:

yourViewer.php?category=Fundraising Raffles

But, if neither of those work, the next step in debugging the problem would be to look at the data being returned and compare it to the where.

list($eventsRecords, $eventsMetaData) = getRecords(array(
'tableName' => 'events',
'perPage' => '25',
#'where' => 'category = "Fundraising Raffles"',
));

showme($eventRecords);
exit;

And to check what the value of 'category' is.

Let me know if any of those help or how far you get. Thanks!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] SQL where clause

By JLynne77 - April 11, 2011

Thanks, Dave. That first option with the LIKE operator worked perfectly to keep with the client's preferences on how they want the site laid out. :)
-----
~Jessica Sullivan, Crystal Realm Designs.