CODEWITHSUNDEEP
phpmysqlsql
Axeem
Axeem

Reputation: 191

Reset a table using query in sql

can here is any reset query which reset the table after update.
I update a colum and then I want to reset it. 

 id      name
 1       azeem
 2       arfan

update the table. 

  update table set name = 'abc' where id = 1;
  update table set name = 'xyz' where id = 2;

id      name
1       abc
2       xyz

Update: 

  $sql = mysql_query("select * from table");
   while($row = mysql_fetch_array($sql)){
      echo $row['name'];
   }

After that I want to reset it. How can I do that.?

Upvotes: 0

Views: 1614

Answers (2)

S.Pols
S.Pols

Reputation: 3434

Yes you can, as sgeddes said, you need to use a transaction. When you use a transaction which is not committed you can rollback to the state before the query executed.

Try this:

//Start the transaction
mysql_query("START TRANSACTION");

//An update query
$yourUpdateQuery = mysql_query("UPDATE YOUR_TBL SET COLUMN = 'COLUMN_VALUE' WHERE COLUMN_ID = ID_VALUE");

//Some SQL select queries (note, that this do not have effect on your data, so this don't need to be in your transaction
$sql = mysql_query("select * from table");
while($row = mysql_fetch_array($sql))
{
    echo $row['name'];
}

//Some check here
if (1==1) {
    //Ok, let's finish it
    mysql_query("COMMIT");
} else {        
    //Not ok, rollback to the state before the transaction started.
    mysql_query("ROLLBACK");
}

Note that you and the above example are using mysql_* functions which are deprecated. You should use PDO instead of mysql_*. Here is a PDO example:

<?php

    //PDO Database connection (if you haven't done this)
    $db = new PDO('mysql:host=localhost;dbname=DATABASE_NAME', "USERNAME", "PASSWORD");

    //Start the transaction
    $db->beginTransaction();

    //An update query
    $db = $db->exec("UPDATE YOUR_TBL SET COLUMN = 'COLUMN_VALUE' WHERE COLUMN_ID = ID_VALUE");

    //Some SQL select queries (note, that this do not have effect on your data, so this don't need to be in your transaction
    $sql = 'select * from table';
    foreach($db->query($sql) as $row)
    {
        echo $row['name'];
    }

    //Some check here
    if (1==1) {
        //Ok, let's finish it
        $db->commit();
    } else {        
        //Not ok, rollback to the state before the transaction started.
        $db->rollBack();
    }   



?>

Upvotes: 1

AbsoluteƵER&#216;
AbsoluteƵER&#216;

Reputation: 7870

2 Ways.

If you're running a test server and want to restore back all of the info that you're changing, then you can make a backup and restore the table values after the update.

otherwise if you're talking about at the transaction level you can make a transaction and instead of committing, roll it back.

<?php
/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();

/* Change the database schema and data */
$sth = $dbh->exec("DROP TABLE fruit");
$sth = $dbh->exec("UPDATE dessert
    SET name = 'hamburger'");

/* Recognize mistake and roll back changes */
$dbh->rollBack();

/* Database connection is now back in autocommit mode */
?>

Upvotes: 2

Related Questions