dynamically generate mysql query with php

Question

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!

Please read up on [SQL injection](http://en.wikipedia.org/wiki/SQL_injection) — Kibbee, Jun 27 '13 at 01:50

Jm Verastigue · Answer 1 · 2013-06-27T02:17:45.333

1

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' ";
}

edited Jun 27 '13 at 02:17

answered Jun 27 '13 at 02:01

Jm Verastigue

408
4
9

Awesome! And I'm guessing I can just keep on adding if statements for more complex forms? – matt tuman Jun 27 '13 at 02:06
Yes you can, just keep in mind the sanitation of your inputs. – Jm Verastigue Jun 27 '13 at 02: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 02:11

score 1 · Answer 2 · edited May 25 '19 at 14:21

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'";
}

Thanks so much! And I'll definitely check out the resources you suggested! — matt tuman, Jun 27 '13 at 02:17

samjco-com · Answer 3 · 2020-10-26T16:08:55.833

So if you want to generate MySQL queries based on form entries you might look into this functional generator below:

Php Sql Query Builder

https://github.com/nilportugues/php-sql-query-builder

This allows you to take your form results and its field names(or ids) and code it into the query builder to generate your requested query!

One bit of advice is to make sure that your field names match your table column names. This will make your process more seamless.

For example. In your case (assuming that your columns names match your form names and your table is named "members"):

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new MySqlBuilder(); // <-- use MySqlBuilder

$query = $builder->select()
    ->setTable('members')
    ->setColumns(['first_name','last_name','email']); // <-- Form names
     
echo $builder->write($query);  
?>

This will output:

SELECT members.first_name, members.last_name, members.email FROM members

Wha la!

Did are many complex queries that you can generate on the developer's GitHub page.

dynamically generate mysql query with php

3 Answers3

Php Sql Query Builder