mysql WHERE statement

I am having a problem with a mysql where statement, using a PHP page I have 3 drop down boxes, each with a SELECT DISTINCT statement in them, all these work ok in selecting the distinct value.

I have set in each of these an initially selected label of "All", with a value of %.

On my results page I have the following mysql statement :-

SELECT *FROM catchesWHERE name = 'colname' AND pegno = 'colpeg' AND specie = 'colspecie'ORDER BY name ASC 

Code (markup):

'colname' , 'colpeg' , 'colspecie' are my 3 variables from the previous page, and I am using the $_POST[colname], $_POST[colpeg] and $_POST[colspecie] for my variables.

If I select for each of the 3 search variables a value which matches in the database to ALL three items, it selects Ok, but I need the "All" option to work on the 3 drop down boxes.

Example, select "Andy" in box 1, but leave the other to as "All", the give all records with "Andy" in them.

I thought the % value would solve this, but I can't seem to get it to work.

Can anyone help me please.

 

try this code

$colname=$_POST['colname'];
$colpeg=$_POST['colpeg'];
$colspecie=$_POST['colspecie'];
$sql=" SELECT * FROM catches WHERE 1=1 ";
if (strlen($colname)>1) $sql.="AND `name`='".$colname."'"; 
if (strlen($colpeg)>1) $sql.="AND `peg_no`='".$colpeg."'"; 
if (strlen($colspecie)>1) $sql.="AND `specie`='".$colspecie."'";
$sql.=" ORDER BY `name` ASC";

Code (markup):

And you have to set for ALL label in options no value
just like : <option value=''>ALL</option> (in html)
and in php file, you have the proper query sentence in $sql.

Enjoy

 

That code should not be used because it is not safe.

NEVER insert anything into your database that comes from the end user without first escaping the data thats received. Anything can be injected into the $_GET/$_POST arrays from the outside and can get your site hacked or destroyed.

Any data received from the outside of your code should pass through mysql_real_escape_string() or mysql_escape_string()

 

That code it's only for a sample usage. Implementation in the main script should complete it. MrMonster it's saying right. Escapin characters MUST be in every website. The best way it's using that in the top script(first included file)
It's only generating a sample sql command.
Most of current day scripts includes a init stage that will parse all $_X values and strip it for bad codes. The others give no security. SQL injection can be one of the greatest pain for a website owner. Also some php settings can do that,automatically.
For escaping stuff, you can use mysql_escape for inserts, true. You must also remind that when you extract strings from database. if you insert it with mysql escape.

Btw, thanks went to MrMonster for completions