how to combine query for multiple able to save data

Question

can it be combine into 1 query?

this is the query that im trying to combine? or is there a better way to relate these to table?

$insert_row = $mysqli->query("INSERT INTO orderlist 
(TransactionID,ItemName,ItemNumber, ItemAmount,ItemQTY)
VALUES ('$transactionID','$itemname','$itemnumber', $ItemTotalPrice,'$itemqty')");

$insert_row1 = $mysqli->query("INSERT INTO order 
(BuyerName,BuyerEmail,TransactionID)
VALUES ('$buyerName','$buyerEmail','$transactionID')");

when i run these both only one query is functional, so what im trying to do is to make them both works.

im open to any suggestion

sidenote about `order`, it's a MySQL reserved word and requires special attention. — Funk Forty Niner, Aug 26 '15 at 02:52
sure you can use `order`, but you need to wrap it in ticks `\`` otherwise it will throw you a syntax error — Funk Forty Niner, Aug 26 '15 at 02:54

score 1 · Accepted Answer · edited May 23 '17 at 11:52

The reason why your second query isn't working is because of the use of order and not escaping it; it is a MySQL reserved word:

https://dev.mysql.com/doc/refman/5.5/en/keywords.html

Sidenote: ORDER is used when performing a SELECT... ORDER BY...

https://dev.mysql.com/doc/refman/5.0/en/select.html

Checking for errors would have shown you the syntax error such as:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax near 'order

http://php.net/manual/en/mysqli.error.php

Therefore, wrap it in ticks:

$insert_row1 = $mysqli->query("INSERT INTO `order` ...

or rename your table to something other than a reserved word, say orders for example.

If you wish to combine both queries, you can use multi_query()

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

Example from the manual:

<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

$sql = "SELECT COUNT(*) AS _num FROM test; ";
$sql.= "INSERT INTO test(id) VALUES (1); ";
$sql.= "SELECT COUNT(*) AS _num FROM test; ";

if (!$mysqli->multi_query($sql)) {
    echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

do {
    if ($res = $mysqli->store_result()) {
        var_dump($res->fetch_all(MYSQLI_ASSOC));
        $res->free();
    }
} while ($mysqli->more_results() && $mysqli->next_result());
?>

I also need to point out that your present code may be open to SQL injection since I do not know if you are escaping your data.

If not, then use prepared statements, or PDO with prepared statements, they're much safer.

score 0 · Answer 2 · answered Aug 26 '15 at 08:33

try to add IF statement.

if ($insert_row = $mysqli->query("INSERT INTO orderlist(TransactionID,ItemName,ItemNumber, ItemAmount,ItemQTY)VALUES ('$transactionID','$itemname','$itemnumber', $ItemTotalPrice,'$itemqty')"));
{
$insert_row1 = $mysqli->query("INSERT INTO order (BuyerName,BuyerEmail,TransactionID) VALUES ('$buyerName','$buyerEmail','$transactionID')");
}

how to combine query for multiple able to save data

2 Answers2