0

I must be stumped. I'm actually asking for Stack Overflow help.

PDO stuff:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

I am using PHP 7.4 and MySQL 5.7.36 on WAMP Server to develop. I am getting the following error:

PDOStatement::errorInfo():

"Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE `emailaddress` AS SELECT @seq := @seq + 1 as `rid`, `LastName`, `Fi' at line 1 )"

when creating and execution the following query from PHP/PDO:

SET @seq = 0;<br />
CREATE TABLE `emailaddress` AS SELECT @seq := @seq + 1 as `rid`, `LastName`, `FirstName`, `Email`
FROM `crew` WHERE `Status` = "A" AND `Approved` != "N" AND (`Email` IS NOT NULL OR `Email` != "");

When I cut and paste the identical query into PHPAdmin 5.2.0 it executes no problem?

PHP context:

$query = "SET @seq = 0; " .
"CREATE TABLE `emailaddress` AS SELECT @seq := @seq + 1 as `rid`, `LastName`, `FirstName`, `Email`" .
" FROM `crew` " .
'WHERE `Status` = "A" AND `Approved` != "N" AND (`Email` IS NOT NULL OR `Email` != "")';
...

        try {
            $result = $db->query($query);
        }
        catch (PDOException $e) {
            LogError($e->getMessage());
            echo "<br /><br />PDOStatement::errorInfo():<br /><br /><br />";
            $arr = $db->errorInfo();
            print_r($arr);
            echo "<br /><br /><br />".$e;Exit;
...
JNevill
  • 46,980
  • 4
  • 38
  • 63
east Texas Bubba
  • 1
  • 1
    I'm no PDO expert, but it could very well be that you have two statements in a single pdo->query(). I see conflicting information online saying whether pdo can handle multiple statements although those appear to be speaking to `pdo->execute()`. – JNevill Aug 12 '22 at 18:25
  • 1
    Yes, @JNevill is correct, PDO sees the `SET` and `SELECT` as two statements, and only one is allowed at a time: https://stackoverflow.com/a/4685823/231316 – Chris Haas Aug 12 '22 at 18:30
  • @ChrisHaas I saw that Q&A but hesitated to refer to it since it's 11 years old. OP, you may want to try executing `->query()` twice in that `try` block. Once for the the `SET` and once again for the `INSERT`. It should run under the same session in mysql and work. – JNevill Aug 12 '22 at 18:50
  • I don't really deal with PDO much either, at least at this level. I'm seeing some talk that MySQL _does_ support multiple queries, however `ATTR_EMULATE_PREPARES` disables this. I think. This depends on MySQL version and library (mysqlnd, libmysql). There's also `MYSQL_ATTR_MULTI_STATEMENTS` which came out a couple of years back that can be used to disable it, too, if I'm reading it correctly. – Chris Haas Aug 12 '22 at 19:13
  • There's little practical benefit to putting multiple statements in one call. Just call `$db->query()` separately for each statement. – Barmar Aug 12 '22 at 19:19
  • 1
    I also don't see why you need the `@seq` variable. MySQL has `AUTO_INCREMENT`, you can use that to assign an incrementing `rid` column automatically. – Barmar Aug 12 '22 at 19:20
  • 2
    The former director R&D for MySQL once told me, "there's no reason for multi-query to exist." He meant that there's no advantage to sending two SQL statements in a single call to `query()`, and some things (like error checking each statement) are simpler if you avoid doing so. – Bill Karwin Aug 12 '22 at 19:56
  • OK, Y'all. I temporarily overcame the situation by just creating a prepared statement to execute the table create. I will experiment some more although I think I have used multi queries before. This is the first serious issue I have had with PDO and have used it extensively since switching several years ago. – east Texas Bubba Aug 13 '22 at 19:44

0 Answers0