query in try-catch block

[Jonob]

Does anyone actually run mysql queries through a php try-catch block?

 

I've come across code that looks something like:

 

mysql_query("BEGIN");
try
{
$sql = "some sql statement";
mysql_query( $sql);
}

catch (Exception $E)
{
mysql_query("ROLLBACK");
return false;
}

mysql_query("COMMIT");
return true;

 

Is this actually considered "good" practise? Or is a plain old mysql_query($sql) or die(mysql_error()); good enough?

[gevans]

In bigger applications it can be very useful....

 

Say for example you were running a script that would affect 3 db tables, the 2nd and 3rd using a foreign key to reference the first. If the first query fails you may no want to carry on with the 2nd and 3rd. When the queries grow in size and number the above method can be a lot easier than running a number of conditional statements, as well as undoing already completed queries if necessary.

 

Though in answer to your question, plain old mysql_query($sql) or die(mysql_error()); is perfectly fine when executing a query `here and there`.

[Jonob]

OK cool, thanks for the answer.

 

Lets say I put 5 (linked) queries into the try block, will it roll back all of them if an error is caught in only the last one? If so, then I can definitely see the use in this.

[gevans]

Have a look at this, very usefull class;

 

mysql_query doesnt support multiple queries, a way round this is to use innodb and transactions

 

this db class/function will accept an array of arrays of querys, it will auto check every line for affected rows in db, if one is 0 it will rollback and return false, else it will commit and return true, the call to the function is simple and is easy to read etc

----------

 

class MySQLDB

{

  private $connection;          // The MySQL database connection

 

  /* Class constructor */

  function MySQLDB(){

      /* Make connection to database */

      $this->connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());

      mysql_select_db(DB_NAME, $this->connection) or die(mysql_error());

  }

 

  /* Transactions functions */

 

  function begin(){

      $null = mysql_query("START TRANSACTION", $this->connection);

      return mysql_query("BEGIN", $this->connection);

  }

 

  function commit(){

      return mysql_query("COMMIT", $this->connection);

  }

 

  function rollback(){

      return mysql_query("ROLLBACK", $this->connection);

  }

 

  function transaction($q_array){

        $retval = 1;

 

      $this->begin();

 

        foreach($q_array as $qa){

            $result = mysql_query($qa['query'], $this->connection);

            if(mysql_affected_rows() == 0){ $retval = 0; }

        }

 

      if($retval == 0){

        $this->rollback();

        return false;

      }else{

        $this->commit();

        return true;

      }

  }

 

};

 

/* Create database connection object */

$database = new MySQLDB;

 

// then from anywhere else simply put the transaction queries in an array or arrays like this:

 

  function function(){

      global $database;

 

      $q = array (

        array("query" => "UPDATE table WHERE something = 'something'"),

        array("query" => "UPDATE table WHERE something_else = 'something_else'"),

        array("query" => "DELETE FROM table WHERE something_else2 = 'something_else2'"),

      );

 

      $database->transaction($q);

 

  }

[printf]

Nice example gevans!

[gevans]

It's from the mysql_query() page at php.net

 

 

[Jonob]

Very nice, thank you