Where help

7 posts by 2 authors in: Forums > CMS Builder
Last Post: May 7, 2018   (RSS)

By zip222 - May 7, 2018

Not sure what i'm doing wrong here

two tables, DATES and SESSIONS.

DATES includes a list field that allows multiple SESSIONS to be selected - field name is related_sessions.

On the public DATES page, I'm trying to load all of the related_sessions

// load record from 'dates'
list($date, $dateMetaData) = getRecords(array(
'tableName'   => 'dates',
'where'       => whereRecordNumberInUrl(0),
'loadUploads' => true,
'allowSearch' => false,
'limit'       => '1',
));
$date = @$date[0]; // get first record
if (!$date) { header('Location: /'); exit; }

// load records from 'sessions'
list($related_sessions, $related_sessionsMetaData) = getRecords(array(
'tableName'   => 'sessions',
'where' => "num LIKE '%\t". $date['related_sessions'] ."\t%'" , 
'loadUploads' => true,
'allowSearch' => false,
));

with debug on:

SELECT SQL_CALC_FOUND_ROWS `sessions`.*
FROM `cms_sessions` as `sessions`
 WHERE (num LIKE '% 1048 1047 %') AND `sessions`.hidden = 0 
 ORDER BY date_time DESC,location,type

Not record are returned. 

By leo - May 7, 2018

Hi,

It seems your related_sessions field is a multi-select field. Try getting the related_sessions value csv string first by explode() and use "num IN ()" instead.

Let me know if you have any questions.

Leo - PHP Programmer (in training)
interactivetools.com

By leo - May 7, 2018 - edited: May 7, 2018

Try this: $related_sessions = implode(',', explode("\t",$page['related_sessions']))

Leo - PHP Programmer (in training)
interactivetools.com

By zip222 - May 7, 2018

tried this...

$related_sessions = implode(',', explode("\t",$page['related_sessions']));

'where' => "num IN ($related_sessions)", 

Got this...

SELECT SQL_CALC_FOUND_ROWS `sessions`.*
FROM `cms_sessions` as `sessions`
 WHERE (num IN (,1048,1047,)) AND `sessions`.hidden = 0 
 ORDER BY date_time DESC,location,type
 LIMIT 2MySQL 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 '1048,1047,)) AND `online_sessions`.hidden = 0 ORDER BY date_time DESC,location' at line 3

By leo - May 7, 2018

Okay it seems that multi value field contains extra spaces. Try replace explode() with this one: preg_split('/[\t]/', trim($page['related_sessions']))

Leo - PHP Programmer (in training)
interactivetools.com

By zip222 - May 7, 2018

that worked. Thanks!