PHP PDO - setAttribute, beginTransaction and commit

Php-mysql Course

• beginTransaction and commit

About try ... catch()

When it is created a PDO object with a connection to a database, in case of an error will throw the PDOException. If the error is not catched with try ... catch() PHP will stop the execution of the script.
PDOException is an extension of the PHP Exception class, that can "catch" the errors.
With try ... catch(), besides the fact that the error is taken and the script can continue its execution, it can also personalize the error message which will be displayed.
Syntax:

try {
  // ... PHP instructions
}
catch(PDOException $e) {
  echo 'Custom Error Message';

  // Output the error code and the error message
  echo $e->getCode(). '-'. $e->getMessage();
}

- $e - is the object that will store the error detected by PHP.
- getCode() - returns the error code.
- getMessage() returns the error message.
If these methods are not added, it can be displayed only a custom message.

setAttribute

The setAttribute() method can be used to set various attributes to the PDO object that handles the connection to database, including how to report the errors catched with "try ... catch()".
Syntax:

$PDOobject->setAttribute(ATTRIBUTE, OPTION)

- ATTRIBUTE - represents the attribute that will be set.
- OPTION - is the option /constant set for that attribute:

• PDO::ATTR_CASE - Force column names to a specific case, with one of these constants:
  • PDO::CASE_LOWER - Force column names to lower case.
  • PDO::CASE_UPPER - Force column names to upper case.
  • PDO::CASE_NATURAL - Leave column names as returned by the database driver.
  
  
• PDO::ATTR_ERRMODE - Specify how to report the errors, with one of the following constants:
  • PDO::ERRMODE_SILENT - Just set error codes, ignore the text error returned by PHP.
  • PDO::ERRMODE_WARNING - Raise E_WARNING.
  • PDO::ERRMODE_EXCEPTION - Throw exceptions, returned by PDOException.

• Here's some examples with setAttribute(). It will be used the same "sites" table, created in the previous lessons.

- The next example sets the PDO::ATTR_CASE attribute with the PDO::CASE_UPPER option.

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Set the column names to be returned uppercase
  $conn->setAttribute(PDO::ATTR_CASE, PDO::CASE_UPPER);

  // Select the first row
  $sql = "SELECT * FROM `sites` LIMIT 1";
  $result = $conn->query($sql)->fetch(PDO::FETCH_ASSOC);      // Execute query and fetch with FETCH_ASSOC

  // If the SQL query is succesfully performed ($result not false)
  if($result !== false) {
    // Traverse the result set and output the column names
    foreach($result as $col=>$row) {
      echo ' - '. $col;
    }
  }

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}

- This script will display the column names in uppercase:
- The next example uses the setAttribute() to output the errors in the standard mode returned by PHP. It sets the PDO::ATTR_ERRMODE with the PDO::ERRMODE_WARNING option. To demonstrate the result, it is performed an SQL SELECT with a column that not exist in the "sites" table.

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);

  // Sets to handle the errors in the PHP standard mode (E_WARNING)
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Select the first row
  $sql = "SELECT `nocolumn` FROM `sites` LIMIT 1";
  $result = $conn->query($sql);               // Executa interogarea

  // Traverse the result set and output data in the 'nocolumn'
  foreach($result as $row) {
    echo $row['nocolumn'];
  }

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

- Because "nocolumn" not exist, the code above will output this error: - If you replace the ERRMODE_WARNING option with ERRMODE_EXCEPTION, the error message will be:

beginTransaction and commit

The beginTransaction() method allows to write SQL statements without to be sent to MySQL server, it is used together with commit().
beginTransaction() stops the execution of any query to the database until the commit() method is called, in that moment will be executed all the SQL queries added between these two methods.
The advantage of this technique is that it can be written several sets of SQL queries, that are "pending", then, when the commit() method is called, all that SQL queries will be executed.

In the next example it is used beginTransaction(), and three SQL commands: UPDATE (to modify data in the row with id=3), INSERT (to add a new row), and SELECT (using the last inserted "id", auto-created by the INSERT). All these instructions will be executed when the commit() method is called (see also the comments in the code).

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);

  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);       // Sets exception mode for errors
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  $conn->beginTransaction();        // Start writting the SQL commands

  // 1. Update the columns "name" and "link", in rows with id=3
  $conn->exec("UPDATE `sites` SET `name`='Spanish Course', `link`='marplo.net/spaniola' WHERE `id`=3");
  // 2. Add a new row
  $conn->exec("INSERT INTO `sites` (`name`, `category`, `link`) VALUES ('JavaScript', 'programming', 'coursesweb.net/javascript')");
  $last_id = $conn->lastInsertId();            // Get the auto-inserted id
  // 3. Selects the rows with id lower than $last_id
  $result = $conn->query("SELECT `name`, `link` FROM `sites` WHERE `id`<'$last_id'");

  $conn->commit();        // Determine the execution of all SQL queries


  // If the SQL select is succesfully performed ($result not false)
  if($result !== false) {
    echo 'Last inserted id: '. $last_id. '<br />';        // Displays the last inserted id

    // Traverse the result set and shows the data from each row
    foreach($result as $row) {
      echo $row['name']. ' - '. $row['link']. '<br />';
    }
  }
  
  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

- As shown in the example above, this method is useful when you have to execute ​​several different queries to the database in the same script. Besides, improve the execution speed of the script, make it more efficient to work with multiple SQL queries.
Between SQL commands which are "pending", you can add various PHP instructions to influence the next command (such as SELECT here was defined according to the latest "id" created by the previous query).
- The script above will output this result:

If the ATTR_ERRMODE is set to ERRMODE_WARNING (with the setAttribute() method), and an error ocurs to one of the SQL instructions between beginTransaction() and commit(), the next SQL instructions after the query which has generated the error will not be executed.
But if it's not specified the ERRMODE_WARNING mode, the PHP continues the execution of the other queries too.

<img src="image.jpg" usemap="#map1">
<map name="map1">
  <area shape="rect" coords="9, 120, 56, 149" href="#">
  <area shape="rect" coords="100, 200, 156, 249" href="#">
</map>

#id {
  overflow: auto;
}

document.getElementById("id").onmouseover = function(){
  document.write("Have Good Life");
}

if(isset($_GET["id"])) {
  echo $_GET["id"];
}