How to use SET and INSERT in one php variable?

Question

This is not duplicated question, since I am asking how to use SET and INSERT in one PHP variable, there no any questions about AUTO_INCREMENT...

I have below page:

<?php

function genWO(){
    $dbtype = "MySQL";
    $username = "user";
    $password = "pass";
    $hostname = "10.10.10.10";
    $dbname = "TABLES";
    //connection to the database
    $conn = new mysqli($hostname, $username, $password);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
}

    $insertNewWONum = "SET @MAX_WO = (SELECT max(WO_NUM) + 1 from GENERIC_TABLES.WO_NUMBERS); INSERT INTO GENERIC_TABLES.WO_NUMBERS (WO_NUM, WO_REQUESTOR) values (@MAX_WO, `test`)";

    if ($conn->query($insertNewWONum) === TRUE) {
        echo "New record created successfully". "<br>";
    } else {
        echo "Error: " . $insertNewWONum . "<br>" . $conn->error;
    }

    $getWONum = "SELECT LPAD(max(WO_NUM) ,6,0) as NEW_WO_NUM from GENERIC_TABLES.WO_NUMBERS";
    $result = $conn->query($getWONum);

    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo "New WO Number: " . $row["NEW_WO_NUM"]. "<br>";
        }
    } else {
        echo "0 results";
    }

    //close the connection
    $conn->close();
    }

    ?>

Since it is not allowed to use INSERT and SELECT for the same table in one query, I am trying to set variable and use it in INSERT query:

$insertNewWONum = "SET @MAX_WO = (SELECT max(WO_NUM) + 1 from GENERIC_TABLES.WO_NUMBERS); INSERT INTO GENERIC_TABLES.WO_NUMBERS (WO_NUM, WO_REQUESTOR) values (@MAX_WO, `test`)";

But it doesnt work, though it works fine if I am using this query in terminal.

Can anyone let me know how to achieve it please?

you sure don't want to do it the way you are. See what @e4c5 said — Drew, Oct 26 '16 at 05:44
Your **MySQL** is vulnerable.Learn to use [**`Prepared Statements`**](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in order to secure your code. — Umair Shah, Oct 26 '16 at 06:02
It is not duplicated question, I couldnt fine similar issue. — Roman, Oct 26 '16 at 08:18

score 0 · Accepted Answer · edited May 23 '17 at 12:16

Since it is not allowed to use INSERT and SELECT for the same table in one query

All issues with your approach aside of course you can use INSERT and SELECT in one statement

INSERT INTO WO_NUMBERS (WO_NUM, WO_REQUESTOR)
SELECT COALESCE(MAX(WO_NUM), 0) + 1, 'Test'
  FROM WO_NUMBERS;

Here is SQLFiddle demo.

Now what you need to realize is that your approach is unsafe for concurrent access. If this code is executed from two or more sessions simultaneously some or all of them may generate the same number effectively breaking your application.

Use AUTO_INCREMENT instead.

CREATE TABLE WO_NUMBERS
(
  WO_NUM INT PRIMARY KEY AUTO_INCREMENT,
  WO_REQUESTOR VARCHAR(32)
);

INSERT INTO WO_NUMBERS (WO_REQUESTOR) VALUES ('Test');

Here is SQLFiddle demo.

If for some reason you can't use AUTO_INCREMENT directly (and I honestly don't see why you couldn't) i.e. you need to add prefixes or augment the generated id/code in some way you can look this answer.

How to use SET and INSERT in one php variable?

1 Answers1