2 questions - 1 creating simple error message, 2 - approximate search matches

jarow

I have a form with two search parameters (text boxes). Users may write in a species name OR common name OR both. I have two questions about this.

since the user writes in the information I would like the search to work even if the name isn't an exact match. In other words, if it approximates the species name or common name then I would like a list of results shown. How can I write an SQL statement to search for "approximate" matches?
Enclosed below is the code for my two search parameters. Where and how should I place the following error message "No matches were found" if there is no reasonable match for either species or common name.

if (isset($HTTP_GET_VARS['especie'])) {
$varespecie_rspasserine = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['especie'] : addslashes($HTTP_GET_VARS['especie']);
}
$varnomcom_rspasserine = "%";
if (isset($HTTP_GET_VARS['nomcom'])) {
$varnomcom_rspasserine = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['nomcom'] : addslashes($HTTP_GET_VARS['nomcom']);
}
mysql_select_db($database_connpasserines, $connpasserines);
$query_rspasserine = sprintf("SELECT passerines.especie, passerines.nomcom, passerines.presencia, passerines.ah, passerines.disp, passerines.dmax, passerines.tampobl, passerines.habitatp, passerines.habitats, passerines.indice, passerines.status, passerines.nota FROM passerines WHERE passerines.especie LIKE '%s' OR passerines.nomcom LIKE '%s'", $varespecie_rspasserine,$varnomcom_rspasserine);
$rspasserine = mysql_query($query_rspasserine, $connpasserines) or die(mysql_error());
$row_rspasserine = mysql_fetch_assoc($rspasserine);
$totalRows_rspasserine = mysql_num_rows($rspasserine);
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

Thanks very much....

Did I mention I was new at this 😃 ??

Jim

cgraz

How can I write an SQL statement to search for "approximate" matches?

SELECT * FROM your_table WHERE species LIKE '%$search_terms%'

2. Enclosed below is the code for my two search parameters. Where and how should I place the following error message "No matches were found" if there is no reasonable match for either species or common name.

Your code is kinda messy, but here's the logic behind it


$query = mysql_query("SELECT . . .");

if (mysql_num_rows($query) == 0) { // if 0 records were returned
   echo "No Results Found";
} else { // 1 or more records were returned
   // display results 
}

Cgraz

jarow

thanks very much dgraz, both your anwers worked.

However, I have another problem I am hoping you can help me with. Everything works fine if I have a single parameter search...but I can't get a multiple optional parameter search to work. Is there something wrong with the following statement?

SELECT species, comname
FROM passerines
WHERE species LIKE '%varspecies%' OR comname LIKE '%varcomname%'

I am using values 1 for each variable (varspecies, varcomname) and runtime values are $HTTP_GET_VARS['species'] and $HTTP_GET_VARS['comname'] respectively

Many thanks

Jim

cgraz

the query looks fine to me (assuming your replace what's between the percent signs with variables). Is the method on your form GET? If not, then $HTTP_GET_VARS should be $HTTP_POST_VARS

Cgraz

Jim_Madrid

hi cgraz,

I had to change OR to AND and now it works fine. But I have another glitch....if I leave the text fields blank...that is if the user enters nothing and presses submit...ALL records appear on the results page...why is that? and how can I prevent it?

Thanks

Jim

cgraz

if you don't want to allow the user to view everything, then don't allow him to go forward until he does.

SELECT species, comname
FROM passerines
WHERE species LIKE '%%' AND comname LIKE '%%'

that's what your query looks like when they don't enter anything. Since the percent symbols are wildcards, it will return everything.

Just make sure they fill something out before allowing them to move forward.

Cgraz

Jim_Madrid

cgraz,

I didn´t understand too well your previous message

Actually my query looks like this:

SELECT species, comname
FROM passerines
WHERE species LIKE '%varspecies%' AND comname LIKE '%varcomname%'

varspecies
value = 1
runtime value= $HTTP_GET_VARS['varspecies']

varcomname, same basically as as varspecies but with change of name.

I am still getting all the records if I don´t put search values in the form.

If I put a search value in one of the fields that has no approximate corresponding value in the database I get the message "No results found" which is exactly what I am supposed to get.

However, if I leave the search form blank, without adding values, and then press submit...all the records are listed on the results page.

What I don´t know how to do is tell it if both (in this case) search text boxes are left empty "No results found" should appear.

Any advice?

cgraz

if your form method is set to post, check if the field has a value by doing

if($HTTP_POST_VARS['var_name'] == "") {
   echo "no records found";
} else {
   // run query
}

or if(!isset($HTTP_POST_VARS['var_name'])) {

Cgraz