Question 20023 in Mysql Question posted in
The official MySQL documentation can be found here.

Combining two MySQL queries into a single query

I have a form that takes a CSV file and uploads it into a MySQL table. Each of the entries upon the upload features characters that I need to remove, as well as the head row. For example:

`| ="username" | ="URL" |`
`| ="john"  | ="url"   |`
`| ="mike"  | ="url1"  |`

At the moment, I use the following two queries via phpMyAdmin to update the entries and delete the first row:

update control set userName = REPLACE(REPLACE(REPLACE(userName,'"',''), '"',''), '=','');

DELETE FROM control WHERE userName = "Username"

Is there a way to combine the two queries into a single one with the goal of running the resulting query with a button click through PHP script?

Tags:

2

Answers


  1. 0

    You can run the two queries by combining them in to one variable like

    $sql = "DELETE FROM control WHERE userName = 'Username';
        UPDATE control SET userName = REPLACE(REPLACE(REPLACE(userName,'"',''),'"',''),'=','')";

if ($conn->multi_query($sql) === TRUE) {
    echo "Query executed successfully";
} else {
    echo "Error executing query: " . $conn->error;
}
    Login or Signup to reply.
  2. 0

    You cannot combine a DELETE query with an UPDATE query unfortunately.

    What you can do however is combine the queries inside a transaction so that they are run consecutively and are rolled back if anything goes wrong.

    START TRANSACTION;
UPDATE control SET userName = REPLACE(REPLACE(REPLACE(userName,'"',''), '"',''), '=','');
DELETE FROM control WHERE userName = "Username"
COMMIT;

    In terms of PHP this could look something like the following:

    $pdo = new PDO('some dsn string');
$pdo->beginTransaction();
$pdo->exec("UPDATE control SET userName = REPLACE(REPLACE(REPLACE(userName,'"',''), '"',''), '=','')");
$pdo->exec("DELETE FROM control WHERE userName = "Username"");
$pdo->commit();

    Above is using the PHP PDO library as an example.

    MySQL Transaction Documentation

    PHP PDO Transaction

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search