Where = limited results

17 posts by 3 authors in: Forums > CMS Builder
Last Post: April 27, 2011   (RSS)

By degreesnorth - April 13, 2011

Hi

I'm struggling with what I thought was going to be an easy "where = product" type scenario, but it's not coming together. I just want the page to limit the results to the amplifer category (which has a dozen or so subcategories), but get the message of "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 ') AND product_sub_categories.hidden = 0 ORDER BY dragSortOrder DESC' at line 3".

My code is:

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php


// load viewer library
$libraryPath = 'cmsAdmin/lib/viewer_functions.php';
$dirsToCheck = array('/clientdata/zeus-dynamic-1/a/u/audiomarketing.com.au/www/','','../','../../','../../../');
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($product_sub_categoriesRecords, $product_sub_categoriesMetaData) = getRecords(array(
'tableName' => 'product_sub_categories',
'where' => ' content="Amplifiers", ',
));



Note: the content field is generated from a multi list record where content = a specific group of products, and for example, amplifiers is record 1.

I haven't seen this error message previously, so am terribly confused! Help pls.

Re: [degreesnorth] Where = limited results

By Dave - April 13, 2011

Hi degreesnorth,

Try dropping the trailing comma and change:
'where' => ' content="Amplifiers", ',
to:
'where' => ' content="Amplifiers" ',

Let me know if that works for you.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Where = limited results

By degreesnorth - April 14, 2011

Hi Dave
Thanks. Perfect. Just one more question. I am trying to link a product category page to a product subcategory page, but it keeps asking me to set the page viewer. There is no "generic" page viewer for each subcategory (there are 49 of them), so how do I overcome this and still make the link work. For example, the first product subcategory on this page http://www.audiomarketing.com.au/category_loudspeakers.php should go to http://www.audiomarketing.com.au/category_architectural_speakers.php, then for example, the bookshelf speakers to http://www.audiomarketing.com.au/category_bookshelf_speakers.php, but I've tried every variation of code for the link and it's not working. Any ideas? Thanks!

Re: [degreesnorth] Where = limited results

By Dave - April 14, 2011

Hi degreesnorth,

Usually you can just use the _link field but in a case like this it is simplest to just hard-code your own link. It might be something like this:

<a href="category_architectural_speakers.php?<?php echo $record['num']">">link</a>

Or if there's different files for each subcategory you can write a little code to get the right link for each (sub)category number.

<?php
$link = "default-file.php";
if ($record['category'] == 1) { $link = "category_architectural_speakers.php"; }
if ($record['category'] == 2) { $link = "category_loudspeakers.php"; }
$link .= "?";
$link .= $record['num'];
?>

<a href="<?php echo $link ?>">link</a>

Let me know if that works (or makes sense). Thanks! :)
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Where = limited results

By degreesnorth - April 15, 2011

Thanks...seeing what you've provided, is there also a way to write the options for "where", ie, "where = category 1, 2, 3, etc, rather than developing seperate pages for every category group?

Re: [degreesnorth] Where = limited results

By Jason - April 15, 2011

Hi,

You can put multiple categories in your where clause like this:

'where' => "category = 'category 1' OR category = 'category 2' OR category = 'category 3' ",

Hope this 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: [degreesnorth] Where = limited results

By Dave - April 15, 2011

You may need to explain a little bit more about how your pages are setup and what you are trying to do so we can help solve the bigger problem, but another way to do multiple values in a where is like this:

'where' => "category IN('category 1', 'category 2', 'category 3') ",

But both work.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Where = limited results

By degreesnorth - April 17, 2011

Hi Dave
No, that doesn't work. It works for the first one, but this code then doesn't pick up the subsequent links and merely directs it to a wrong page.

Where I use the category code:
<?php
$link = "default-file.php";
if ($record['category'] = 4) { $link = "category_architectural_speakers.php"; }
if ($record['category'] == 8) { $link = "category_bookshelf_speakers.php"; }
if ($record['category'] == 19) { $link = "category_floorstanding_speakers.php"; }
if ($record['category'] == 32) { $link = "category_outdoor_speakers.php"; }
if ($record['category'] == 13) { $link = "category_computer_speakers.php"; }
if ($record['category'] == 29) { $link = "category_lifestyle_speakers.php"; }
if ($record['category'] == 7) { $link = "category_sub_woofers.php"; }
if ($record['category'] == 7) { $link = "category_home_theatre_speakers.php"; } $link .= "?";
$link .= $record['num'];
?>

<a href="<?php echo $link ?>"><font color="#7E7957"><b>|| detailed info</b></a>

or replace the numbers with a 1, 2, 3, it always just goes to the first one (ie, category_architectural_speakers.php)

Any other suggestions? The page is at: http://www.audiomarketing.com.au/category_loudspeakers.php

Thanks

Re: [degreesnorth] Where = limited results

By Dave - April 18, 2011

First, add an equals here:

if ($record['category'] == 4) { $link = "category_architectural_speakers.php"; }

And if that doesn't fix it then attach the viewer php file so we can see the code.

Thanks!
Dave Edis - Senior Developer
interactivetools.com