Need IMMEDIATE help with multiple WHERE statement in head or a switch with where - am clueless

9 posts by 2 authors in: Forums > CMS Builder
Last Post: March 6, 2013   (RSS)

By Codee - February 18, 2013

For ANYONE that can and will help me, please...(thanks in advance).  I have 3 cases that I want the WHERE statement to go through, in a specific order, when pulling the records for a section. The section is for games. Fields in a game record include team_1, team_2, (that pull data from a Teams section), organization (that pulls data from an Organization section), and league (that pulls data from a League section). Users have different skill levels (field 'skill_level' in user accounts) that correspond to their activity with the teams and their games. I'm part way to accomplishing the WHERE statement, but each case is completely different, and my limited understanding says WHERE statements can't be, or shouldn't be, too complex. And that if they are then maybe a variable should be used (like $where) somehow to combine the statements. Anyway one of the cases works well and it's for finding all the game records for a single team (we're using the Membership function). This first case WHERE statement is to load all the game records where the team denoted with the team_1 field is matched with a user that has the same team chosen in the account dropdown. The other part of this where statement is show all records if the user is the admin.

On a list page in the head area I have the records call as:

--------------------------

 // 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'     => '10',
  ));
   
// load referenced records for our fields
beta_lookupRelatedFields(array(
'table' => 'games',
'recordList' => &$gamesRecords,
'fieldList' => array('team_1','team_2','scout_team')
));

--------------------------------

So the next two cases, in order, are a) An organization is a collection of teams, so we want a user with an organization choice matching an organization that 6 teams belong, to see all the games for those 6 teams. So, something like

'where' => " (organization = '{$CURRENT_USER['organization']}')  "

and, b) the league owner gets to see all games for all teams in the league. It would be easy to give the league owner admin power, but we don't want to do that. So, something like

'where' => " (league = '{$CURRENT_USER['league']}')  "

So, show the order for the where statement is to pull records first for just a team logging in, unless it's an organization, unless it's a league. Would it be best to necessitate some type of switch statement? 

By Dave - February 18, 2013

Hi equinox, 

Here's my first try, but it might take a few. :)

We have a helper function called mysql_escapef() that lets you put questions marks where you want data inserted and then list the variables at the end.  Sometimes it makes it a little easier to read.  Here's an example:

where => mysql_escapef("team_1 = ? OR organization = ? OR league = ?",
                       @$CURRENT_USER['team'],
                       @$CURRENT_USER['organization'],
                       @$CURRENT_USER['team']),

That will return a record from 'games' if the team or organization or league matches that of the current user.  Let me know if that's what you want or if any of those fields are multi-value fields (in which case we need to change the query to see if they contain the value, not just if they match it exactly).

Hope that helps!

Dave Edis - Senior Developer
interactivetools.com

By Codee - February 19, 2013

THANKS, Dave! Let me try that (immediately because people are upset). Each of those fields are from a pulldown with single choice only.

By Codee - February 19, 2013

Just got a call...looks like in the accounts table, the dropdown for team may need to become a multiple choice instead of a single. How do we modify the code then, after we clear the error I wrote about above? THankS!

By Dave - February 19, 2013

Hi equinox, 

Notice: Use of undefined constant where - assumed 'where' in /.../fsyflvideo/games2.php on line 16 

Make sure you add quotes around the where like this 'where' or "where", sorry I missed those in my example.

MySQL Error: Unknown column 'organization' in 'where clause'

This means that the field 'organization' does not exist in the table 'games'.  Check the spelling, maybe it's organisation? 

...looks like in the accounts table, the dropdown for team may need to become a multiple choice instead of a single. How do we modify the code then,

That's a bit trickier.  So games would always have a single value for organization and users would have a multi value?  Or might games have a multiple value as well?

Dave Edis - Senior Developer
interactivetools.com

By Codee - February 19, 2013 - edited: February 19, 2013

Hi Dave,

1. It is not a spelling error, but you are correct that in the section Games there is no field "organization" as that field is actually a field in the Team section, which each game record calls to associate team_1, team_2 and scout_game. This is why I'm attempting to use the relatedRecords plugin (which to this day I still am horrible at grasping well. My understanding that by utilizing the related records script that any fields in the team records that are called would be available to the games records.

2. So games would always have a single value for organization and users would have a multi value?  Or might games have a multiple value as well?

That's the thing, neither the field 'organization' nor 'league' are actually in the games record, but they are part of each team record (from the Teams section) that is used to populate the game record fields 'team_1', 'team_2' and 'scout_game'...which I thought becomes available through the use of the relatedRecords plugin.

3. I thought that there needed to be some quotes or syntax grammar. I put single quotes around the WHERE, and I changed the second case for 'team' to 'league'. There is a new error:

