如何以“可重用”的方式将参数传递给数组中的PDO SELECT语句？

Question

I'm new to PDO and have successfully converted most of my site's inserts and updates to PDO transactions, each in a centrally accessible function for maximum reuse. I really want to do the same with the SELECT statements, but this seems harder!

What I was expecting to do is this:

function getProdDetails2SaveInInvoice($data) {

    global $dbh;

    try {
        $sth=$dbh->prepare("
        SELECT
            AES_DECRYPT(?, '".DBKEY,"'),
            AES_DECRYPT(?, '".DBKEY,"'),
            AES_DECRYPT(?, '".DBKEY,"')
         FROM
            products
        WHERE
            ? ? ?
    ");

        $sth->execute($data);
        $sth->fetch(PDO::FETCH_OBJ);

        return $sth;
    }

    catch(PDOException $e) {
        echo  "Something went wrong. Please report this error.
";
        file_put_contents(
            $_SERVER['DOCUMENT_ROOT']."/PDOErrors.txt",
            "

Script name : ".SCRIPT."
Function name : ".__FUNCTION__."
".
            $e->getMessage(), FILE_APPEND);

        throw new failedTransaction();
    }
}

// Fetch additional info from invoice_products.
        $data = array(
            'alt_id',           /* field 1                  */
            'prod_name',        /* field 2                  */
            'prod_desc',        /* field 3                  */
            'prod_id',          /* where                    */
            '=',                /* operator                 */
            $prodid         /* comparison               */
        );
        $rs = getProdDetails2SaveInInvoice($data);

Unfortunately, this doesn't work and returns the error,

ERROR:"Call to a member function execute() on a non-object".

I can confirm that the $dbh database connection is working as it's the same connection working for the inserts and updates.

Answer 1

You can't bind the field names. Assuming DBKEY is a constant, your query should look like this:

$sth=$dbh->prepare("
        SELECT
            AES_DECRYPT(alt_id, ?),
            AES_DECRYPT(prod_name, ?),
            AES_DECRYPT(prod_desc, ?)
         FROM
            products
        WHERE
            prod_id = ?
    ");

and $data like this:

$data = array(
            DBKEY, 
            DBKEY, 
            DBKEY,
            $prodid
        );