devcoens

2.2

- pdo_connect

$dsn = 'mysql:host=localhost;dbname=DBNAME';

$db = new PDO($dsn, 'USER', 'PASSWORD', array(

PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"

));

2.3 

- pdo_loop

$sql = "SELECT name, meaning, gender FROM names";

foreach ($db->query($sql) as $row) {

echo $row['name'];

echo $row['meaning'];

echo $row['gender'];

}

2.4

- pdo_fetch

$sql = "SELECT name, meaning, gender FROM names";

$result = $db->query($sql);

while ($row = $result->fetch()) {

echo $row[0];

echo $row['meaning'];

}

- pdo_fetchAll

$sql = "SELECT name, meaning, gender FROM names";

$result = $db->query($sql);

$all = $result->fetchAll(PDO::FETCH_NUM);

print_r($all);

- pdo_fetchColumn

$sql = "SELECT name, meaning, gender FROM names";

$result = $db->query($sql);

while($col = $result->fetchColumn(1)) {

echo $col;

}

2.5

- pdo_rowcount

$sql = "select count(*) FROM names";

$result = $db->query($sql);

$numrows = $result->fetchColumn();

$sql = "SELECT name, meaning, gender FROM names";

$result = $db->query($sql);

$numrows = $result->rowCount();

2.6

- pdo_checkrow

$row = $result->fetch();

if (!$row) {

echo 'No results found';

}

2.7 

- query_insert

$sql = 'INSERT INTO names (name, meaning, gender) VALUES ("William", "resolute guardian", "boy")';

$result = $db->query($sql);

echo $result->queryString; // ==> INSERT INTO names (name, meaning, gender) VALUES ("William", "resolute guardian", "boy")

- exec_delete

$sql = 'DELETE FROM names WHERE name = "William"';

$affected = $db->exec($sql);

- exec_insert

$sql = 'INSERT INTO names (name, meaning, gender) VALUES ("William", "resolute guardian", "boy")';

$affected = $db->exec($sql);

2.8

- pdo_error

try {

$sql = ".....";

$result = $db->query($sql);

$errorInfo = $db->errorInfo();

print_r($errorInfo);

} catch (Exception $e) {

echo $e->getMessage();

}

- pdo_exception

try {

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = ".....";

$result = $db->query($sql);

} catch (Exception $e) {

echo $e->getMessage();

}

2.9 

- pdo_quote

        $searchterm = '%' . $_GET['searchterm'] . '%';

        $sql = 'SELECT name, meaning FROM names WHERE name LIKE ' . $db->quote($searchterm);

        $result = $db->query($sql);

//------------------------------------------

3.2

- pdo_named

        $sql = 'SELECT make, yearmade, mileage, price, description

                FROM cars

                LEFT JOIN makes USING (make_id)

                WHERE make LIKE :make AND yearmade >= :yearmade AND price <= :price

                ORDER BY price';

        $stmt = $db->prepare($sql);

        $stmt->bindValue(':make', '%' . $_GET['make'] . '%');

        $stmt->bindParam(':yearmade', $_GET['yearmade'], PDO::PARAM_INT);

        $stmt->bindParam(':price', $_GET['price'], PDO::PARAM_INT);

        $stmt->execute();

        $errorInfo = $stmt->errorInfo();

$row = $stmt->fetch();

do {

echo $row['make'];

echo $row['yearmade'];

} while ($row = $stmt->fetch());

3.3

- pdo_anonymous

        $stmt = $db->prepare($sql);

        $stmt->bindValue(1, '%' . $_GET['make'] . '%');

        $stmt->bindParam(2, $_GET['yearmade'], PDO::PARAM_INT);

        $stmt->bindParam(3, $_GET['price'], PDO::PARAM_INT);

        $stmt->execute();

3.4

- pdo_named_execute

        $sql = 'SELECT make, yearmade, mileage, price, description

                FROM cars

                LEFT JOIN makes USING (make_id)

                WHERE make LIKE :make AND yearmade >= :yearmade AND price <= :price

                ORDER BY price';

        $stmt = $db->prepare($sql);

        $values = array(':price' => $_GET['price'],

                        ':make' => '%' . $_GET['make'] . '%',

                        ':yearmade' => $_GET['yearmade']);

        $stmt->execute($values);

