PDA

View Full Version : Working with PDO (PHP Data Objects)


davidj
07-28-2010, 03:08 PM
Here is a nice little example of working with PDO and MYSQL

PDO adds an abstraction layer so you could happily change your database to another by simply changing the driver. All your database functions and calls which use the PDO library will still work

Example outputs a multi dimensional array of results.

<?php

$user = 'user';
$pass = 'pass';
$database = 'database';

//try catch throw
try {

// create PDO Object
$dbh = new PDO('mysql:host=localhost;dbname='.$database, $user, $pass);

// define SQL
$sql = 'SELECT * from users';

$result = array();

// loop through result and create a multi d array
foreach($dbh->query($sql) as $row) {

$result[] = $row;
}

// dont need the object any more
$dbh = null;

// catch errors and handle them
} catch (PDOException $e) {

print "Error!: " . $e->getMessage() . "<br/>";

die();
}

// output result
echo "<pre>";
print_r($result);
echo "</pre>";

?>

davidj
07-29-2010, 01:16 PM
Here is another example

It automatically cleans the form values so protects against SQL Injection




<?php

// I have used constants do define my connection variables this time as this is best practice.

define(USER,'username');
define(PASS,'password');
define(DB,'database');

$name = $_POST['uname'];
$pass = $_POST['pword'];

//try catch throw
try {

// create PDO Object
$dbh = new PDO('mysql:host=localhost;dbname='.DB, USER, PASS);

// define SQL. use prepare method and place holders. Use limit to stop the query once 1 record found
$query = $dbh->prepare("SELECT * from users where name=:name and password=:pass limit 1");

// bind param to place holder in SQL
$query->bindParam(":name", $name);
$query->bindParam(":pass", $pass);

// execute query here
$query->execute();

// define array
$result = array();

// loop through result and create a multi d assoc array
while($row = $query->fetch(PDO::FETCH_ASSOC)) {

$result[] = $row;
}

// close connection (this is done automatically anyway)
$dbh = null;

// catch errors and handle them
} catch (PDOException $e) {

print "Error!: " . $e->getMessage() . "<br/>";

die();
}



// output result. Debug method only
echo "<pre>";
print_r($result);
echo "</pre>";

?> Awesome!

You all should use this method

davidj
07-29-2010, 02:03 PM
Another example which does away with the loop

<?php

define(USER,'username');
define(PASS,'password');
define(DB,'database');

//try catch throw
try {

// create PDO Object
$dbh = new PDO('mysql:host=localhost;dbname='.DB, USER, PASS);

// define SQL. use prepare method.
$query = $dbh->prepare("SELECT * from users ");

// execute query
$query->execute();

// get results back in a multi d array without using a loop
$result = $query->fetchAll();

// catch errors and handle them
} catch (PDOException $e) {

print "Error!: " . $e->getMessage() . "<br/>";

die();
}

// output results
echo "<pre>";
print_r($result);
echo "</pre>";

?>