[RESOLVED] How to search db from URL Variables, Can't get this!

Hello...

I am trying to create a sql search on a master catalog page. I want to narrow down items based on selected information in a few dropdown boxes.

I have three: Make, Model, Color ... upon clicking 'submit' the results page is shown with the search results.

They are all dynamic menus. Their values correspond to their primary IDs from their mysql table.
I also added a label called "All" with no value, so the user can leave any of the menuboxes as "All" when searching.

I then pass the information to the search results page using the get method on the form (using URL Variables).

What throws me off is the "All" that I would like to have in the boxes.

My search results page is made to get the numeric information from the URL variables passed from the dropdown search boxes.

My search works great if all fields are entered as of right now.

However, as I mentioned, I have the value set to nothing for the "All" option in the dropdown box, so the URL variable is blank.

How can I modify the sql to incorporate that is there is no value for the url variable, then do not use it in the where clause of the sql?

Code:

---

$make_rs = "-1";
if (isset($_GET['make'])) {
  $man_rs = $_GET['make'];
}
$model_rs = "-1";
if (isset($_GET['model'])) {
  $level_rs = $_GET['model'];
}
$color_rs = "-1";
if (isset($_GET['color'])) {
  $model_rs = $_GET['color'];
}
mysql_select_db($database_connectDB, $connectDB);
$query_rs = sprintf("SELECT * FROM prod_items WHERE make_id = %s AND model_id=%s AND color_id = %s", GetSQLValueString($make_rs, "int"),GetSQLValueString($model_rs, "int"),GetSQLValueString($color_rs, "int"));

---

... that is my search now.

I am using php/mySQL. :-)

This is kind of like the search feature at www.cars.com. One can select "All" for makes, "all" for model, and so on.

Thank you!!!!

in SQL '1' is a match for everything.

for example:
SELECT * FROM `mytable` WHERE 1

that will select all of the rows in my table.

So in your case:
SELECT * FROM `mytable` WHERE make_id=1 AND model_id=1...

would select all matching them.

since the first reply to this thread was made by someone who obviously didn't read the original post, I'll get back on topic:

