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

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!

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

Hi Dave, Something did not work as I copy/pasted tat helper code and got error

Notice: Use of undefined constant where - assumed 'where' in /home/content/33/10223033/html/fsyflvideo/games2.php on line 16 MySQL Error: Unknown column 'organization' in 'where clause'

Here's how I copy pasted it into the head portion of the list page:

  // 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['team']),
    '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')
));

Should there be some apostrophes around 'where'?

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!

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?

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!

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!

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