Multiple SQL output

Question

My current echo $sql; returns two queries:

UPDATE 00001 SET discount=11, discount_price=166.43, discount_price=166.00 WHERE code=00001070170UPDATE 00001 SET discount=11, discount_price=166.43, discount_price=166.00 WHERE code=00001070171

I was wondering how I can separate them and add a ; at the end of each one. My $sql command follows:

$sql = ("UPDATE ".$row['sku']." SET discount=".$discount1.", discount_price=".$discount_price1.", discount_price_elite=".$discount_price." WHERE code=".$row['code']."");

And the exected output is:

UPDATE 00001 SET discount=11, discount_price=166.43, discount_price=166.00 WHERE code=00001070170;
UPDATE 00001 SET discount=11, discount_price=166.43, discount_price=166.00 WHERE code=00001070171

The mysqli plugin for PHP does not allow branched/combined statements. It only allows one statement at a time execution. You'll have to use parameterized prepared statements and just execute them with different bound paraemters. — clearshot66, Sep 17 '18 at 17:12
Google prepared statements in a loop, the top 5 all explain how to do this — clearshot66, Sep 17 '18 at 17:18
for example https://stackoverflow.com/questions/19106963/php-prepared-statements-and-transactions-in-a-loop — clearshot66, Sep 17 '18 at 17:19
@clearshot66 Technically your first statement is incorrect. Mysqli can execute combined statements, if combined correctly; but it is not secure. — user3783243, Sep 17 '18 at 17:26
Is the goal just to return SQL or do you actually need to execute this later? `str_replace('UPDATE', ';' . PHP_EOL . 'UPDATE', $string)` could do it, assuming `UPDATE` is never present as a value/column. `00001` as a table name is invalid unless encapsulated in backticks. — user3783243, Sep 17 '18 at 17:28
I just need to run the queries at this point. Its being echoing fine, so Im just looking for a command that will output each line of $result and execute them one by one @user3783243 . I was able to add the ; without str_replace. — JohnnyBratsoni, Sep 17 '18 at 17:37
That was for just outputting, not executing. Can you add a bit more to the question; how is `$sql` being generated (`for` loop, `while` on result object from another query, other), what does your DB look like? — user3783243, Sep 17 '18 at 17:41
Well, mysqli_query($mysqli,$sql); seems to be running my queries, however, it fails to stop. Any ideas? — JohnnyBratsoni, Sep 17 '18 at 17:46
I don't know what your code does aside from the 1 line you shared. Monitor your error logs.. or are you asking how to kill a running process? — user3783243, Sep 17 '18 at 17:55

score 1 · Accepted Answer · answered Sep 17 '18 at 18:45

Don't try to combine them into a single query, do them as separate queries:

foreach ($rows as $row) {
    $sql = "UPDATE ".$row['sku']." SET discount=".$discount1.", discount_price=".$discount_price1.", discount_price_elite=".$discount_price." WHERE code=".$row['code'];
    mysqli_query($mysqli, $sql);
}

BTW, it's a very strange database design to have a separate table for each SKU. There should normally be just a single products table, with an sku column.

Multiple SQL output

1 Answers1