CODEWITHSUNDEEP
phpmysqliprepared-statement
Bunny Davis
Bunny Davis

Reputation: 407

How to make prepared mysqli statements for dynamic sql statement

I would like to make a prepared statement for dynamic SQL statement, the statement that depends on users decision. So I don’t know in advance what it will look like. I can not make a 'template' for it in advance. For example:

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli('localhost','root','','test');

if (isset($_POST['submit'])){

$build = "";

 if(!empty($_POST['city'])){
 $city=$_POST['city'];
 $build.= "AND city= $city ";

   }

 if(!empty($_POST['type'])){
 $type=$_POST['type'];
 $build.= "AND type = $type ";

   }

  $build= substr_replace($build,'',0,3);

  $sql = "SELECT * FROM proizvodi WHERE $build";

  $search=$conn->query($sql);

  $num = $search->num_rows;

        if($num>0){

            echo "<table>";

            while($row = $search->fetch_object()){
            echo "<tr>";

            echo "<td>".$row->name."</td>";
            echo "<td>".$row->surname."</td>";
            echo "<td>".$row->price."</td>";

            echo "</tr>";
    }       
            echo "</table>";
    }

}else{
     echo "Put some value";
}

?>
<form action="" method="post">
<input type="text" name="house" id="city"/>City
<input type="text" name="flat" id="type"/>Type
<input type="submit" name="submit" id="submit" value="Submit"/>
</form>

I have some ideas to make prepare statement for every $build string, but I am not sure if that is ok, and what if, for example, I have dozens of possible user inputs. That would be too much code. Is there a more elegant way to do that? Thanks!

Upvotes: 0

Views: 186

Answers (2)

Nigel Ren
Nigel Ren

Reputation: 57131

When building a prepared statement from the above idea you need to think about how to build up the SQL and the bind variables.

$build = "";
$bindType = "";   // String for bind types
$data = [];   // Array for the bound fields

if(!empty($_POST['city'])){
    $bindType .= "s";             // This is a string bind
    $data[] = $_POST['city'];     // Add the field into the list of bound fields
    $build .= "AND city= ? ";     // ? for the bound field
}

So repeat this pattern for each field you want to use (except don't re-initialise the various things including the $data array). Adding each item to $bindType builds up a string of the various types as on http://php.net/manual/en/mysqli-stmt.bind-param.php.

Then to execute the query, using the splat operator (...) to bind the array values...

$sql = "SELECT * FROM proizvodi WHERE $build";

$search=$conn->prepare($sql);
$search->bind_param($bindType, ...$data);
$search->execute();

Upvotes: 2

Dhanu K
Dhanu K

Reputation: 12433

This may help you @Bunny Davis,

$data  = $_POST[];
$query = "SELECT * FROM proizvodi WHERE 1";
foreach ($data as $key => $value) {
    $query .= " AND $key ='$value'";
}
//now $query is your final query

Upvotes: 0

Related Questions