CODEWITHSUNDEEP
phpmysqlinsert
PillBoxCharger
PillBoxCharger

Reputation: 193

Is there a way to test a mysql insert or update prior to actually inserting or updating?

My PHP/Mysql script works in stages:

Insert Customer info
  Get new customer's ID via mysqli_insert_id(blah)
Insert into ADDRESS using new customer's ID
Insert into PHONE using new customer's ID
Insert into CONTACTS using new customer's ID

My problem is: If something goes wrong with one of the latter insert statements, MYSQL stops and throws an error of course, but the initial Customer INSERT already fired and was successful. Now if we go back and correct the error in the inputs and try to do this all again, we will have multiple partial entries for this Customer.

So Question: Is there a way to test ALL the MySQL Inserts for errors and upon finding none, THEN go ahead and submit them all?

For Example:

Insert Customer info (check for would-be errors; don't actually insert)
  Get new customer's ID...
Insert into ADDRESS...(check for would-be errors; don't actually insert)
Insert into PHONE...(check for would-be errors; don't actually insert)
Insert into CONTACTS...(check for would-be errors; don't actually insert)

OK, now that there were no errors thrown and nothing was actually inserted, do all the pre-screened inserts!

Does this exist?

Upvotes: 6

Views: 3649

Answers (4)

Elias Van Ootegem
Elias Van Ootegem

Reputation: 76433

Yes, a transaction allows you to perform a series of queries, and commit all those changes at the end, or roll them back in case one of the queries failed.
If you're using PDO, the basic setup is this:

//optional
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try
{
    $pdo->beginTransaction();
    $stmt = $pdo->prepare();
    $pdo->query();
    //in short, all your queries here
    $pdo->commit();//commit changes to DB
}
catch (PDOException $e)
{
    $pdo->rollBack();//Nothing is changed in DB
}

Of course, transactions work best on transaction-safe storage engines (InnoDB, BerkeleyDB, ndbcluster). Since MySQL 5, transactions do work on MyISAM, but in order for them to be atomic, and thus safe to use, you'll have to lock and un-lock the table yourself.
See the mysql docs for more details

Upvotes: 2

Kai Qing
Kai Qing

Reputation: 18843

Here's a sample using mysqli to support my comment:

if (isset($_POST) && isset($_POST['whatever']))
{

    $con = new mysqli("localhost", "user", "pw", "db");
    // begin transaction by setting autocommit to false;
    $con->autocommit(FALSE);

    $stmt = $con->prepare("INSERT INTO `something` (`data`) VALUES (?)");
    $stmt->bind_param("s", $_POST['whatever']);
    $stmt->execute();
    if (!$stmt)
    {
        $con->rollback();
    }
    else
    {
        $con->commit();
    }

    $stmt->close();
}

You should know transactions require innodb tables. It helps to check the specs: http://dev.mysql.com/doc/refman/5.0/en/commit.html

See barranka's comments for more details

Upvotes: 0

exussum
exussum

Reputation: 18568

a transaction would be best for a roll back state. ifthats not possible prefix the statements with insert and it should let you know about any errors

Upvotes: 0

Alireza Fallah
Alireza Fallah

Reputation: 4607

Use MySQL transaction.

A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

Practically, you will club many SQL queries into a group and you will execute all of them together as part of a transaction.

Read this tutorial about MySQL transaction

Upvotes: 4

Related Questions