Confused about default values when using WHERE statement.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post by WLW »

Using echo($query); Now I get 2 hidden errors:

Notice: Undefined variable: condition_found in c:\inetpub\wwwroot\hp4-php\TMP8x95m290ur.php on line 78

Notice: Undefined variable: query in c:\inetpub\wwwroot\hp4-php\TMP8x95m290ur.php on line 85

Lines 78-85:

Code: Select all

if($condition_found) 
{ 
  $query .= $and_clause; 
} 
mysql_select_db($database_connHP4_php, $connHP4_php);
$query_rs_members = sprintf("SELECT members.memno, members.stage_last, members.stage_first, members.e_color, members.h_color, members.age_L, members.age_H, members.weight, members.height_ft, members.height_in, members.SAG, members.AFTRA, members.AEA, members.union_other, members.phone1, members.ph1_type, members.phone2, members.ph2_type, members.email1, members.website, members.thumb_loc, members.pic_loc, agents.agent_name, agents.agent_phone, managers.manager_name, managers.manager_phone, members.category FROM managers INNER JOIN (agents INNER JOIN members ON agents.agent_ID = members.agent_ID) ON managers.manager_ID = members.manager_ID WHERE (members.age_L >= '%s' AND members.age_H <= '%s') AND (members.weight BETWEEN '%s' and '%s') AND (members.height_ft BETWEEN '%s' and '%s') AND (members.height_in BETWEEN '%s' and '%s') AND (members.stage_last LIKE '%s%%') AND (members.category LIKE '%s')", $ageL_rs_members,$ageH_rs_members,$wL_rs_members,$wH_rs_members,$ftL_rs_members,$ftH_rs_members,$inL_rs_members,$inH_rs_members,$stageL_rs_members,$category1_rs_members);
$query_limit_rs_members = sprintf("%s LIMIT %d, %d", $query_rs_members, $startRow_rs_members, $maxRows_rs_members);
echo($query);
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post by WLW »

I must say, you guys are very smart. It feels like I'll never get the hang of this.
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post by WLW »

Code: Select all

$and_clause = "AND "; 
foreach($values as $choice=>$properties) 
{ 
   if(isset($_GET[$choice])) 
   { 
     $and_clause .= "($properties[column] = '$properties[search_value]') OR "; 
     $condition_found = true; 
   } 
} 
echo($properties);
produces the following hidden on the page:

Array
Notice: Undefined variable: condition_found in c:\inetpub\wwwroot\hp4-php\hp5-results-with-unions-added.php on line 78

Notice: Undefined variable: query in c:\inetpub\wwwroot\hp4-php\hp5-results-with-unions-added.php on line 85


and this code:

Code: Select all

$and_clause = "AND "; 
foreach($values as $choice=>$properties) 
{ 
   if(isset($_GET[$choice])) 
   { 
     $and_clause .= "($properties[column] = '$properties[search_value]') OR "; 
     $condition_found = true; 
   } 
} 
echo($choice);
produces this:

choice6
Notice: Undefined variable: condition_found in c:\inetpub\wwwroot\hp4-php\TMP9swqp291zs.php on line 78

Notice: Undefined variable: query in c:\inetpub\wwwroot\hp4-php\TMP9swqp291zs.php on line 85


and this:

Code: Select all

$and_clause = "AND "; 
foreach($values as $choice=>$properties) 
{ 
   if(isset($_GET[$choice])) 
   { 
     $and_clause .= "($properties[column] = '$properties[search_value]') OR "; 
     $condition_found = true; 
   } 
} 
echo($condition_found);
produces this:

Notice: Undefined variable: condition_found in c:\inetpub\wwwroot\hp4-php\TMP9viaw29236.php on line 73

Notice: Undefined variable: condition_found in c:\inetpub\wwwroot\hp4-php\TMP9viaw29236.php on line 78

Notice: Undefined variable: query in c:\inetpub\wwwroot\hp4-php\TMP9viaw29236.php on line 85


Any ideas??
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post by WLW »

If the form submits only "choice3" which is named "aftra1" and sends the search value "AFTRA", what would you expect this code to echo:

Code: Select all

