PHP PDO - prepare and execute

Php-mysql Course

• bindValue and bindParam

With the exec() and query() methods the SQL command is automatically executed.
There is another variant, too. With PDO it's posible to "prepare" a SQL command without send /execute it. This is accomplished with the prepare() method.
- The prepare() method takes as argument an SQL statement and returns a PDOStatement object. This object contains an execute() method that will execute the SQL statement when it is called.
- The execute() returns TRUE, or FALSE in case of error.

So, prepare() can prepare an SQL statement to be executed by the execute() method, using this syntax:

$sqlprep = $PDOconnection->prepare("SQL statement");
$sqlprep->execute(Array);

- The "SQL statement" - can be any valid SQL statement: INSERT, SELECT, UPDATE, DELETE.
- The Array argument from the execute() method - is optional. It's an array of values with as many elements as there are bound parameters in the SQL statement being executed, specified in the prepare().

The prepared SQL instruction can be used to be executed multiple times, with different values. You can understand from the examples below.

• The examples presented it this tutorial use the "sites" table, created in the previous lessons, containig these data:

| id | | name               | | category          | | link                         |
------------------------------------------------------------------------------------
| 1 | | Courses - Tutorials | | education         | | https://coursesweb.net           |
| 2 | | PHP-MySQL Course    | | programming       | | https://coursesweb.net/php-mysql |
| 3 | | English Courses     | | foreign languages | | marplo.net/engleza       |

Here is an example with prepare() and execute(). We prepare a SQL statement that selects rows in the "sites" table, according to the values added to "execute()".

<?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

  // Define the SQL statement that will be applied in prepare()
  $sql = "SELECT `name`, `link` FROM `sites` WHERE `id`= :id OR `category`= :category";
  $sqlprep = $conn->prepare($sql);        // Prepares and stores the SQL statement in $sqlprep

  // The array with values that must be added in the SQL instruction (for ':id', and ':category')
  $ar_val = array('id'=>2, 'category'=>'education');

  // If the prepared SQL is succesfully executed with execute()
  if($sqlprep->execute($ar_val)) {
    // gets and displays the data returned by MySQL
    while($row = $sqlprep->fetch()) echo $row['name'].' - '.$row['link'].'<br />';
  }

     /* Execute again the prepared SQL, with other values */
   echo 'The 2nd select<br />';

  // The array with values that must be added in the prepared SQL (for ':id', and ':category')
  $ar_val = array('id'=>8, 'category'=>'foreign languages');

  // Execute the SQL instruction
  if($sqlprep->execute($ar_val)) {
    // gets and displays the data returned by MySQL
    while($row = $sqlprep->fetch()) echo $row['name'].' - '.$row['link'].'<br />';
  }

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

- In this statement: $sql = "SELECT `name`, `link` FROM `sites` WHERE `id`= :id OR `category`= :category"; ,   ":id" and ":category" are parameter markers for the values added in the Array passed in the execute(). Each parameter marker is associated with the value of the Array element with the same key-name ('id' and 'category').
- You must include a unique parameter marker for each value you wish to pass in to the statement when you call the "execute()" method.

Due to this notation you can use the same prepared SQL command multiple times, with different values. This technique optimizes the performance of your application, and helps to prevent SQL injection because the "prepare()" method eliminate the need to manually quote the parameters.

The code above will display:

In the prepared SQL command you can replace the parameter marker (:name) with question mark (?) parameter. But in this case the Array with values passed in execute() must be a secvential array, 0-indexed. The values will be associated and added in their order.
        Example:
                $sql = "SELECT `name`, `link` FROM `sites` WHERE `id`=? OR `category`=?";
                $sqlprep = $conn->prepare($sql);
                $sqlprep->execute(array(2, 'programming'));

bindValue and bindParam

Another way to pass values to an SQL statement prepared with "prepare()" is by using the bindValue() or bindParam() method.
These methods bind a value to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement, they provide better control over the values that must ​​transmited. With these methods you can specify the data type and eaven the maximum number of characters for the value. They are applied to the PDOStatement object returned by "prepare()".
The difference between bindValue and bindParam is:
          - bindValue receives directly the values.
          - bindParam receives the values in variables, and optionally can be speciffied the maximum number of charracters that can be passed.

• Syntax for bindValue():

bindValue(prepMark, value, data_type);

- prepMark - parameter identifier. The name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter (starting with 1).
- value - the value to bind to the parameter.
- data_type - (optional) explicit data type for the parameter using the PDO::PARAM_* constants: PDO::PARAM_INT for integer, PDO::PARAM_STR for string.

