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.
interactivetools.com
By zip222 - May 7, 2018 - edited: May 7, 2018
Trying the following, but not working...
$related_sessions = explode("\t",$page['related_sessions']);
and...
'where' => "num IN ($related_sessions)",
getting this...
Notice: Array to string conversion in
By leo - May 7, 2018 - edited: May 7, 2018
Try this: $related_sessions = implode(',', explode("\t",$page['related_sessions']))
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']))
interactivetools.com