PDO Multiple Queries not working

Question

I am using PDO to run a query, and it isn't working how I would like. I wrote a wrapper around pdo so, and here is the main query method:

<?php
public function query($query, $params = array())
{
    if(!is_array($params))
    {
        throw new Exception("Parameter 2 must be a key => value array.");
    }
    if(!$this->pdo)
    {
        $this->connect();
    }
    echo "
--------------------------------------------------------------------------------

$query

--------------------------------------------------------------------------------    
    ";
    $this->sql = $this->pdo->prepare($query);
    foreach($params as $key => $value)
    {
        $this->sql->bindParam($key, $value);
    }
    $sql = $this->sql->execute();
    if(!$sql)
        throw new Exception('[' . $this->sql->errorCode() . ']: ' . $this->sql->errorInfo());
}

I can not show the whole query, but here are parts of it:

    $str = "SET @usapev = :usapev;
    SET @canpev = :canpev;
    SET @ven = :ven;
    SET @usa = :isUSA;
    SET @can = :isCAN;

    DROP TEMPORARY TABLE IF EXISTS T_MailingList;
    CREATE TEMPORARY TABLE T_MailingList
    (
        bsg_uk INT NOT NULL PRIMARY KEY,
        demog_id INT NOT NULL,
        procardnbr VARCHAR(12)
    );

    INSERT IGNORE INTO T_MailingList
    SELECT m.bsg_uk
    , m.demog_id
    , m.procardnbr
    FROM FROM bsg.member m;";

I then run the query like so:

<?php
    $this->db->query($str, array(
        "ven"    => $ven,
        "usapev" => $usapev,
        "canpev" => $canpev,
        "isUSA"  => (int)$is_usa,
        "isCAN"  => (int)$is_can
    ));

When the query is echoed out in the echo in query() I copy that to workbench and run it, and it works fine in there. No errors, and I get results back. In pdo when I run another query that wants to use the temporary table, the table doesn't exist.

Why is it not working in PDO?

Possible duplicate of [PDO support for multiple queries (PDO\_MYSQL, PDO\_MYSQLND)](http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd). Accepted answer is quite comprehensive. — Álvaro González, Mar 04 '13 at 17:00
This is not a query but rather set of queries. just run them one by one. — Your Common Sense, Mar 04 '13 at 17:02

score 0 · Answer 1 · answered Mar 04 '13 at 17:03

It probably has to do with being able to prepare only one query at a time. Here's how afaik prepared statements work: the query is sent to mysql first, then the parameters are sent separatelly. Since you are sending multiple queries to mysql, that probably is the cause of your problem.

PS: I would suggest a stored procedure for what you want to achieve.

score 0 · Accepted Answer · answered Mar 04 '13 at 17:04

0

Use

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

or explode the query with ";" and run each query in a loop

$queries = explode(";", $query);
foreach ($queries as $query) {
    $pdo->query($query, $attrs);
}

answered Mar 04 '13 at 17:04

Philipp

15,377
4
35
52

I think that may have fixed it, I am getting a new error now, so I well need fix that first and I will get back to you! – Get Off My Lawn Mar 04 '13 at 17:15
I am getting this message with my bindParam stmt now, because my prepare stmt is coming back false: `PHP Fatal error: Call to a member function bindParam() on a non-object in /var/www/reportBuilder/classes/Mysql.class.php on line 102` – Get Off My Lawn Mar 04 '13 at 17:19

PDO Multiple Queries not working

2 Answers2