current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm trying to learn how to dynamically generate a mysql query based on the form fields that a user chooses to fill with data. In-order to make the learning process as easy as possible I'm using a simple form with a field for the users first name and last name. The basic (non-dynamic) version of the code is as follows:

<html>
<head>

<title>Untitled</title>

</head>
<body>

<form method="post" name="test" action="dynamic_search.php">
<input type="text" name="first_name">
<input type="text" name="last_name">
<input type="submit" value="Submit">

</form>

<?php

$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];

include "link.php";

$query = "SELECT * FROM members " .
     "WHERE first_name = '$first_name' " .
     "AND last_name = '$last_name' ";

$result = mysql_query($query) 
or die(mysql_error());

$row = mysql_fetch_array($result);
$member_id = $row['member_id'];
$member_first_name = $row['first_name'];
$member_last_name = $row['last_name'];

echo $member_id;
echo $member_first_name;
echo $member_last_name;

?>
</body>
</html>

What I need to be able to do is generate a query based on the data submitted. So if the user only enters their first name the query would read as :

$query = "SELECT * FROM members " .
     "WHERE first_name = '$first_name' ";

But if the user enters both their first and last name the query would read as :

$query = "SELECT * FROM members " .
     "WHERE first_name = '$first_name' " .
     "AND last_name = '$last_name' ";

Any help (or if someone can point me towards a good tutorial) would be greatly appreciated!

Thanks!

share|improve this question
    
Please read up on SQL injection –  Kibbee Jun 27 '13 at 1:50
add comment

3 Answers

up vote 1 down vote

One option is to check for blank:

$query = "SELECT * FROM members " .
     "WHERE ('$first_name' = '' OR first_name = '$first_name') " .
     "AND ('$last_name' = '' OR last_name = '$last_name') ";

This way, it will equal true if first_name is blank for example (just make sure your parentheses are written appropriately).

share|improve this answer
    
That would only work if the record in the database for first or last name was empty. But thanks –  matt tuman Jun 27 '13 at 1:59
1  
@matttuman -- why? This should work if $first_name is blank -- the variable being passed in. –  user2480596 Jun 27 '13 at 2:00
    
If the user enters the first name Nick but doesn't enter their last name the database would be searched for records containing first name Nick and last name empty...In this scenario I want a query that would only search for first names that equal Nick –  matt tuman Jun 27 '13 at 2:04
    
@matttuman -- if the user enters Nick as the first name and doesn't enter a last name, this query will search for first_name = 'Nick', as well as '' = '' (for the last_name) -- the OR is the key... Give it a try -- it should work :D –  user2480596 Jun 27 '13 at 2:08
add comment
up vote 1 down vote

First, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. (Credit)

Second, a caution to always escape user input being included in an SQL statement. Prepared statements handles this for you automatically.

Having said that, the PHP logic that you're after is something like this:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

$first_name = $mysqli->real_escape_string($_POST['first_name']);
$last_name  = $mysqli->real_escape_string($_POST['last_name']);

$sql = "SELECT * FROM members WHERE 1";
if (! empty($first_name)) {
    $sql .= " AND first_name = '$first_name'";
}
if (! empty($last_name)) {
    $sql .= " AND last_name = '$last_name'";
}
share|improve this answer
    
Thanks so much! And I'll definitely check out the resources you suggested! –  matt tuman Jun 27 '13 at 2:17
add comment
up vote 1 down vote

You can use PHP to check the input and append to the query when necessary.

$query = "SELECT * FROM members ";
$query .= "WHERE first_name = '$first_name' ";
if($last_name!="")
    $query .="AND last_name = '$last_name' ";

Remember to escape the strings my using real_escape_string

$first_name = mysql_real_escape_string($_POST['first_name']);

In case you want to check for the first name:

$query = "SELECT * FROM members ";
if($first_name!=""){
    $query .= "WHERE first_name = '$first_name' ";
    if($last_name!="")
        $query .="AND last_name = '$last_name' ";
}
else{
    if($last_name!="")
        $query .="WHERE last_name = '$last_name' ";
}
share|improve this answer
    
Awesome! And I'm guessing I can just keep on adding if statements for more complex forms? –  matt tuman Jun 27 '13 at 2:06
    
Yes you can, just keep in mind the sanitation of your inputs. –  Jm Verastigue Jun 27 '13 at 2:11
    
Although, if the user does the opposite and fills in their last name while leaving their first name blank I'll have the same issue. –  matt tuman Jun 27 '13 at 2:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.