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

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 »

You need to change that "=" to "=>", as that's proper array notation.

ie:

Code: Select all

array("key_1"=>"value1",
         "key_2"=>"value2");
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 »

Actually, I did figure that out, but now I get another error:

Parse error: parse error, unexpected '{' in c:\inetpub\wwwroot\hp4-php\hp5-results-with-unions-added.php on line 68

Here are lines 68-71

Code: Select all

{ 
     $and_clause .= "($propertiesїcolumn] = '$propertiesїsearch_value]') OR "; 
     $condition_found = true; 
   }
Last edited by WLW on Tue Aug 10, 2004 2:27 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it's likely the previous line..
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post by WLW »

Here are lines 66-72

Code: Select all

{ 
   if(isset($_GETї$choice]) 
   { 
     $and_clause .= "($propertiesїcolumn] = '$propertiesїsearch_value]') OR "; 
     $condition_found = true; 
   } 
}
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 »

missing a closing ) on line 67. I told you the code was ugly :)
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 »

Thanks again pickle!

Now the page displays, but with the wrong data. There are 4 test records.

I would expect that all four records appear when the form is not submitted, or is submitted with no options checked. Instead, in both cases only 2 of the 4 records show. And no matter what selections I make on the form, the same 2 records always show.

How can I view what's going on behind the scenes?
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 »

Your best bet would be to echo the query before you send it, then you can see exactly what the query is.
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'm not quite sure what you mean...

I placed your code onto the page like this:

Code: Select all

}
$ageL_rs_members = "0";
if (isset($_GETї'ageL'])) {
  $ageL_rs_members = (get_magic_quotes_gpc()) ? $_GETї'ageL'] : addslashes($_GETї'ageL']);
}
//assume checkboxes are choice1....choice6 
//put in an array that maps checkboxes to values to search for 
$valuesї"choice1"] = array("column"=>"members.SAG", 
                                        "search_value"=>"sag1"); 
$valuesї"choice2"] = array("column"=>"members.SAG", 
                                        "search_value"=>"sagEL"); 
$valuesї"choice3"] = array("column"=>"members.AFTRA", 
                                        "search_value"=>"aftra1"); 
$valuesї"choice4"] = array("column"=>"members.AFTRA", 
                                        "search_value"=>"aftraEL"); 
$valuesї"choice5"] = array("column"=>"members.AEA", 
                                        "search_value"=>"aea1"); 
$valuesї"choice6"] = array("column"=>"members.AEA", 
                                        "search_value"=>"aeaEL"); 
$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) 
{ 
  $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);
$rs_members = mysql_query($query_limit_rs_members, $connHP4_php) or die(mysql_error());
$row_rs_members = mysql_fetch_assoc($rs_members);

if (isset($_GET&#1111;'totalRows_rs_members'])) &#123;
  $totalRows_rs_members = $_GET&#1111;'totalRows_rs_members'];
&#125; else &#123;
  $all_rs_members = mysql_query($query_rs_members);
  $totalRows_rs_members = mysql_num_rows($all_rs_members);
&#125;
$totalPages_rs_members = ceil($totalRows_rs_members/$maxRows_rs_members)-1;
?>
At what point do I put the "echo" and what would the term look like?

Sorry that I included so much code here...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

your query looks odd... with this:

Code: Select all

INNER JOIN (agents INNER JOIN members ON agents.agent_ID = members.agent_ID)
are you selecting all, or most of the member table's columns, it sure looks like it.. you could change your whole select chunk to "memebers.*" and probably be fine..
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post by WLW »

That is meant to bring in data from another table. The SQL was generated by MS Access and then brought into Dreamweaver MX.
Last edited by WLW on Tue Aug 10, 2004 3:40 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

btw, you'd want to echo just after the sprintf, generally.. or whenever you finish adding stuff to the query string.. which ever comes last.
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 »

Ya, right before you call $rs_members = mysql_query($query_limit_rs_members, $connHP4_php) or die(mysql_error());. However, I notice this isn't the query that the AND clause is being added to, in fact, I'm not sure that particular query is being run.
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 added echo:

Code: Select all

$inH_rs_members,$stageL_rs_members,$category1_rs_members);
echo;
...and got the following error hidden on the displayed page.

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

Lines 78-81 are:

Code: Select all

if($condition_found) 
{ 
  $query .= $and_clause; 
}
By the way, all four records do appear. I haven't tried to submit the form with a choice made yet.
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 »

echo is a function. You need to echo something. Try echo($query);.
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 will in a moment.

By the way, when I run the page:
http://localhost/hp4-php/hp5-results-wi ... -added.php

I only get 2 of the 4 records to display.

But if I run the page using the Dreamweaver "preview in browser" I get all 4 records...
Post Reply