php中的SQL查询与数组中的谓词

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 looks like

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

$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. How to use array variable into mysql query in php/mysql

If you want to manage 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
    $sql = "SELECT * FROM Persons";

    // considering this is the array you want to use to prepare the sql
    $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 prepared statement using mysqli, try 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
$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
$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);