if you want to be able to select "all" makes and models (or whatever you're doing), then all you need to do is omit that category in the WHERE clause of your SQL. having 3 different ANDs in your query makes it very specific and very precise -- to get less precise, you just leave some of them out. something like this would do the trick (given example of make/model/year):

PHP Code:

---

<?php
  $sql = "SELECT * FROM cars WHERE "; //base of sql query
  $sqladded = false; //to see if we need an "AND"
  if($_GET['make'] != "all"){
    //if the make is not set to all, we add to the query
    $sql .= "make='{$_GET['make']}'";
    $sqladded = true; //if model is not all, we need an AND to make a proper query
  }
  if($_GET['model'] != "all"){
    //if the model is not set to all, we add to the query as well.
    //however, if $sqladded is true, we need to add the "AND"
    if($sqladded){
      $sql .= " AND ";
    }
    $sql .= "model='{$_GET['model']}'";
    $sqladded = true; //make sure this is set to true still
  }
  if($_GET['year'] != "all"){
    //same as above
    //still need to check $sqladded!
    if($sqladded){
      $sql .= " AND ";
    }
    $sql .= "year='{$_GET['year']}'";
    $sqladded = true;
  }
  //now, our problem is.. if every value is set to all, we will have a query syntax error
  if(!$sqladded){
    //if no sql was added, void the where clause by making it select everything
    $sql .= "1";
  }
  $sql .= " LIMIT 30"; //add the limit if applicable
  echo $sql;
?>

---

hope that makes sense, tried to make the comments at least somewhat helpful.

If all is selected I have the URL variable sent as "all" now to match up with your code.

I still cannot get this working, although I am trying very hard.

As of now, kows, your code here just echos out the sql, nothing else appears onscreen.

Here is what I have done (keep in mind that my final three search parameters are make, level, and model; I am also interjoining tables):

Code:

---

mysql_select_db($database_connectDB, $connectDB);
$query_rs = "SELECT * FROM prod_items  INNER JOIN prod_make ON prod_items.make_ID= prod_make.make_ID INNER JOIN prod_model ON prod_items.model_ID= prod_model.model_ID  INNER JOIN prod_man ON prod_items.man_ID= prod_man.man_ID INNER JOIN prod_level ON prod_make.level_ID= prod_level.level_ID WHERE ";
$sqladded = false; //to see if we need an "AND"
  if($_GET['make'] != "all"){
    //if the make is not set to all, we add to the query
    $query_rs .= "prod_items.make_id='{$_GET['make']}'";
    $sqladded = true; //if model is not all, we need an AND to make a proper query
  }
  if($_GET['level'] != "all"){
    //if the model is not set to all, we add to the query as well.
    //however, if $sqladded is true, we need to add the "AND"
    if($sqladded){
      $query_rs .= " AND ";
    }
    $query_rs .= "prod_items.level_id='{$_GET['level']}'";
    $sqladded = true; //make sure this is set to true still
  }
  if($_GET['model'] != "all"){
    //same as above
    //still need to check $sqladded!
    if($sqladded){
      $query_rs .= " AND ";
    }
    $query_rs .= "prod_items.model_id='{$_GET['model']}'";
    $sqladded = true;
  }
  //now, our problem is.. if every value is set to all, we will have a query syntax error
  if(!$sqladded){
    //if no sql was added, void the where clause by making it select everything
    $query_rs .= "1";
  }
  $query_rs .= " LIMIT 30"; //add the limit if applicable
  echo $query_rs;
 
$query_limit_rs = sprintf("%s LIMIT %d, %d", $query_rs, $startRow_rs, $maxRows_rs);
$rs = mysql_query($query_limit_rs, $connectDB) or die(mysql_error());

---

Wherever you had $sql, I changed it to $query_rs to keep in line with my code. I think that is ok?

Thanks for the help.

The results page is all white with this text on it only:

"SELECT * FROM prod_items INNER JOIN prod_make ON prod_items.make_ID= prod_make.make_ID INNER JOIN prod_model ON prod_items.model_ID= prod_model.model_ID INNER JOIN prod_man ON prod_items.man_ID= prod_man.man_ID INNER JOIN prod_level ON prod_make.level_ID= prod_level.level_ID WHERE prod_items.make_id='' AND prod_items.level_id='' AND prod_items.model_id='' LIMIT 30You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 10' at line 1"

if i get rid of echo $query_rs;

then i get an error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 10' at line 1"

the errror was there before the echo was removed (shown by what you posted in the last post).

this is your problem: your SQL query is showing two limits: LIMIT 30 LIMIT 0, 0

you can just get rid of the line that adds the LIMIT from my code:

PHP Code:

---

<?php
$query_rs = "SELECT * FROM prod_items  INNER JOIN prod_make ON prod_items.make_ID= prod_make.make_ID INNER JOIN prod_model ON prod_items.model_ID= prod_model.model_ID  INNER JOIN prod_man ON prod_items.man_ID= prod_man.man_ID INNER JOIN prod_level ON prod_make.level_ID= prod_level.level_ID WHERE ";
$sqladded = false; //to see if we need an "AND"
  if($_GET['make'] != "all"){
    //if the make is not set to all, we add to the query
    $query_rs .= "prod_items.make_id='{$_GET['make']}'";
    $sqladded = true; //if model is not all, we need an AND to make a proper query
  }
  if($_GET['level'] != "all"){
    //if the model is not set to all, we add to the query as well.
    //however, if $sqladded is true, we need to add the "AND"
    if($sqladded){
      $query_rs .= " AND ";
    }
    $query_rs .= "prod_items.level_id='{$_GET['level']}'";
    $sqladded = true; //make sure this is set to true still
  }
  if($_GET['model'] != "all"){
    //same as above
    //still need to check $sqladded!
    if($sqladded){
      $query_rs .= " AND ";
    }
    $query_rs .= "prod_items.model_id='{$_GET['model']}'";
    $sqladded = true;
  }
  //now, our problem is.. if every value is set to all, we will have a query syntax error
  if(!$sqladded){
    //if no sql was added, void the where clause by making it select everything
    $query_rs .= "1";
  }

  $query_limit_rs = sprintf("%s LIMIT %d, %d", $query_rs, $startRow_rs, $maxRows_rs);
  echo $query_limit_rs;
?>

---

this should do what you want then.

That works great for me, I really want to thank you for helping me.

Because of your help (which I really understand), I am starting to expand my query to include a price range too.

Thanks again.

I was just recommended strongly that I come up with a way to validate the variables, just in case, someone doesn't inject an attack on my db.

I am working on this for the to of the page:

Code:

---

$number = Array(
        $_GET['man'],
        $_GET['level'],
        $_GET['make'],
        $_GET['min'],
                $_GET['max'],
        );

foreach($number as $element)
{
    if(is_numeric($element))
    {
        echo "'{$element}' is numeric", PHP_EOL;
    }
    else
    {
        echo "'{$element}' is NOT numeric", PHP_EOL;
    }
}

---

I am making sure that all of the url variables passed are numeric at least. How can I make this validation work?

of course. you should always validate your variables. never trust your users. I left all of that out in my examples simply because it's faster to write. first, I would make sure that you are being passed the correct values (like you've tried to do above). second, I would also use the mysql_real_escape_string() function before passing any strings to a query in your database. this will add slashes where needed. you could do something like this, that will loop through your entire GET array and validate whichever values you like using a switch.

PHP Code:

---

