Hmm.... difficult

[Skatecrazy1]

Well I haven't put much effort into finding a way around this, but

here's my code, if you can understand php well enough you'll see it's a form that filters out mySQL results and shows the pertinent ones

[code]
<?php  $self = $_SERVER['PHP_SELF'];


function echoSpacer()
{
  $space = " | ";
  echo $space;
}

$conn = @mysql_connect("localhost", "root", "") or die(mysql_error());
$type_rs = @mysql_select_db("type");


/*---------------------------------------------------*/
/*---------------PROJECT TYPE SELECT BOX-------------*/
$type_sql = "SELECT * FROM `selection_type` order by `rec_id`";
$type_rs = @mysql_query($type_sql, $conn) or die(mysql_error());
//start building selector
echo("<form method=\"url\" action=\"$self\">");
$type .= "<select name=\"type\">";
$type .= "<option selected value=\"\">-=Work Type=-</option>";
while($row = mysql_fetch_array($type_rs))
{
  $type .= "<option value=\"".$row['rec_id']."\">".$row['name']."</option>";
}

$type .= "</select>";
echo $type;

echoSpacer();
/*-------------END PTYPE SELECT---------------------*/
/*--------------------------------------------------*/
/*--------------PRIORITY SELECT BOX-----------------*/
$priority_rs = @mysql_select_db("priority") or die(mysql_error());
$priority_sql = "SELECT * FROM `priority_value`";
$priority_rs = @mysql_query($priority_sql, $conn) or die(mysql_error());
$priority = "<select name=\"priority\">";
$priority .= "<option selected value=\"\">-=Priority Level=-</option>";
while($pr_row = mysql_fetch_array($priority_rs))
{
  $priority .= "<option value=\"".$pr_row['rec_id']."\">".$pr_row['name']."</option>";
}
$priority .= "</select>";
echo $priority;
echoSpacer();
/*-------------END PRIORITY SELECT------------------*/
/*--------------------------------------------------*/
/*-----------GROUP SELECT BOX-----------------------*/
$group_rs = @mysql_select_db("group") or die(mysql_error());
$group_sql = "SELECT * FROM `group_name`";
$group_rs = @mysql_query($group_sql, $conn) or die(mysql_error());
$group = "<select name=\"group\">";
$group .= "<option selected value=\"\">-=Group=-</option>";
while($group_row = mysql_fetch_array($group_rs))
{
  $group .= "<option value=\"".$group_row['rec_id']."\">".$group_row['name']."</option>";

}

$group .= "</select>";
echo $group;
echoSpacer();
/*-------------END GROUP SELECT--------------------*/
echo("<input type=\"hidden\" value=\"track\" name=\"hidden\" />");
echo("<input type=\"submit\" value=\"Find\">");

echo("</form>");
/*-------------------------------------------------*/
/*--------------START DISPLAY RESULTS--------------*/
$group = $_GET['group'];
$priority = $_GET['priority'];
$type = $_GET['type'];
$rs = @mysql_select_db("project") or die(mysql_error());
if(isset($_GET['hidden']))
{
  $sql = "SELECT * FROM `project_general` WHERE `group`=\"$group\" OR `priority`=\"$priority\"
    OR `type` =\"$type\" ORDER BY `rec_num` ASC";

} else {

  $sql = "SELECT * FROM `project_general` ORDER BY `rec_num` ASC";
}

$result = "<table cellpadding=\"4\" border=\"1\">";
$result .= "<tr><th>Manager</th><th>Name</th><th>Start Date</th><th>End Date</th><th>Finish Date</th>
<th>Amount</th><th>Notes</th></tr>";
$rs = @mysql_query($sql, $conn) or die(mysql_error());

while($row = mysql_fetch_array($rs))
{
  $result .= "<tr><td>".$row['manager']."</td><td>".$row['name']."</td>";
  $result .= "<td>".$row['start_date']."</td>";
  $result .= "<td>".$row['end_date']."</td>";
  $result .= "<td>".$row['finish_date']."</td>";
  $result .= "<td>".$row['amount']."</td>";
  $result .= "<td>".$row['notes']."</td>";
  $result .= "</tr>";
}

$result .= "</table>";
echo $result;


?>
[/code]

my only problem is that when a user only selects one category, all of my values are set to null, and this doesn't match the where clause in the query, so i get no results, even though the condition the user selected matched one of the results.

please help.

[btherl]

If I understand your problem correctly, you can dynamically generate the SQL statement, something like this:

