SQL query in PHP with predicates in an array

Question

I want to prepare an SQL query in PHP using parameters from an array. It should be a prepared statement.

Assuming that this is my array with parameters

$params = array("arg1" => 1, "arg2" => 0, "arg3" => 1)

I want my query to look like

SELECT * FROM table WHERE arg1 = 1 AND arg2 = 0 AND arg3 = 1

What have you tried so far? – Nigel Ren Apr 14 '19 at 08:05 — Nigel Ren, Apr 14 '19 at 08:05
https://www.php.net/manual/en/mysqli-stmt.bind-param.php – SHR Apr 14 '19 at 13:28 — SHR, Apr 14 '19 at 13:28

score 0 · Answer 1 · edited Jun 20 '20 at 15:17

$qry= 'SELECT * FROM table WHERE ';
foreach($params as $key => $value)
{
    $qry .= $key . '=' . $value . ' AND ';
}
$qry = substr($qry, 0, -5); // remove last ' AND '

This should be enough to get you started. Depending on what versions of MySQL/SQL/PHP you are running, you may need to put in quotes around variables. This solution does not address SQL injection or prepared statements. You can add the ? or the :variable depending on how you are structuring your prepared statements.

Other options... You can implode the array to a ' AND ' separated string. See Stack Overflow question How to use array variable into mysql query in php/mysql

score 0 · Answer 2 · edited Jun 20 '20 at 15:21

If you want to manage a prepared statement using PDO, try the following code:

<?php
    $servername = "hostname";
    $username = "username";
    $password = "password";
    $dbname = "database";

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

        // Set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // Prepare SQL content
        $sql = "SELECT * FROM Persons";

        // Considering this is the array you want to use to prepare the SQL content
        $params = array("LastName" => 1, "FirstName" => 1, "Address" => 1);
        foreach($params as $attr => $val) {
            $where[] = "$attr = :$attr";
        }
        $sql .= " where " . join(' and ', $where); // *** where LastName = :LastName and FirstName = :FirstName and Address = :Address

        $stmt = $conn->prepare($sql); //  *** SELECT * FROM Persons where LastName = :LastName and FirstName = :FirstName and Address = :Address

        // Bind parameters
        foreach($params as $attr => $val) {
            $stmt->bindValue(":$attr", $val, PDO::PARAM_STR);
        }

        $stmt->execute();

        //$stmt->debugDumpParams();

        echo $stmt->rowCount();

        print_r($stmt->fetch());
    }
    catch(PDOException $e)
    {
        echo "Error: " . $e->getMessage();
    }

Or if you want to manage a prepared statement using mysqli, try the following:

<?php
    $servername = "hostname";
    $username = "username";
    $password = "password";
    $dbname = "database";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    $sql = "SELECT * FROM Persons";

    // Considering this is the array you want to use to prepare the SQL content
    $params = array("LastName" => 1, "FirstName" => 0, "Address" => 1);
    $sqltype = "";

    foreach($params as $attr => $val) {
        $where[] = "$attr = ?";
        $sqltype .= 's';
        $bind_val[] = $val;
    }

    $sql .= " where " . join(' and ', $where); // *** SELECT * FROM Persons where LastName = ? and FirstName = ? and Address = ?

    // Prepare SQL content
    $stmt = $conn->prepare($sql);

    // Bind parameters
    $stmt->bind_param( $sqltype, ...$bind_val ); // *** $stmt->bind_param("sss", 1, 0, 1);

    $stmt->execute();

    $result = $stmt->get_result();

    echo $result->num_rows;
    print_r($result);

SQL query in PHP with predicates in an array

2 Answers2