动态绑定(bind)问号的PHP函数/过程

Question

我是 PHP 的新手。我需要通过编写自定义函数来绑定(bind) PDO 中的参数。

假设这些是我拥有的 2 个 sql。

sample_sql_1="select f_name, age, address from table1 where l_name=? and dob >= ? and cty =?"

sample_sql_2="select * from table2 where cty=?"

我想编写一个函数来接受有问题的 sql 查询并将参数绑定(bind)到问号，而不管我传递了多少参数。

例子:我想打电话说，

bind_params(sample_sql_1,array($name,$dob,$cty));
bind_params(sample_sql_2,array($cty));

这是我到目前为止编写的用于连接到数据库的函数

function pdo_db_query($query) {
    try {
    # MySQL with PDO_MYSQL
    $DBH = new dbconn(); // Create DB connection

    $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $DBH->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
    $STH = $DBH->prepare($query);

    // Please help to create a dynamic function to bind
     bind_params(sample_sql_1,array($name,$dob,$cty));
     bind_params(sample_sql_2,array($cty));

    / Execute the query
    $STH->execute();

    # setting the fetch mode
    $STH->setFetchMode(PDO::FETCH_ASSOC);

    // Create temporary array variable
    $json_arr = array();

    while ($row = $STH->fetch()) {
        $json_arr[] = $row;
    }

    # Close the connection
    $DBH = null;

    // Return the result set as a json
    echo json_encode($json_arr);
    } catch (PDOException $e) {
    echo $e->getMessage();
    var_dump($e->getMessage());
    }
}

我需要帮助编写函数“bind_params”。任何帮助都会让我受益匪浅。

Answer 1

您不一定需要 bind_params()，您可以将值作为数组提供给 execute()。

请参阅 documentation 中的示例:

/* Execute a prepared statement by passing an array of insert values */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->execute(array($calories, $colour));

专门针对您的情况:

// add a parameter for the values
function pdo_db_query($query, $params = array()) {
    try {
    # MySQL with PDO_MYSQL
    $DBH = new dbconn(); // Create DB connection

    $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $DBH->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
    $STH = $DBH->prepare($query);

    // Execute the query with the given params
    $STH->execute($params);

    # setting the fetch mode
    $STH->setFetchMode(PDO::FETCH_ASSOC);

    // Create temporary array variable
    $json_arr = array();

    while ($row = $STH->fetch()) {
        $json_arr[] = $row;
    }

    # Close the connection
    $DBH = null;

    // Return the result set as a json
    echo json_encode($json_arr);
    } catch (PDOException $e) {
    echo $e->getMessage();
    var_dump($e->getMessage());
    }
}

将其与 LIKE 查询一起使用:

$query = "SELECT * FROM table WHERE field LIKE ?";
$params = array( '%' . $searchvalue . '%' );
$result = pdo_db_query( $query, $params );