Page 1 of 1
php search form
Posted: Wed Apr 02, 2008 4:49 pm
by Faithe
The form I have consists of a name, age, gender, and position field. As it stands right now, when you enter information into the form it goes to a simple mysql query (below) that takes the information and finds the corresponding rows in the database. The problem is, for example, if you input the age as 25 and the gender as male (or any other combination), instead of narrowing the search down to only 25 year old males, it gives me all the 25 year olds and all the males in the database. If someone could show me how to solve this problem, i'd really appreciate it. Thanks.
Code: Select all
//retrieve the results from the database
$find = mysql_query("SELECT * FROM bmem WHERE name = '".$name."' OR age = '".$age."' OR gender = '".$gender."' OR position = '".$position."'");
while ($results = mysql_fetch_array($find)) {
echo $results['name'];
echo "<br>"; }
}
Re: php search form
Posted: Wed Apr 02, 2008 5:17 pm
by micknc
It is the operator OR. Change it to AND. This will limit the query to return only the rows that match all of the criteria. Or is saying that age can = 25 OR gender = male (if either is true). And would return where age = 25 and gender = male (only if both are true).
Re: php search form
Posted: Wed Apr 02, 2008 5:38 pm
by Faithe
I tried the operator AND as well but then I get no results returned. I think that might be because the other fields are left blank so it's looking for rows with null fields?
Any alternatives?
Re: php search form
Posted: Wed Apr 02, 2008 6:11 pm
by micknc
Yeah that is right it would only work if all the fields where filled in. To check for blanks you would do something like this:
Code: Select all
$find = "SELECT * FROM bmem WHERE";
if( isset($_POST["name"]) && trim($_POST["name"]) != "" )
{
$find.= " AND name='". mysql_real_escape_string($_POST["name"])."'";
}
if( isset($_POST["age"]) && trim($_POST["age"]) != "" )
{
$find.= " AND age='". mysql_real_escape_string($_POST["age"])."'";
}
And so one for all of your variables...
Your while statement would need to be change to look like this:
Code: Select all
$results = mysql_query($find);
while ($results = mysql_fetch_array($find)) {
echo $results['name'];
echo "<br>"; }
}
Give that a try and you should be okay.
Re: php search form
Posted: Thu Apr 03, 2008 12:18 pm
by Faithe
Great, thank you. I tweaked it a little bit to fit the code and it works perfectly.
On a side note, I'd also like the form be able to find entire names based on only a partial entry. For example, entering "test" will bring up any name entry containing that phrase.
Let me know if I should repost this as a new topic or not. Thanks.
Re: php search form
Posted: Thu Apr 03, 2008 8:49 pm
by micknc
Sure that isn't too hard. Here is a little info that will help you:
In MySQL there is a "wildcard" character '%' that can be used to search for partial matches in your database. The '%' tells MySQL to ignore the text that would normally appear in place of the wildcard. For example '2%' would match the following: 20, 25, 2000000, 2avkldj3jklsaf, and 2!
On the other hand, '2%' would not match the following: 122, a20, and 32.
You can use this wildcard at the beginning, middle, and end of the string.
Re: php search form
Posted: Sun Apr 06, 2008 8:39 pm
by Faithe
Code: Select all
if( isset($_POST["name"]) && trim($_POST["name"]) != "" )
{ $find.= " AND name=' % ". mysql_real_escape_string($_POST["name"])." % ' "; }
So, using that info, shouldn't this give me the desired result?
Re: php search form
Posted: Mon Apr 07, 2008 5:39 pm
by micknc
Just watch out for your spacing... I think your code would actually output "AND % post %. That space will get you. Try it like this:
Code: Select all
if( isset($_POST["name"]) && trim($_POST["name"]) != "" )
{ $find.= " AND name='%". mysql_real_escape_string($_POST["name"])."%' "; }
Re: php search form
Posted: Mon Apr 07, 2008 10:24 pm
by Faithe
Hm, that doesn't seem to work =\
It returns no results.
Could it possibly be something wrong with my form?
Re: php search form
Posted: Tue Apr 08, 2008 8:40 am
by micknc
echo it out in your output and see exactly what you are passing to the query.
Ex: echo $find;
That should produce everything after the where statement in the query.
Also, I am not sure why but I have seen and used two percentage signs, at times with better results.
Code: Select all
if( isset($_POST["name"]) && trim($_POST["name"]) != "" )
{ $find.= " AND name='%%". mysql_real_escape_string($_POST["name"])."%%' "; }
Re: php search form
Posted: Thu Apr 10, 2008 8:13 pm
by Faithe
Code: Select all
SELECT * FROM bmem WHERE employer='2' AND name='%Band%'
That's the statement my query is outputting. I also tried it with the percent signs outside the apostrophes as well as the double percents like you suggested. However, still no results :/
For the record, every name listed in the database is Band Member (Random Number), so it should be returning all the rows, right?
Re: php search form
Posted: Thu Apr 10, 2008 10:07 pm
by John Cartwright
it is
Code: Select all
SELECT ... WHERE name LIKE '%Band%'
if you want to use partial matching
Re: php search form
Posted: Mon Apr 14, 2008 9:53 pm
by Faithe
Thanks so much, that solved my problem (: