Query that works in phpmyadmin is NOT working in php page?

Question

I have tested this query in phpmyadmin & it returns exactly what I'm looking for...it duplicates row1 & updates the title to DUPLICATE.

$sql = "CREATE TEMPORARY TABLE  tmp 
        SELECT  `unit_id`,
                    `title`,
                    `status_id`,
                    `category_id`,
                    `tags`,
                    `access_id`
        FROM        unit_genData 
        WHERE       `unit_id`='1';
        ALTER TABLE tmp 
        DROP COLUMN `unit_id`;
        UPDATE      tmp 
        SET         `title` = 'DUPLICATE';
        INSERT INTO unit_genData 
        SELECT      0,tmp.* 
        FROM        tmp;
        DROP TABLE  tmp;";

Then I go and add it to a php page, and...

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALTER TABLE tmp DROP COLUMNunit_id; UPDATE tmp ' at line 10

Why am I getting this error?

did you try surrounding the table name tmp with single quotes? — Fawzan, Nov 07 '14 at 05:24
Unless you are using [`mysqli_multi_query()`](http://php.net/manual/en/mysqli.multi-query.php), your query fails, as your query has to be 1 query only, so it is failing on your first `;` — Sean, Nov 07 '14 at 05:25
Why create a table and then alter it with next query? Why not just create it the way you want it? — Mike Brant, Nov 07 '14 at 05:30
@MikeBrant in this event, I'm duplicating a row. Wanted to duplicate the selected row's data & create a new entry row — cpardon, Nov 07 '14 at 05:41
Ok. Why not Insert into ... Select ... Syntax instead of all the temp table business? You should be able to do thus with a single query. — Mike Brant, Nov 07 '14 at 05:46
@MikeBrant tried it but it wasn't working with the INSERT with no unit_id. I saw this temp table concept & it worked. Easy choice — cpardon, Nov 07 '14 at 05:51

score 1 · Accepted Answer · answered Nov 07 '14 at 05:25

use mysqli_multi_query() for executing multiple queries.

$sql = "CREATE TEMPORARY TABLE  tmp 
    SELECT  `unit_id`,
                `title`,
                `status_id`,
                `category_id`,
                `tags`,
                `access_id`
    FROM        unit_genData 
    WHERE       `unit_id`='1';
    ALTER TABLE tmp 
    DROP COLUMN `unit_id`;
    UPDATE      tmp 
    SET         `title` = 'DUPLICATE';
    INSERT INTO unit_genData 
    SELECT      0,tmp.* 
    FROM        tmp;
    DROP TABLE  tmp;";

$mysqli->multi_query($sql);

score 0 · Answer 2 · answered Nov 07 '14 at 05:25

By default, PHP disables multiple statements in a single query.

Either run the two statements separately, or you can use mysql_multi_query.

http://php.net/manual/en/mysqli.quickstart.multiple-statement.php

(Question doesn't specify which MySQL interface is used.)

Note that if your code is subject to SQL Injection vulnerabilities, then enabling multiple statements per query can throw open the door to a whole boatload of nefariousness... ala Little Bobby Tables http://xkcd.com/327/.

score 0 · Answer 3 · edited May 23 '17 at 10:25

0

If you're for some reason using mysql functions pass CLIENT_MULTI_STATEMENTS as mysql_connect's 5th parameter to allow multiple statements in your queries.

More info here.

edited May 23 '17 at 10:25

Community

1
1

answered Nov 07 '14 at 05:27

Eliel

164
6

Query that works in phpmyadmin is NOT working in php page?

3 Answers3