parseha parseha - 2 months ago 6
MySQL Question

Best query for three textboxes in php and MySQL?

I have one table:

CREATE TABLE IF NOT EXISTS `test` (
`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" />
</form>


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

firstname=$_REQUEST['firstname']


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

Taking the start from Ampere ...

$firstname=mysql_real_escape_string(trim($_POST['firstname']));
$lastname=mysql_real_escape_string(trim($_POST['lastname']));
$nickname=mysql_real_escape_string(trim($_POST['nickname']));

... 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}%'

Comments