to determine which code is suitable for reuse in your own applications.
Copyright 2013 Marc J. Rochkind. All rights reserved. May be copied and used
under the BSD-type license at http://basepath.com/aup/copyright.htm.
*/
define('DB_HOST', 'localhost');
define('DB_PORT', '3306');
define('DB_NAME', 'mydb');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '...');
try {
$dsn = 'mysql:host=' . DB_HOST . ';port=' . DB_PORT . ';dbname=' . DB_NAME;
$pdo = new PDO($dsn, DB_USERNAME, DB_PASSWORD);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
add_triggers($pdo, 'manager', "\n if office not in\n ('DALLAS', 'BOSTON', 'PARIS', 'TOKYO') then\n signal SQLSTATE value 'CK001'\n set MESSAGE_TEXT = 'Invalid OFFICE value.';\n end if;\n ");
$pdo->beginTransaction();
// ... several SQL statements ...
$pdo->commit();
} catch (PDOException $e) {
if (isset($pdo) && $pdo->inTransaction()) {
$pdo->rollBack();
}
die(htmlspecialchars($e->getMessage()));
}
function add_triggers($pdo, $table, $sql)
{
$stmt = $pdo->prepare('select column_name, column_type
from information_schema.columns
where table_schema = :dbname and table_name = :table');
$stmt->execute(array('dbname' => DB_NAME, 'table' => $table));
No technical support is available for any of this source code. In general,
you must modify and test this code before incorporating it into your programs.
Warning: Some code contains mistakes or deliberately incorrect coding for the
purpose of serving as an example for the book. Please read the book carefully
to determine which code is suitable for reuse in your own applications.
Copyright 2013 Marc J. Rochkind. All rights reserved. May be copied and used
under the BSD-type license at http://basepath.com/aup/copyright.htm.
*/
require_once 'lib/common.php';
try {
$db = new DbAccess();
$pdo = $db->getPDO();
add_triggers($pdo, 'user', "\n if length(trim(userid)) = 0 then\n signal SQLSTATE value 'CK001'\n set MESSAGE_TEXT = 'User ID is required.@userid';\n end if;\n if length(trim(phone)) = 0 then\n signal SQLSTATE value 'CK001'\n set MESSAGE_TEXT = 'Phone is required.@phone';\n end if;\n if email not like '%_@__%.__%' then\n signal SQLSTATE value 'CK001'\n set MESSAGE_TEXT = 'Email is missing or invalid.@email';\n end if;\n if length(trim(last)) = 0 then\n signal SQLSTATE value 'CK001'\n set MESSAGE_TEXT = 'Last Name is required.@last';\n end if;\n if length(trim(phone_method)) = 0 then\n signal SQLSTATE value 'CK001'\n set MESSAGE_TEXT = 'SMS/Voice is required.@phone_method';\n end if;\n ");
} catch (PDOException $e) {
die(htmlspecialchars($e->getMessage()));
}
function add_triggers($pdo, $table, $sql)
{
$stmt = $pdo->prepare('select column_name, column_type
from information_schema.columns
where table_schema = :dbname and table_name = :table');
$stmt->execute(array('dbname' => DB_NAME, 'table' => $table));
$cols = $parms = '';
while ($row = $stmt->fetch()) {
$cols .= ", new.{$row['column_name']}";
$parms .= ", {$row['column_name']} {$row['column_type']}";
}
$cols = substr($cols, 2);
