MYSQL Select Question

4 posts by 2 authors in: Forums > CMS Builder
Last Post: January 2, 2020   (RSS)

By theclicklab - December 11, 2019

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 theclicklab - January 1, 2020

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

Robin
Programmer
interactivetools.com