mysql WHERE statement

[beyton7]

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

Code:

$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);
?>

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

 

[r937]

the only part of your question that i can understand is this --

SELECT * FROM catches
WHERE name = '%s'
AND pegno = '%s'
AND specie = '%s'
ORDER BY name ASC

i suspect you should be using LIKE instead of equals

if all that perl code is important to the question, perhaps you should post in the perl forum

r937.com | rudy.ca

 

[beyton7]

Thank you, I have tried using LIKE instead of %, but it returns nothing.

Its not perl, but PHP, but its the mysql part of the query that I am struggling with, hence the post in this forum.

mysql code:
$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);

 

[towerbase]

This is the MySQL forum and you should not assume that we know PHP or any other specific language.

The way I usually tackle this kind of problem is to build up the SELECT statement clause by clause.

Using pseudo code something along these lines should work:

statement = 'SELECT * FROM catches WHERE 0=0'

if namebox <> 'All' then
  statement = statement + ' AND name = ' + namebox

if specbox <> 'All' then
  statement = statement + ' AND spec = ' + specbox

...

statement = statement + ' ORDER BY name ASC'

If you still have a problem show us the actual SELECT statement that has been created and the results of executing the SELECT statement.

Andrew
Hampshire, UK

 

[r937]

WHERE name LIKE '%todd%'
AND pegno LIKE '%foo%'
AND specie LIKE '%horse%'

note that when using LIKE, the search string has wildcards (percent signs) appended front and back

if the default for one of the dropdowns is 'All' then of course what you do in that instance is not generate that line into the sql

so if the dropdown for pegno is 'All' then you would run this --

WHERE name LIKE '%todd%'
AND specie LIKE '%horse%'


r937.com | rudy.ca

 

[beyton7]

You'll have to forgive me, as I am a total newbie to mysql, the actual mysql SELECT statement is :-

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

'colname' , 'colpeg' , 'colspecie' are my 3 variables, and in the dropdown boxes, the label is ALL, but the value against that label is %.

Would the % before and after and using the LIKE, now look like this ?

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

 

[r937]

that depends, and i apologize if i got confused

if you want the search string to be found somewhere within the column value then you would use LIKE

if you want an exact match then you would use equals

r937.com | rudy.ca

 

[beyton7]

No need to apologize, I should have worded my first post better.

You are quite correct, replacing the = with LIKE solved my problem, thank you for your help.