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,
));