$and_clause = "AND "; 
foreach($values as $choice=>$properties) 
{ 
   if(isset($_GET[$choice])) 
   { 
     $and_clause .= "($properties[column] = '$properties[search_value]') OR "; 
     $condition_found = true; 
   } 
} 
echo($properties);
Here is what the echo shows:

Array
Notice: Undefined variable: condition_found in c:\inetpub\wwwroot\hp4-php\hp5-results-with-unions-added.php on line 78


What should be shown to represent $properties instead of the word "Array"?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

It seems to me that you've got your error reporting level set really high. PHP has the ability to not output certain errors - like your notices. I'm a little unsure how to change that level - maybe check the manual or wait until ~feyd reads this again :). In the meantime, just be sure to define $condition_found and $query and those errors should go away.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post by WLW »

I'll try to look that up.

In the meanwhile, here's something interesting. This echo:

Code: Select all

$and_clause = "AND "; 
foreach($values as $choice=>$properties) 
{ 
   if(isset($_GET[$choice])) 
   { 
     $and_clause .= "($properties[column] = '$properties[search_value]') OR "; 
     $condition_found = true; 
   } 
} 
echo($properties[column]);
Shows this:

Notice: Use of undefined constant column - assumed 'column' in c:\inetpub\wwwroot\hp4-php\hp5-results-with-unions-added.php on line 73
members.AEA
Notice: Undefined variable: condition_found in c:\inetpub\wwwroot\hp4-php\hp5-results-with-unions-added.php on line 78
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Actually, it's not the echo that's doing that. echo is similar to printf in that it outputs whatever you put between the (), to the browser. So, if I were to echo("test"), I would see "test" somewhere in my browser. Look it up in the manual too: [php_man]echo[/php_man]