[code]$sql = "SELECT * FROM `project_general` ";
$added_where = false;
if ($group) {
  $sql .= " WHERE `group`=\"$group\" ";
  $added_where = true;
}
if ($priority) {
  if ($added_where) {
    $sql .= " OR ";
  } else {
    $sql .= " WHERE ";
    $added_where = true;
  }
  $sql .= "`priority`=\"$priority\" ";
}
if ($type) {
  if ($added_where) {
    $sql .= " OR ";
  } else {
    $sql .= " WHERE ";
    $added_where = true;
  }
  $sql .= "`type`=\"$type\" ";
}
$sql .= " ORDER BY `rec_num` ASC";[/code]

Yes, it's not elegant.  SQL is messy to generate.  If I was writing that I would put the SQL generation code into a separate function to clarify things.

[Skatecrazy1]

Can you have more than one WHERE clause?

[roopurt18]

No.

Every time he appends a "WHERE" to the query he sets a variable to true.  Next time he's about to add another "WHERE" he checks if it's already been added and if it has, uses "OR" instead.

The method I typically use for dynamic WHEREs is:
[code]<?php
$add_wheres = Array(); // Set to empty
if($condition1){
  $add_wheres[] = "table1.fld1=table2.fld2";
}
if($condition2){
  $add_wheres[] = "$table2.fld3=table3.fld4";
}
$sql = "SELECT ..."; // There is no WHERE in here yet
if(count($add_wheres)){
  // We have a dynamic WHERE
  $sql .= " WHERE " . implode(" AND ", $add_wheres );
}else{
  // No dynamic WHERE, possibly set a default WHERE or do nothing
  $sql .= " WHERE something=something"; // Used a default
}
?>[/code]

[roopurt18]

Applying my approach to your direct problem:

[code]
<?php
/*-------------------------------------------------*/
/*--------------START DISPLAY RESULTS--------------*/
$group = isset($_GET['group']) ? "'" . addslashes($_GET['group']) . "'"
                               : NULL; // Clean your data before you
                                       // give it to a database -- ALWAYS!!!
$priority = isset($_GET['priority']) ? "'" . addslashes($_GET['priority']) . "'"
                                     : NULL;
$type = isset($_GET['type']) ? "'" . addslashes($_GET['type']) . "'"
                             : NULL;
// Now each of $group, $priority, and $type are an escaped string surrounded
// with single quotes or the value NULL
// We can now build our dynamic WHERE clause
$add_wheres = Array();
if($group){
  $add_wheres[] = "`group`={$group}";
}
if($priority){
  $add_wheres[] = "`priority`={$priority}";
}
if($type){
  $add_wheres[] = "`type`={$type}";
}
$rs = @mysql_select_db("project") or die(mysql_error());
$sql = "SELECT * FROM `project_general`";
if(count($add_wheres)){
  $sql .= " WHERE " . implode(" OR ", $add_wheres);
}
$sql .= " ORDER BY `rec_num` ASC";
/* Previously you had the following lines, but it would seem $_GET['hidden'] is
   no longer needed with my method
if(isset($_GET['hidden']))
{
$sql = "SELECT * FROM `project_general` WHERE `group`=\"$group\" OR `priority`=\"$priority\"
  OR `type` =\"$type\" ORDER BY `rec_num` ASC";

} else {

$sql = "SELECT * FROM `project_general` ORDER BY `rec_num` ASC";
}
*/
?>
[/code]

(EDIT) Note that I've cleaned the data you're giving to the database; I've assumed it's text data, you would use a slightly different process for numeric data.

In addition, as recommended by btherl, you should break that page down into smaller functions, it makes it easier to really follow what's going on.

[stewart715]

WHERE $a = a AND $b = b

[Skatecrazy1]

Okay now I get the problem of, when a user doesn't select anything, the GETs get sent as emtpy variables, but they're still set variables, so the SQL basically still doesn't work because of this.  any way to not set the $_GET variables if the select input is emtpy?

[roopurt18]

Look up isempty or is_empty in the PHP manual.  I forget the exact name.

[Skatecrazy1]

okay, well the bool is just empty($var)

the problem now is:

when you specify the method="url" in the form, it automatically passes the variables into the URL.

i want to stop any emtpy variables from being passed.

that's what I was getting at.

[roopurt18]

Use method="post" and the $_POST array instead of $_GET, although you might get the same thing.

For my own pages, I use method="post" for all form submissions and reserve using $_GET only for when I want to create dynamic links that pass very little data, maybe only a product or user ID but nothing more.

[Skatecrazy1]

nope, same result;

all i'm passing is numbers (1, 2, 3, 4, & 5) so i was just using get