Show products related to the compnay - intval

3 posts by 2 authors in: Forums > CMS Builder
Last Post: July 30, 2012   (RSS)

By benedict - July 27, 2012

Hi guys, this one is driving me nuts because I have used the method before without problem:

I have a company section and a products section. Every time a product is added, I have a multiselect/checkbox list field (associated_company) where the user can tick which companies sell that product. This field is populated using num for values and title_english for labels from cms_companies

Now on the company page I am showing the detail page for the company, a list of other companies (so users can switch companies) and the list of products that have that company ticked as an associated company. The list of related products is not working (presenting zero results) when I am using this:

// load records from 'products_services'
list($products_servicesnavRecords, $products_servicesnavMetaData) = getRecords(array(
'tableName' => 'products_services',
'where' => "associated_company = '". intval($companiesRecord['num']) ."'" ,
'debugSql' => true, // optional, display SQL query, defaults to no
));


I set it up this way because one company can have many products, but one product can also relate to many companies.

The complete header code for this page is:

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


// load viewer library
$libraryPath = 'cmsAdmin/lib/viewer_functions.php';
$dirsToCheck = array('/home/jyfnge/public_html/','','../','../../','../../../');
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 record from 'companies'
list($companiesRecords, $companiesMetaData) = getRecords(array(
'tableName' => 'companies',
'where' => whereRecordNumberInUrl(0),
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
$companiesRecord = @$companiesRecords[0]; // get first record
if (!$companiesRecord) { dieWith404("Record not found!"); } // show error message if no record found

// load navigation records from 'companies'
list($companiesnavRecords, $companiesnavMetaData) = getRecords(array(
'tableName' => 'companies',
'loadUploads' => true,
));

// load records from 'products_services'
list($products_servicesnavRecords, $products_servicesnavMetaData) = getRecords(array(
'tableName' => 'products_services',
'where' => "associated_company = '". intval($companiesRecord['num']) ."'" ,
'debugSql' => true, // optional, display SQL query, defaults to no
));
?>


Debug SQL spits this out:

SELECT SQL_CALC_FOUND_ROWS `products_services`.* FROM `cms_products_services` as `products_services` WHERE (associated_company = '3') ORDER BY dragSortOrder DESC

Re: [benedict] Show products related to the compnay - intval

By Jason - July 30, 2012

Hi,

This is actually a pretty common problem. The issue here is when you used a multiselect list field. In CMS Builder, these fields are stored as a string separated by tab (\t) characters. You can fix this problem but chaning your where caluse slightly like this:

// load records from 'products_services'
list($products_servicesnavRecords, $products_servicesnavMetaData) = getRecords(array(
'tableName' => 'products_services',
'where' => "associated_company LIKE '%\t". intval($companiesRecord['num']) ."\t%'" ,
'debugSql' => true, // optional, display SQL query, defaults to no
));


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/