Again, these errors just mean $condition_found isn't defined, and also that the "column" index of $properties isn't found. These aren't errors per se, just notices. You should really look at cutting down the error reporting level
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Notice: Use of undefined constant column - assumed 'column' is because you've used $properties[column] instead of $properties['column']. If you don't quote your array elements then it treats them as defined vars ( i.e it's looking for define('column', 'somevalue'); )
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post by WLW »

Can someone please show me what these should be defined as? I am confused. I believe constants are define(); and variables are $something = something;

But, I have no idea what the actual values need to be. For example, define('column', something). I have no clue what the "something" is for this code I've been working with...
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post by WLW »

I think I figured out most of it.

My new code is:

Code: Select all

mysql_select_db($database_connHP4_php, $connHP4_php);
$query_rs_members = sprintf("SELECT members.memno, members.stage_last, members.stage_first, members.e_color, members.h_color, members.age_L, members.age_H, members.weight, members.height_ft, members.height_in, members.SAG, members.AFTRA, members.AEA, members.union_other, members.phone1, members.ph1_type, members.phone2, members.ph2_type, members.email1, members.website, members.thumb_loc, members.pic_loc, agents.agent_name, agents.agent_phone, managers.manager_name, managers.manager_phone, members.category FROM managers INNER JOIN (agents INNER JOIN members ON agents.agent_ID = members.agent_ID) ON managers.manager_ID = members.manager_ID WHERE (members.age_L >= '%s' AND members.age_H <= '%s') AND (members.weight BETWEEN '%s' and '%s') AND (members.height_ft BETWEEN '%s' and '%s') AND (members.height_in BETWEEN '%s' and '%s') AND (members.stage_last LIKE '%s%%') AND (members.category LIKE '%s')", $ageL_rs_members,$ageH_rs_members,$wL_rs_members,$wH_rs_members,$ftL_rs_members,$ftH_rs_members,$inL_rs_members,$inH_rs_members,$stageL_rs_members,$category1_rs_members);
$query_limit_rs_members = sprintf("%s LIMIT %d, %d", $query_rs_members, $startRow_rs_members, $maxRows_rs_members);
$rs_members = mysql_query($query_limit_rs_members, $connHP4_php) or die(mysql_error());
$row_rs_members = mysql_fetch_assoc($rs_members);
//assume checkboxes are choice1....choice6 
//put in an array that maps checkboxes to values to search for 

$condition_found = true;

$values["sag1"] = array("column"=>"members.SAG", 
                                        "search_value"=>"SAG");
$values["sagEL"] = array("column"=>"members.SAG", 
                                        "search_value"=>"SAG Eligible"); 
$values["aftra1"] = array("column"=>"members.AFTRA", 
                                        "search_value"=>"AFTRA"); 
$values["aftraEL"] = array("column"=>"members.AFTRA", 
                                        "search_value"=>"AFTRA Eligible"); 
$values["aea1"] = array("column"=>"members.AEA", 
                                        "search_value"=>"AEA"); 
$values["aeaEL"] = array("column"=>"members.AEA", 
                                        "search_value"=>"AEA Eligible"); 
$and_clause = " AND "; 
foreach($values as $choice=>$properties) 
{ 
   if(isset($_GET[$choice])) 
   { 
     $and_clause .= "($properties[column] = '$properties[search_value]') OR "; 
	 $condition_found = true; 
   } 
} 
$and_clause = rtrim($and_clause," OR "); 
//only add "and" clause if there is something to sort on, 
//otherwise, no "and" clause = return everything. 
if($condition_found) 
{ 
  $and_clause = rtrim($and_clause," AND "); 
  $query_rs_members .= $and_clause;
} 
echo $query_rs_members;
The echo seems to show that the SQL is correct no matter what combo of checkboxes I choose. For example, after choosing the checkboxes 'sag1' and 'aeaEL', the echo shows the following SQL statement:

Code: Select all

SELECT members.memno, members.stage_last, members.stage_first, members.e_color, members.h_color, members.age_L, members.age_H, members.weight, members.height_ft, members.height_in, members.SAG, members.AFTRA, members.AEA, members.union_other, members.phone1, members.ph1_type, members.phone2, members.ph2_type, members.email1, members.website, members.thumb_loc, members.pic_loc, agents.agent_name, agents.agent_phone, managers.manager_name, managers.manager_phone, members.category FROM managers INNER JOIN (agents INNER JOIN members ON agents.agent_ID = members.agent_ID) ON managers.manager_ID = members.manager_ID WHERE (members.age_L &gt;= '0' AND members.age_H &lt;= '100') AND (members.weight BETWEEN '0' and '500') AND (members.height_ft BETWEEN '0' and '7') AND (members.height_in BETWEEN '0' and '12') AND (members.stage_last LIKE '%') AND (members.category LIKE '%') AND (members.SAG = 'SAG') OR (members.AEA = 'AEA Eligible')
But, it does NOT seem to be filtering the actual records in the database. I tested the above SQL on a copy of the page that didn't have any of the new code, and sure enough, it filtered the records as expected. Any suggestions??
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Paste the query right into the command line and see what it does. Also, you might want to put another set of parentesis around all the OR statements: ie:

Code: Select all

.... AND ( (members.SAG = 'SAG') or (members.AEA = 'AEA Eligible'));
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post by WLW »

pickle wrote:Paste the query right into the command line and see what it does.
What do you mean by "command line"?
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

just try and paste it into phpmyadmin or something, see if you get teh correct resultset, you do NOT need to use the command-line
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post by WLW »

By the way, the other search form parameters filter the database properly. Does the following, which appears right after the main SQL statement, have anything to do with the search not filtering on the new parameters?

Code: Select all

, $ageL_rs_members,$ageH_rs_members,$wL_rs_members,$wH_rs_members,$ftL_rs_members,$ftH_rs_members,$inL_rs_members,$inH_rs_members,$stageL_rs_members,$category1_rs_members,$spanish_rs_members);
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post by WLW »

I added the () around the "OR" statements like this:

Code: Select all

$and_clause = " AND ("; 
foreach($values as $choice=>$properties) 
{ 
   if(isset($_GET[$choice])) 
   { 
     $and_clause .= "($properties[column] = '$properties[search_value]') OR "; 
	 $condition_found = true; 
   } 
} 
$and_clause = rtrim($and_clause," OR ");
$and_clause .= ")" ;
It works when form selections are made. But, when no checkboxes are selected, the SQL ends with "AND ()" and generates an error. By the way, I'm still don't know why the database is not reacting to the full SQL.
Post Reply