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?