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 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.