Search DB on one or more form fields?

form database mysql search sql

[New1]

Hi,
I'm new here and have read some posts, but wanted to ask a question about forms, PHP and MySQL.

Say i have a form with three text fields:

Name
Age
Favourite Colour

What i want to be able to do is build an SQL query that would allow me to search the database based upon that info entered.

The user can enter data into one, or two, or three fields, or none at all.. how do I get it to build the sql query required to return the results?

eg. In the form, if i just enter a colour of GREEN, i would expect it to return all names, ages and favourite colours matching GREEN.

Or if they entered an age of 28, and favourite colour of BLUE, it should return all people who are 28 and have BLUE as their favourite colour.

Or if they just click "submit" with no data entered, it would return everything...

Do you see what i mean? It has to build the query based upon which fields have data entered into them....

Any ideas on how i can do this? Thank you so much

New1 (Paul) :)

[Timotheos]

Welcome to Webmasterworld New1!

See msg #5 in this thread
[webmasterworld.com...]

[New1]

Hi,

Thanks for the reply.. I have read over the other description and unfortunately it sorta helps, but doesn’t show me how to construct this particular type of query..

Form:

Name (form field name = “name”)
Age (form field name = “age”)
Favourite Colour (form field name=”colour”)

Assuming register_globals is on to access form values, the following code can be used to query the database for a simple example:

$query = “select name, age, colour from person where colour = \“$colour\””;

This is also assuming they enter a colour into the colour field.

What if they didn’t enter a colour, but an age instead? I would need a separate query to build the SQL:

Code snippet:

$query = “select name, age, colour from person where “;

if (!empty($colour)) then
 $query .= “colour=\”$colour\””;
else if (!empty($age)) then
 $query .= “age=\”$age\””;
else if (!empty($name)) then
 $query .= “name=\”$name\””;
else
 $query .= “name=\”\””;

This basically says if the colour field isn’t empty then complete the sql query, but if it is empty, then go and see if the age field is empty… if it is then check the name field to see if it is empty. If they are all empty then you have to complete the sql query, because as it is, “select name, age, colour from person where” isn’t valid syntax! So I need to complete it by having “name=””” not sure if this will work though..

This is getting complex!

It gets worse when there are two fields filled in…Age and Colour.

The query would then be:

$query = “select name, age, colour from person where “;

if (!empty($age) &&!empty($colour))
 $query .= “age=\”$age\”” AND colour=\”$colour\””;

Do you see how this is getting more and more complex… as there are more combinations…

E.g. they have filled in Name and Age, or Colour and Name, or just Name, or just Age, or just Colour, or Colour and Age.

Is this the right way to build this query, or am I making it overly complex?

Is there a simpler way?

Hope this makes sense…

Thanks,
Paul.

[Timotheos]

Ok here's my trick for building search queries with mySQL. Maybe somebody else has something more elegant.

The base sql is like this
$query = "select name, age, colour from person where 1";
This by itself works because the where part is always true.

if (!empty($colour)) then
$query .= " and colour='$colour'";
else if (!empty($age)) then
$query .= " and age='$age'";
else if (!empty($name)) then
$query .= " and name='$name'";
else {
echo "Whoops! Nothing to search on.";
exit;
}

Hope that makes sense,
Tim

BTW, It's best not to have register globals on.
[us2.php.net...]

[Blue_Tac]

I am trying to adapt this to allow me to search several fields for full or partial matches. ie when I search for green I will be returned all rows with green in any of the fields. OK, I've got the returning similar strings bit sorted with a where like statement, but now I can't seem to move on to new fields without it searching the last field mentioned. It seems as soon as I put in an if statement it skips all previous ones and only analyses the last.

code:
$query="select * from names where English like '%$queryname%'";
if(empty($English)) {
$query="select * from names where Scientific like '%$queryname%'";
}
else if(empty($Scientific)) {
$query="select * from names where Afrikaans like '%$queryname%'";
}

[Timotheos]

This is from the manual [php.net]

There may be several elseifs within the same if statement. The first elseif expression (if any) that evaluates to TRUE would be executed.

The elseif statement is only executed if the preceding if expression and any preceding elseif expressions evaluated to FALSE, and the current elseif expression evaluated to TRUE.

So echo your empty conditions to see if they are what you're expecting. When I first started I would get confused between empty [php.net] and isset [php.net]. Typically when variables come from GET or POST I use isset.

[Blue_Tac]

Cheers for your help. In the end I think I cheated and just used an OR statement between each search criterion, eg.
$query="select * from names where English like '%$queryname%' OR Scientific like '%$queryname%'"

I know it's a bit messy, but it works :)