CODEWITHSUNDEEP
phpmysql
matt tuman
matt tuman

Reputation: 83

dynamically generate mysql query with php

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!

Upvotes: 0

Views: 3186

Answers (3)

samjco-com
samjco-com

Reputation: 409

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.

Upvotes: 0

rebroken
rebroken

Reputation: 633

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

Upvotes: 1

Jm Verastigue
Jm Verastigue

Reputation: 398

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

Upvotes: 1

Related Questions