CODEWITHSUNDEEP
phpmysqlsql
user3247335
user3247335

Reputation: 163

PHP MySQL - Move record to another table, Select, Insert, Delete

I have two tables with the same structure. Iam attempting to move an entire record from one table to another and then delete from the source table. If i execute the two statements seperately it works, i.e. select and Insert 2. Delete.

How could I combine these two sql statments so that both statements are within one, and can be esxecuted one after another. Here is mysql statement so far, Its not executing

$q="  INSERT INTO archived (SELECT * FROM table1 WHERE id='$id') ;

DELETE FROM table1 WHERE id='$id'; ";

Upvotes: 1

Views: 4818

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

You can execute multiple SQL statements using one SQL statement string by seperating them by semi colon and passing the final statement to mysqli_multi_query() function.

$con = new mysqli('localhost', 'user', 'pass', 'demo');
// username is user , password is pass ,connecting to the demo database on the localhost host:
if($con->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

$sql =   "INSERT INTO archived SELECT * FROM table1 WHERE id='$id';";
$sql .=  "DELETE FROM table1 WHERE id='$id'; "; 

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

You can do

function mysql_multiquery($queries,$conn)
{
    $queries = explode(";", $queries);
    foreach ($queries as $query)
        $query = mysql_query(trim($query),$conn);

}

Call function

mysql_multiquery($sql,$conn);

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

If you are looking to send only one command from php, write a stored procedure with the two queries. Then your php command executes the procedure.

Upvotes: 0

Related Questions