Parse error: syntax error, unexpected T_DOUBLE_ARROW in /home/content/33/10223033/html/fsyflvideo/games2.php on line 20

Okay, so line 20 is the one with 'allowSearch' => false,  . In fact, here's the entire opening php head (with the domain info changed to not publish that in this forum):

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php require_once "/stuff/stuff/stuff/stuff/html/fsyflvideo/fsadmin/lib/viewer_functions.php"; ?>
<?php if (!$CURRENT_USER) { websiteLogin_redirectToLogin(); } ?>
<?php

// load viewer library
 $libraryPath = 'fsadmin/lib/viewer_functions.php';
  $dirsToCheck = array('/stuff/stuff/stuff/stuff/html/fsyflvideo/','','../','../../','../../../');
  foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
  if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }
 
  // load records from 'games'
  list($gamesRecords, $gamesMetaData) = getRecords(array(
    'tableName'   => 'games',
    'loadUploads' => true,
    'where' => mysql_escapef("team_1 = ? OR organization = ? OR league = ?",
                       @$CURRENT_USER['team'],
                       @$CURRENT_USER['organization']),
                       @$CURRENT_USER['league']),
    'allowSearch' => false,
    'perPage'     => '12',
  ));
 
 
// load referenced records for our fields
beta_lookupRelatedFields(array(
'table' => 'games',
'recordList' => &$gamesRecords,
'fieldList' => array('team_1','team_2','scout_team')
));

$gamesRecord = @$gamesRecords[0]; // get first record

  // load records
  list($bottom_menuRecords, $bottom_menuMetaData) = getRecords(array(
    'tableName'   => 'bottom_menu',
    'loadUploads' => true,
    'allowSearch' => false,
  ));
 
   // load records from 'topmenu'
  list($topmenuRecords, $topmenuMetaData) = getRecords(array(
    'tableName'   => 'topmenu',
    'loadUploads' => true,
    'allowSearch' => false,
  ));
 

?>

Also, as I'm learning...does the where-helper function you built need some kind of 'array' command with the case list or is that already built in to that function?

As always...thank you kindly!

By Dave - February 20, 2013

Hi equinox, 

As far as beta_lookupRelatedFields(), it was an experimental function Chris wrote which never ended up making it into the CMSB core and isn't something I've ever used.  So if it's working, you can continue using it, but I can't offer any support for it.

For figuring out the "where" logic, you have a lot going on with the code.  Anytime things start getting complex the best way to approach it is to make it simple.  How I do this when writing code is to imagine what instructions I'd give a human if I wanted them to perform the task.  I write those out, make sure they're simple and make sense, and only then do I try and turn it into PHP and MySQL.  This process forces me to make sure that I clearly understand what I'm trying to do before I code it up.  If I couldn't explain it to a person then there's no way I could write a program to do it.  Almost every time I do this I end up with a clearer picture of what I'm trying to do.

As an example, you might say... 

I have two tables, tablea and tableb, 

tablea has these fields:
categories (a multi value field that stores categories by number, getting the categories from the 'categories' table)
type (the type of record, possible values widget, sprocket, or misc)
...

tableb has these fields:
...

I want to return records from tablea on these conditions: 
The record field "type" is set to the same value as the current users "preferred types" field
etc

If you can write it out in human language, and make it really simple, it's very easy for me to help you turn it into PHP and MySQL.

Also, as I'm learning...does the where-helper function you built need some kind of 'array' command with the case list or is that already built in to that function?

It looks similar but it's just a comma separated list of values you're passing into the function.  The first value is the text you want to output, and any subsequent values get mysql escaped and swapped into the places where ? characters are.

You can see the sample output with print:

$var1 = "'Hello World'";
$var2 = 'A long string containing "double quotes" ';
$var3 = "123,431.00"; 
print mysql_escapef("fieldA = ? AND fieldB = ? AND fieldC = ?", $var1, $var2, $var3);
// outputs: fieldA = '\'Hello World\'' AND fieldB = 'A long string containing \"double quotes\" ' AND fieldC = '123,431.00'

// The straight PHP-only way to do it without the helper function looks like this:
$var1 = "'Hello World'";
$var2 = 'A long string containing "double quotes" ';
$var3 = "123,431.00";
print "fieldA = '".mysql_real_escape_string($var1)."' AND fieldB = '".mysql_real_escape_string($var2)."' AND fieldC = '".mysql_real_escape_string($var3)."'";
// outputs: fieldA = '\'Hello World\'' AND fieldB = 'A long string containing \"double quotes\" ' AND fieldC = '123,431.00'

Hope that helps!

Dave Edis - Senior Developer
interactivetools.com

By Codee - March 6, 2013

Thank you KINDLY for the information and beginner-teachings, oh 'enlightened one'! It is appreciated.