mysql WHERE statement

[thorin]

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 :-

Code:

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

'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.

[eelixduppy]

You cannot use '=' with the wild-card character. Try a query like this:

 SELECT *
FROM catches
WHERE name LIKE 'colname' AND pegno LIKE 'colpeg' AND specie LIKE 'colspecie'
ORDER BY name ASC

Also, don't forget to escape your variables with mysql_real_escape_string [php.net].

Good luck :)

[thorin]

Thank you, I tried your suggestion, but it didn't return any records.

The action code in PHP is :-

<?php

$colpeg_rsName = "1";
if (isset($_POST['gkpeg'])) {
$colpeg_rsName = (get_magic_quotes_gpc())? $_POST['gkpeg'] : addslashes($_POST['gkpeg']);
}
$colspecie_rsName = "1";
if (isset($_POST['gkspecie'])) {
$colspecie_rsName = (get_magic_quotes_gpc())? $_POST['gkspecie'] : addslashes($_POST['gkspecie']);
}
$colname_rsName = "1";
if (isset($_POST['gkname'])) {
$colname_rsName = (get_magic_quotes_gpc())? $_POST['gkname'] : addslashes($_POST['gkname']);
}
mysql_select_db($database_roughamlake, $roughamlake);
$query_rsName = sprintf("SELECT * FROM catches WHERE name = '%s' AND pegno = '%s' AND specie = '%s' ORDER BY name ASC", $colname_rsName,$colpeg_rsName,$colspecie_rsName);
$rsName = mysql_query($query_rsName, $roughamlake) or die(mysql_error());
$row_rsName = mysql_fetch_assoc($rsName);
$totalRows_rsName = mysql_num_rows($rsName);
?>

Does this make any difference?

[barns101]

Try

$query_rsName = sprintf("SELECT * FROM catches WHERE name LIKE '%s' AND pegno LIKE '%s' AND specie LIKE '%s' ORDER BY name ASC",

[thorin]

Thank you it works perfectly now.