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

Robin
Programmer
interactivetools.com

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

Robin
Programmer
interactivetools.com