Example:

 $sql = "SELECT `name`, `link` FROM `sites` WHERE `id`= :id OR `category`= :category";
 $sqlprep = $conn->prepare($sql);

 $sqlprep->bindValue(':id', 2, PDO::PARAM_INT);          // Adds the value 2 for "id" column

 // Adds "programming" for "category"
 $sqlprep->bindValue(':category', 'programming', PDO::PARAM_STR);

 $sqlprep->execute();          // executes the query

• Syntax for bindParam():

bindParam(prepMark, $var_val, data_type, length);

- prepMark - parameter identifier. The name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter (starting with 1).
- $var_val - the PHP variabile that contains the value to bind to the parameter.
- data_type - (optional) explicit data type for the parameter using the PDO::PARAM_* constants: PDO::PARAM_INT for integer, PDO::PARAM_STR for string.
- length - (optional) Length of the data type. The maximum number of characters that will be send.

Example:

 // define and prepare the SQL statement
 $sql = "SELECT `name`, `link` FROM `sites` WHERE `id`=? OR `category`=?";
 $sqlprep = $conn->prepare($sql);

 // define the variabile with the values that wil be added in the SQL statement
 $id = 2;
 $categ = 'programming';

 // Adds the value of $id variabile for the first ? (id)
 $sqlprep->bindParam(1, $id, PDO::PARAM_INT);

 // Adds the value of $categ variabile for the second ? (category)
 $sqlprep->bindParam(2, $categ, PDO::PARAM_STR, 11);

 $sqlprep->execute();          // executes the query

• Here's another example with these two methods, applied to the "sites" table. We add 2 rows in the table, one after another. The first row using bindValue(), and the second with bindParam().

<?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

  // Define and prepare an INSERT statement
  $sql = "INSERT INTO `sites` (`name`, `category`, `link`) VALUES (:name, :category, :link)";
  $sqlprep = $conn->prepare($sql);

  // Adds value with bindValue
  $sqlprep->bindValue(':name', 'Ajax Course', PDO::PARAM_STR);
  $sqlprep->bindValue(':category', 'programming', PDO::PARAM_STR);
  $sqlprep->bindValue(':link', 'coursesweb.net/ajax', PDO::PARAM_STR);

  // If the query is succesfully executed, output the value of the last insert id
  if($sqlprep->execute()) echo 'Succesfully added the row with id='. $conn->lastInsertId();

  // Define the variabiles with values to be added to bindParam
  $name = 'Flash Games';
  $category = 'games';
  $link = 'marplo.net/games';

  // Adds the variable to the SQL prepared statement
  $sqlprep->bindParam(':name', $name, PDO::PARAM_STR);
  $sqlprep->bindParam(':category', $category, PDO::PARAM_STR);
  $sqlprep->bindParam(':link', $link, PDO::PARAM_STR);

  // If the query is succesfully executed, output the value of the auto inserted id
  if($sqlprep->execute()) echo '<br/>Succesfully added the row with id='. $conn->lastInsertId();

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

- Result:
The execution of the SQL commands with prepare() ... execute() is often faster than query() / execute().
This formula is useful when you want to use multiple times the same SQL statement with different parameters.
Another advantage is that the data that must be transmitted to the MySQL server are more clearly separated from the SQL command, besides, the data are also automatically filtered. For example, if you use one of the "bind" methods to send the string "a' b' c", PDO include the string "a\' b\' c".

• If the SQL command contains the LIKE instruction with a parameter, the '%' character must be added to the value.
    Example:

               // incorect
$sqlprep = $conn->prepare("SELECT `column` FROM `table` WHERE `column` LIKE %:word%");
$sqlprep->bindValue(':word', 'value');

               // CORECT
$sqlprep = $conn->prepare("SELECT `column` FROM `table` WHERE `column` LIKE :word");
$sqlprep->bindValue(':word', '%value%');

- In the next tutorial you can learn about the methods: setAttribute(), beginTransaction(), and commit().

<ul>
 <li>http://coursesweb.net/html/</li>
 <li>http://coursesweb.net/css/</li>
</ul>

.some_class {
  display: list-item;
}

var obj = {
 "courses": ["php", "javascript", "ajax"]
};
var jsonstr = JSON.stringify(obj);
alert(jsonstr);    // {"courses":["php","javascript","ajax"]}

$strhtml = '<body><div id="dv1">CoursesWeb.net</div></body>';
$dochtml = new DOMDocument();
$dochtml->loadHTML($strhtml);
$elm = $dochtml->getElementById("dv1");
echo $elm->nodeValue;    // CoursesWeb.net