php/mysql Search query

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
Rivaldi
Forum Newbie
Posts: 2
Joined: Thu Jul 21, 2005 10:06 am
Contact:

php/mysql Search query

Post by Rivaldi »

I made a simple html form and php query to search for people in a database.
The code is:

Code: Select all

$searchword=$_GET[searchword];
     $state=$_GET[state];
     $type=$_GET[type];
	
  $query  = "SELECT id, name, state, address, type FROM table WHERE name='$searchword' AND state='$state' AND type='$type'";
The html form is just a textbox and two drop down menus. It works if I enter in the values but I want to have an option for each catagory to include everything in that column. Like if I entered a name and choose a type and selected an option so that it included all of the states.
Thank you,
-Rivaldi
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

so you want to leave some of the fields blank and have it search for all if they're blank?

you could just check to see if they're populated, then build your where clause accordingly. Or use the like operator and replace "" with %.
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post by zoe »

So... create an option on your drop down menu named 'All' or something to that effect:

Code: Select all

if ($state == 'All'){
$query  = "SELECT id, name, state, address, type FROM table WHERE name='$searchword' AND type='$type'";
}else{
$query  = "SELECT id, name, state, address, type FROM table WHERE name='$searchword' AND state='$state' AND type='$type'";
}
Not sure if this is the most efficient solution. I'm very new to this, but it worked for me in a similar situation :roll:
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Usually i have a "search" script..

It displays all the available columns, and allows the user to type values they want to search for.. After that they are redirected to the "list" script again, but the posted search values will be used to generate additional WHERE stuff ;)

If the user decides to refine his search, he has to go back to the "search" script, where he will be able to modify the search criteria..

For this i prefer to use the empty function
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Make an option in both of your select boxes that has the value '' (empty string). Then, modify your query to be:

Code: Select all

SELECT
       id,
       name,
       state,
       address,
       type
FROM
       table
WHERE
       name LIKE '%$searchword%' AND
       state LIKE '%$state%' AND
       type LIKE '%$type%'
So when nothing is entered, $searchword and/or $state and/or $type will be blank. The query will then look for columns LIKE '%%' which will match everything.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Rivaldi
Forum Newbie
Posts: 2
Joined: Thu Jul 21, 2005 10:06 am
Contact:

Post by Rivaldi »

Thank you for your replys. I never knew about the % before but it works perfectly. Thanks again,

-Rivaldi
Post Reply