Multiple MySQL queries in one statement

Question

I'm trying to insert data into one table and select the ID and insert additional information in another table related to the ID, My code is below (I'm not exactly advanced so please be patient, I'm looking to solve this not learn something completely different if possible), This code works up to "INSERT INTO sb_admins_servers_groups" then stops.

    $sql = 'INSERT INTO sb_admins (user, authid, password, gid, email, validate, extraflags, immunity, srv_group, srv_flags, srv_password, lastvisit)
    VALUES (:name, :ID, "#######", "-1", "", "", "0", "0", "X Permissions", "", "", "");
    SELECT aid FROM sb_admins WHERE authid = :ID
    INSERT INTO sb_admins_servers_groups (admin_id, group_id, srv_group_id, server_id)
    VALUES (aid, "5", "4", "-1")';
    $s=$pdo->prepare($sql);
    $s->bindvalue('name', $player->name);

    $s->bindvalue('ID', $player->uniqueid);

    $s->execute();
    }
    catch(PDOException $e)
    {
    echo "There was an error " . $e->getMessage() . "";
    exit();
    }

You can find all the info you need in a previous question here: http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd — icecub, Feb 17 '15 at 19:20
There may be an issue using the same named placeholder more than once in a statement. The documentation says that this will work if "emulate mode is on" (`PDO::ATTR_EMULATE_PREPARES`), but in my experience it doesn't. (My experience is a bit dated, likely with a previous release of PDO). Reference: [https://bugs.php.net/bug.php?id=33886](https://bugs.php.net/bug.php?id=33886) . I recommend that you may want to first test using distinct bind placeholders, and then test out using the same named bind placeholder more than once.) — spencer7593, Feb 17 '15 at 19:38

score 0 · Answer 1 · answered Feb 17 '15 at 19:21

0

I think a semicolon(";") is missing after line SELECT aid FROM sb_admins WHERE authid = :ID. It should be SELECT aid FROM sb_admins WHERE authid = :ID;

answered Feb 17 '15 at 19:21

Vivek Vaghela

1,075
9
16

I tried this and still nothing, I have realised that the ':' after the first query seems to stop the second and naturally the third does not even get processed, if I remove those 2 I get this error 'There was an error SQLSTATE[42000]: Syntax error or access violation: 1064 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 'SELECT aid FROM sb_admins WHERE authid = '123456' INSERT INTO sb_' at line 3 – easono Feb 17 '15 at 19:28

score 0 · Answer 2 · 2015-02-17T19:36:17.143

You're missing a semi-colon after your second query, but that's not your only problem.

Your SELECT will select the value, but not store it. When you get to your third query there will be no value for aid, so your query will fail in some way there.

You can combine your second and third queries to avoid the necessity to store an interim result. Use INSERT...SELECT

INSERT INTO sb_admins_servers_groups (admin_id, group_id, srv_group_id, server_id) 
    SELECT aid,  "5", "4", "-1" FROM sb_admins WHERE authid = :ID

MySQL reference: http://dev.mysql.com/doc/refman/5.5/en/insert-select.html

However, I don't think you'll get the multiple-query form to work with prepared statements unless you turn on the PDO emulation first with

$s->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);

To be honest, I think you should just prepare and execute two separate queries.

I can see what your saying I think from this, PDO emulation is not something I have tried knowingly. I will get these a go and see what happens, this query runs from a foreach so I'm not sure running it as a second query would be 100% accurate as the data may vary the next run. I will have another play. Thank you — easono, Feb 17 '15 at 19:41

Multiple MySQL queries in one statement

2 Answers2