"Where" syntax for multiple choice

Try using IN operator instead of equals:

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

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

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>

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

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?

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

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

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