<?php
  $error = false;
  $querysafe = array(); //array to hold "validated" values
  foreach($_GET as $key => $value){
    switch($key){
      case "level":
      case "min":
      case "max":
        //these are supposed to be numeric
        if(!is_numeric($value)){
          $error = true;
        }else{
          $querysafe[$key] = $value;
        }
        break;
      case "make":
      case "man":
        //these are supposed to be strings, no real way to validate them
        //so we'll just make them query safe.
        $querysafe[$key] = mysql_real_escape_string($value);
        break;
    }
  }
  //handle errors?
  if($error){
    echo "something's wrong.";
  }else{
    //do your stuff.
  }
?>

---

when querying the database, you could now use the $querysafe array instead of $_GET, as long as no errors occurred. of course, if none of your GET values will ever be a string, you could get rid of the switch and just use a foreach loop to check if $value was numeric or not.

thanks... i am going to try this.

can i do something like this:

Code:

---

mysql_select_db($database_connectDB, $connectDB);
$query_rs = "SELECT * FROM prod_items  INNER JOIN prod_make ON prod_items.make_ID= prod_make.make_ID INNER JOIN prod_model ON prod_items.model_ID= prod_model.model_ID  INNER JOIN prod_man ON prod_items.man_ID= prod_man.man_ID INNER JOIN prod_level ON prod_make.level_ID= prod_level.level_ID WHERE ";
$sqladded = false; //to see if we need an "AND"
        if($_GET['man'] != "0"){
                //if the make is not set to all, we add to the query
                if (is_numeric($_GET['man']))
                {
                $query_rs .= "prod_items.man_id='{$_GET['man']}'";
                $sqladded = true; //if model is not all, we need an AND to make a proper query
                }
                }
///the rest here in the same format

---

does that work too?!!

here's what I also have done:

Code:

---

mysql_select_db($database_connectDB, $connectDB);
$query_rs = "SELECT * FROM prod_items  INNER JOIN prod_make ON prod_items.make_ID= prod_make.make_ID INNER JOIN prod_model ON prod_items.model_ID= prod_model.model_ID  INNER JOIN prod_man ON prod_items.man_ID= prod_man.man_ID INNER JOIN prod_level ON prod_make.level_ID= prod_level.level_ID WHERE ";
$sqladded = false; //to see if we need an "AND"
        if($_GET['man'] != "0"){
                $_GET['man'] = mysql_real_escape_string($_GET['man']);
                //if the make is not set to all, we add to the query               
                $query_rs .= "prod_items.man_id='{$_GET['man']}'";
                $sqladded = true; //if model is not all, we need an AND to make a proper query
                }
  if($_GET['level'] != "0"){
    //if the model is not set to all, we add to the query as well.
    //however, if $sqladded is true, we need to add the "AND"
    if($sqladded){
      $query_rs .= " AND ";
    }
        $_GET['level'] = mysql_real_escape_string($_GET['level']);
    $query_rs .= "prod_level.level_id='{$_GET['level']}'";
    $sqladded = true; //make sure this is set to true still
  }
  if($_GET['model'] != "0"){
    //same as above
    //still need to check $sqladded!
    if($sqladded){
      $query_rs .= " AND ";
    }
    $_GET['model'] = mysql_real_escape_string($_GET['model']);
        $query_rs .= "prod_items.model_id='{$_GET['model']}'";
    $sqladded = true;
  }
    if($_GET['min'] != ""){
    //same as above
    //still need to check $sqladded!
    if($sqladded){
      $query_rs .= " AND ";
    }
    $_GET['min'] = mysql_real_escape_string($_GET['min']);
        $query_rs .= "prod_items.list_price>='{$_GET['min']}'";
    $sqladded = true;
  }
    if($_GET['max'] != ""){
    //same as above
    //still need to check $sqladded!
    if($sqladded){
      $query_rs .= " AND ";
    }
    $_GET['max'] = mysql_real_escape_string($_GET['max']);
        $query_rs .= "prod_items.list_price<='{$_GET['max']}'";
    $sqladded = true;
  }

---

I used mysql_real_escape_string() just before the URL variable is inserted into the sql.

well, that would do it, yes, but I'd probably check if the variables were numeric first before even bothering with anything else. you could print an error without having your script go through the process of building the query/etc. in the code you posted, you'll just only add onto the query when that value is valid, which would mean you'd always end up having a result of the "base" query.. which could be what you want, anyway. so, yeah. it just depends on what you want, I suppose.

thanks again kows.

I am so new to php, that my way is the easiest way for me right now.

Once I get better, I will return to your method and incorporate it. I wasn't sure if I place that at the top of the script or just before the if statements you had helped me script.

As long as my way is query safe, then I'll be ok for now.

Thanks again.

you could enter it at the top or just before the query part, it wouldn't matter. good luck.

thank you kows, again for your help.