php search form

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Faithe
Forum Commoner
Posts: 33
Joined: Tue Jul 12, 2005 3:26 pm
Location: WA

php search form

Post 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>"; }
}
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: php search form

Post 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).
Faithe
Forum Commoner
Posts: 33
Joined: Tue Jul 12, 2005 3:26 pm
Location: WA

Re: php search form

Post 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?
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: php search form

Post 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.
Faithe
Forum Commoner
Posts: 33
Joined: Tue Jul 12, 2005 3:26 pm
Location: WA

Re: php search form

Post 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.
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: php search form

Post 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.
Faithe
Forum Commoner
Posts: 33
Joined: Tue Jul 12, 2005 3:26 pm
Location: WA

Re: php search form

Post 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?
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: php search form

Post 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"])."%' "; }
Faithe
Forum Commoner
Posts: 33
Joined: Tue Jul 12, 2005 3:26 pm
Location: WA

Re: php search form

Post by Faithe »

Hm, that doesn't seem to work =\
It returns no results.

Could it possibly be something wrong with my form?
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: php search form

Post 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"])."%%' "; }
Faithe
Forum Commoner
Posts: 33
Joined: Tue Jul 12, 2005 3:26 pm
Location: WA

Re: php search form

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: php search form

Post by John Cartwright »

it is

Code: Select all

SELECT ... WHERE name LIKE '%Band%'
if you want to use partial matching
Faithe
Forum Commoner
Posts: 33
Joined: Tue Jul 12, 2005 3:26 pm
Location: WA

Re: php search form

Post by Faithe »

Thanks so much, that solved my problem (:
Post Reply