parseha parseha - 1 year ago 38
MySQL Question

Best query for three textboxes in php and MySQL?

I have one table:

`firstname` varchar(100) NOT NULL,
`lastname` varchar(100) NOT NULL,
`nickname` varchar(100) NOT NULL

I have this html code:

<form action="" method="post">
<input name="firstname" type="text" />
<input name="lastname" type="text" />
<input name="nickname" type="text" />
<input type="submit" value="search" />

I need that when I fill
text box my query condition is:


And when this text box is not filled in this condition should not be used.
I want it that when I fill two text boxes or three text boxes, the correspond condition is checked but I need one query to do this.

Answer Source

Taking the start from Ampere ...


... and then build the query depending on which input fields are not empty:

$query="SELECT * FROM `test` WHERE 1";
if ($firstname) $query .= " AND `firstname`='$firstname'";
if ($lastname) $query .= " AND `lastname`='$lastname'";
if ($nickname) $query .= " AND `nickname`='$nickname'";

If you leave all fields blank, your query will select all rows. If you enter some information, every given field must match the appropriate column.

PS: Maybe you want also to return "Edward" as well, when someone enters "Ed" into the the firstname field. You can use LIKE '%Ed%' instead of ='Ed' in your WHERE statements: firstname LIKE '%{$firstname}%'