MYSQL Select Question
4 posts by 2 authors in: Forums > CMS Builder
Last Post: January 2, 2020 (RSS)
Hi there,
I have a user who has been tagged with multiple yacht types that they are interested in:
$CURRENT_USER['preferred_yacht_types']So the value in here might be something like this:
2 3 4 How do I setup the select statement to bring back all yachts that match any one of these values? This is what I have so far:
<?php // load records from 'yachts'
if ($CURRENT_USER['preferred_yacht_types']) {
$user_yacht_type = $CURRENT_USER['preferred_yacht_types'];
} else {
$user_yacht_type = "";
}
list($yachtsRecords, $yachtsMetaData) = getRecords(array(
'tableName' => 'yachts',
'where' => "type LIKE '". mysql_escape($user_yacht_type) ."'" . ' AND active ="1"' . ' AND hide ="0"' . ' AND private ="1"',
'loadUploads' => true,
'allowSearch' => false,
'useSeoUrls' => true,
'limit' => 3,
'debugSql' => true,
)); ?>Thanks in advance for your help
By robin - December 12, 2019
Hi theclicklab,
So $CURRENT_USER['preferred_yacht_types'] contains the preferred yacht types separated by spaces?
You can use a MySQL IN statement for this kind of thing:
https://www.w3resource.com/mysql/comparision-functions-and-operators/in-function.php
To you IN you'll need to make the data comma separated. Here is another forum post we can use as a base example:
https://www.interactivetools.com/forum/forum-posts.php?postNum=2243600#post2243600
In this case you might try something like:
if ($CURRENT_USER['preferred_yacht_types']) {
$preferred_yacht_types = explode(" ", trim($CURRENT_USER['preferred_yacht_types']));
$user_yacht_type = mysql_escapeCSV($preferred_yacht_types);
} else {
$user_yacht_type = "0";
}
list($yachtsRecords, $yachtsMetaData) = getRecords(array(
'tableName' => 'yachts',
'where' => "type IN (" . $user_yacht_type . ") AND active ='1' AND hide ='0' AND private ='1' ",
'loadUploads' => true,
'allowSearch' => false,
'useSeoUrls' => true,
'limit' => 3,
'debugSql' => true,
));
Hope that helps. Please let me know any questions. Thanks,
Robin
Programmer
interactivetools.com
Thanks for this, so I am still having an issues, the mysql debug is showing up the correct values:
SELECT SQL_CALC_FOUND_ROWS `yachts`.*
FROM `cms_yachts` as `yachts`
WHERE (type IN ('motor','sailing','catamaran','power-catamaran','gulet','motorsail','mega-yacht') AND active ='1' AND hide ='0' AND private ='1' ) but Im still not getting back any results. Is it because the type field in the yacht table is holding multiple text values separated by tabs? e.g.
" gulet "
" sailing gulet motorsail "
" gulet motorsail "Here's my code:
if ($CURRENT_USER['preferred_yacht_types']) {
$preferred_yacht_types = explode("\t", trim($CURRENT_USER['preferred_yacht_types']));
$user_yacht_type = mysql_escapeCSV($preferred_yacht_types);
} else {
$user_yacht_type = "";
}
list($yachtsRecords, $yachtsMetaData) = getRecords(array(
'tableName' => 'yachts',
'where' => "type IN (" . $user_yacht_type . ") AND active ='1' AND hide ='0' AND private ='1' ",
'loadUploads' => true,
'allowSearch' => true,
'useSeoUrls' => true,
'limit' => 3,
'debugSql' => true,
));By robin - January 2, 2020
Hi theclicklab,
Yes, it's the tabs that will be causing you trouble here. You'll need to use LIKE conditions to get those values. Unfortunately you can't use LIKE and IN at the same time. You'll need to construct your sql something like this: (searching for gulet and motorsail)
list($yachtsRecords, $yachtsMetaData) = getRecords(array(
'tableName' => 'yachts',
'where' => "(type LIKE '%\tgulet\t%' OR type LIKE '%\motorsail\t%') AND active ='1' AND hide ='0' AND private ='1' ",
'loadUploads' => true,
'allowSearch' => true,
'useSeoUrls' => true,
'limit' => 3,
'debugSql' => true,
));This is using LIKEs with ORs surrounded by brackets to achieve the same functionality as IN). The "\t" is a tab and the % is a wildcard. These help with the multivalue field setup in the cms.
Hope that helps! Please let me know any other questions. Thanks,
Robin
Programmer
interactivetools.com