- pdo_anonymous_execute

        $sql = 'SELECT make, yearmade, mileage, price, description

                FROM cars

                LEFT JOIN makes USING (make_id)

                WHERE make LIKE ? AND yearmade >= ? AND price <= ?

                ORDER BY price';

        $stmt = $db->prepare($sql);

        $stmt->execute(array('%' . $_GET['make'] . '%', $_GET['yearmade'], $_GET['price']));

3.5 

- pdo_output

        $sql = 'SELECT make, yearmade, mileage, price, description

                FROM cars

                LEFT JOIN makes USING (make_id)

                WHERE make LIKE :make AND yearmade >= :yearmade AND price <= :price

                ORDER BY price';

        $stmt = $db->prepare($sql);

        $stmt->bindValue(':make', '%' . $_GET['make'] . '%');

        $stmt->bindParam(':yearmade', $_GET['yearmade'], PDO::PARAM_INT);

        $stmt->bindParam(':price', $_GET['price'], PDO::PARAM_INT);

        $stmt->execute();

        $stmt->bindColumn('make', $make);

        $stmt->bindColumn(2, $year);

        $stmt->bindColumn('mileage', $miles);

        $stmt->bindColumn('price', $price);

        $stmt->bindColumn('description', $desc);

        $errorInfo = $stmt->errorInfo();

$stmt->fetch(PDO::FETCH_BOUND);

do {

echo $make;

echo $year;

} while ($stmt->fetch(PDO::FETCH_BOUND));

- pdo_transaction

$amount = 200;

$payer = 'John White';

$payee = 'Jane Brown';

$debit = 'UPDATE savings SET balance = balance - :amount WHERE name = :payer';

$credit = 'UPDATE savings SET balance = balance + :amount WHERE name = :payee';

$pay = $db->prepare($debit);

$pay->bindParam(':amount', $amount);

$pay->bindParam(':payer', $payer);

$receive = $db->prepare($credit);

$receive->bindParam(':amount', $amount);

$receive->bindParam(':payee', $payee);

$db->beginTransaction();

$pay->execute();

if (!$pay->rowCount()) {

$db->rollBack();

$error = "Transaction failed: could not update $payer's balance.";

} else {

$receive->execute();

if (!$receive->rowCount()) {

   $db->rollBack();

   $error = "Transaction failed: could not update $payee's balance.";

} else {

   $db->commit();

}

}

3.7

- pdo_check_balance

...........................................

// Transaction

$db->beginTransaction();

$pay->execute();

if (!$pay->rowCount()) {

$db->rollBack();

$error = "Transaction failed: could not update $payer's balance.";

} else {

// Check the remaining balance in the payer's account

$check->execute();

$bal = $check->fetchColumn();

$check->closeCursor();

// Roll back the transaction if the balance is negative

if ($bal < 0) {

$db->rollBack();

$error = "Transaction failed: insufficient funds in $payer's account.";

} else {

$receive->execute();

if (!$receive->rowCount()) {

$db->rollBack();

$error = "Transaction failed: could not update $payee's balance.";

} else {

$db->commit();

}

}

}

//====================================

Car.php

4.1

- pdo_array

$sql = 'SELECT name, meaning FROM names';

$result = $db->query($sql);

$names = $result->fetchAll(PDO::FETCH_KEY_PAIR);

/*

==>

Array

(

   [Alice] => noble, light

   [Aubrey] => ruler of elves

   [Harry] => power, ruler

)

*/

4.2

- pdo_fetch_into

$car_id = 18;

$car = new Car($car_id); //<=== DataObject. 첨부파일. 

$sql = 'SELECT * FROM cars LEFT JOIN makes USING (make_id) WHERE car_id = ?';

$stmt = $db->prepare($sql);

$stmt->execute(array($car_id));

$stmt->setFetchMode(PDO::FETCH_INTO, $car);

$stmt->fetch();

// Display the car object

echo $car;

4.3

- pdo_fetch_class

$car_id = 25;

$sql = 'SELECT * FROM cars LEFT JOIN makes USING (make_id) WHERE car_id = ?';

$stmt = $db->prepare($sql);

$stmt->execute(array($car_id));

$stmt->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, 'Car', array($car_id));

$car = $stmt->fetch();

echo $car;

4.4

- pdo_reuse

$sql = 'SELECT name, meaning FROM names ORDER BY name';

$result = $db->query($sql);

$names = $result->fetchAll(PDO::FETCH_ASSOC);

foreach ($names as $name) {

echo $name['name'];

}

reset($names);

foreach ($names as $name) {

echo $name['meaning'];

}