Where = limited results

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

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

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

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: [Dave] Where = limited results

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

Re: [Dave] Where = limited results

Hi Dave
No that doesn't work either. That goes to a "page cannot be found", which was the reason why I took it out. I have attached the php file... any light shed would be great!
Thanks
Attachments:

category_loudspeakers.php 20K