"Where" syntax for multiple choice

13 posts by 4 authors in: Forums > CMS Builder
Last Post: March 5, 2013   (RSS)

By degreesnorth - February 13, 2013

Hi

How would I go about writing a statement for     'where' => ' country_list="Vietnam", "Cambodia" , "Burma" , "Laos" ', (I only want to include these ones, not the rest) - I am doing something wrong as I'm getting a message of MySQL Error: Operand should contain 1 column(s). Any light on this problem would be greatl appreciated.

Thanks in advance.

By kitsguru - February 13, 2013

Try using IN operator instead of equals:

'where' => ' country_list IN("Vietnam", "Cambodia" , "Burma" , "Laos")

Jeff Shields

By gregThomas - February 13, 2013

Hi,

The easiest way to write this statement is to use the MySQL IN function. Here is an example of how to use it:

  // load records from 'blog'
  list($blogs, $blogMetaData) = getRecords(array(
    'tableName'   => 'blog',
    'loadUploads' => true,
    'where' => "country_list IN('Vietnam','Cambodia','Burma','Laos')",
    'allowSearch' => false,
  ));

  showme($blogs);

This will only return records where the Vietnam, Cambodia, Burma or Laos are in the coutry_list field.

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By degreesnorth - February 13, 2013

That doesn't work, so created another field called      'where' => "region IN('India','Bhutan','Sri Lanka')",

However, that's just causing another problem as I am trying to write (to only get Textile and Craft Tours)

// load records from 'escorted_tours' 
  list($escorted_tours, $escorted_toursMetaData) = getRecords(array(     'tableName'   => 'escorted_tours',     'loadUploads' => true,     'where' => "type_of_trip_activity IN ('Textiles & Crafts')",     'allowSearch' => false,   ));

  showme($escorted_tours);

But that's not working either with a message of "Undefined variable"r.  Is it because both the activites and countries_list are a seperate editor table, and I've linked them via a list in the escorted_tours table? (ie,   <?php echo join(', ', getListLabels('tours', 'type_of_trip_activity', $record['type_of_trip_activity'])); ?></td>

By gregThomas - February 14, 2013

Hi,

The problem will be that your linking them via the escorted tours table. You will need to change your code so the IN statement contains the values that you have stored the list field. (I've attached a screenshot which shows the fields values you need to enter in the IN function.)

For example, if your values for the country_list field is the num field from the escorted_tours table. Then your where statement might look like this:

  // load records from 'blog'
  list($blogs, $blogMetaData) = getRecords(array(
    'tableName'   => 'blog',
    'loadUploads' => true,
    'where' => "country_list IN(1,6,12,15)",
    'allowSearch' => false,
  ));

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com
Attachments:

example_006.png 14K

By Codee - February 28, 2013

I need to form a similar where statement in the page top area. Some of the users belong to multiple teams. The games list pulls in records based on the 'team' field in the users' account. Game records, of course, are in a separate section. Right now the coaches accounts were set up for just one team from a dropdown field ('team').  So if I change the 'team' field in the accounts section to be a multiple choice, it's now a list of teams or an array of teams that coach belongs to.

Right now, as a single choice the CMSB code at the top of the page looks like this:

// load records from 'games'
  list($gamesRecords, $gamesMetaData) = getRecords(array(
    'tableName'   => 'games',
    'loadUploads' => true,
    'where' => " (team_1 = '{$CURRENT_USER['team']}') OR '{$CURRENT_USER['isAdmin']}' ",
    'allowSearch' => false,
    'perPage'     => '18',
  ));

So if I change the 'team' field in user accounts to be a multiple choice dropdown, how do I change the WHERE statement to pull in game records for all the teams that user belongs to?

By gregThomas - March 1, 2013

Hi,

I've done some local testing, and I think you need to do something like this:


//Trim will remove the unrequired tab space at the beginning of the list. The team values will be tab separated, explode them into an array
$values = explode("\t",trim($CURRENT_USER['team']));
//implode the values into a comma seperated array
$values = implode(',',$values);

//The MySQL IN statement will retrieve any rows which appear in the $values list.
list($gamesRecords, $gamesMetaData) = getRecords(array(
  'tableName'   => 'games',
  'loadUploads' => true,
  'where' => " (team_1 IN($values)) OR '{$CURRENT_USER['isAdmin']}' ",
  'allowSearch' => false,
  'perPage'     => '18',
));

So I've converted the team list into a comma separated string, then used the MySQL IN function to find all rows where team_1 contains a value from that list.

Let me know if you have any questions.

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By Codee - March 1, 2013

Wow! Thanks! I'll put this in and try it out. I have never used the explode or implode commands but are intrigued by them. The short course I took at a community college did not actually teach syntax; how to read it, how to write it (and use it) properly...it was more of a "go find how and where someone else used it and copy it. Next." I was bummed so, I'm learning on my own. The help I receive in these forums and from IT directly has always been awesome! Thanks again!

By Codee - March 1, 2013

Hi Greg, It seems to work when a regular user logs in but when an admin logs in we get the following error:

MySQL 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 ')) OR '1' ) AND `games`.hidden = 0 ORDER BY dragSortOrder, played ASC LIMIT 1' at line 3

In the Advanced Tab for the games section editor, we do have the sorting set as:

dragSortOrder, played ASC

By gregThomas - March 4, 2013

Hi,

I think the problem is with the second part of the where statement after the OR, as it's displaying either 0 or 1 depending on if the user is an admin. The PHP is creating a statement like this:

  'where' => " (team_1 IN($values)) OR '1'",

Do you want to retrieve all records if the user is an administrator?

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com