Best query for three textboxes in php and MySQL?

Question

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.

you mean to say that you don't want to insert data if a field is empty — Manigandan Arjunan
– Manigandan Arjunan, Commented Jan 19, 2012 at 11:37

DerVO · Accepted Answer · 2012-01-19 12:50:40Z

4

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

edited Jan 19, 2012 at 12:50

answered Jan 19, 2012 at 11:51

DerVO

3,6791 gold badge27 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Manigandan Arjunan Over a year ago

great this is what he's expecting i think

DerVO Over a year ago

@Col.Shrapnel: I agree, trim() sould be used on the inputs. But why you think empty() is not neccecary?

Your Common Sense Over a year ago

why you think it is necessary? You are using too much needless operators, making code unnecessarily complicated.

Your Common Sense Over a year ago

look, you defined these variables right before this code. Thus you don't have to check if they are set or not. thus no need to use empty() if you want to check only variable contents - if ($var) is enough.

DerVO Over a year ago

You are absolutely right of course. I use isset() and empty() so often, that I sometimes forgot, when it’s not necessary. Maybe I’m paranoid. PS: Sorry, that I have overwritten your 1st edit. It was by mistake. PPS: Anyway I would keep my curly brackets. They improve readability a lot for me.

Manigandan Arjunan · Accepted Answer · 2012-01-19 11:46:27Z

0

$firstname=mysql_real_escape_string($_POST['firstname']);
$lastname=mysql_real_escape_string($_POST['lastname']);
$nickname=mysql_real_escape_string($_POST['nickname']);
if($firstname!="" && $lastname!="" && $nickname!="")
{
     $query="SELECT * FROM `test` WHERE firstname='$firstname' and lastname='$lastname' and nickname='$nickname'";

}

answered Jan 19, 2012 at 11:46

Manigandan Arjunan

2,2651 gold badge25 silver badges43 bronze badges

1 Comment

parseha Over a year ago

no no no this not complete , i need that one or two fields is empty.

Uday Sawant · Accepted Answer · 2012-01-20 04:58:32Z

0

Try this...

if(!empty($_REQUEST['firstname'])
    $firstName = ' AND firstName = "'. mysql_real_escape_string($_REQUEST['firstname']).'"';
else 
    $firstName = '';

if(!empty($_REQUEST['lastname'])
    $lastName = ' AND lastName = "'. mysql_real_escape_string($_REQUEST['lastname']).'"';
else 
    $lastName = '';

if(!empty($_REQUEST['nickName'])
    $nickname = ' AND nickName = "'. mysql_real_escape_string($_REQUEST['nickName']).'"';
else 
    $nickname = '';


$sql="SELECT * FROM `test` WHERE 1 $firstName $lastName $nickName";

echo $sql."<br />";
?>

edited Jan 20, 2012 at 4:58

answered Jan 19, 2012 at 11:51

Uday Sawant

5,8083 gold badges35 silver badges46 bronze badges

2 Comments

gmadd Over a year ago

mysql_escape_string is deprecated in PHP 5.3.0 - php.net/manual/en/function.mysql-escape-string.php

gmadd Over a year ago

Sorry, just noticed too, you are missing the quotes around the escaped values, AND firstName = ' should be AND firstName = "'

Collectives™ on Stack Overflow

Best query for three textboxes in php and MySQL?

3 Answers 3

5 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related