README

Maggsweb PDO

An easy-to-use PDO Database wrapper for PHP & MySQL

Table of Contents

Initialization
Query
Insert Records
Update Records
Delete Records

Initialization

To use this class, set your database connection constants, download and include 'MyPDO.php' into your project and instantiate a database connection.

define('DBHOST', '');  //eg: 127.0.0.1
define('DBUSER', '');  //eg: root
define('DBNAME', '');  //eg: admin
define('DBPASS', '');  //eg: password

require_once ('MyPDO.php');

$db = new MyPDO(DBHOST, DBUSER, DBNAME, DBPASS);

Query

Run a query, any query..

To execute a string of SQL, pass the complete SQL string to ->query

$sql = "INSERT INTO `names` VALUES 
    (NULL, 'Joe',  'Bloggs'),
    (NULL, 'John', 'Bloggs'),
    (NULL, 'Jane', 'Bloggs');";

$db->query($sql);

$result = $db->execute();

Call ->execute to execute the query. This returns true|false;

Run a query and return the results

To return the results from ->query for use call ->fetchAll() for multiple rows, ->fetchRow for a single row or ->fetchOne for a single value. Results are returned as an Array of Objects an Object or a value. Optionally, passing 'Array' to the fetch functions will return results as a Multi-dimensional Array.

$sql = "SELECT * FROM `names`";

$db->query($sql);

$results = $db->fetchAll();             // Multiple rows
//$result  = $db->fetchRow();           // Single row
//$result  = $db->fetchRow('Array');    // Single row, returned as an array
//$result  = $db->fetchAll('Array');    // Multiple rows, returned as a multi-dimensional array
//$result  = $db->fetchOne();           // Single value

On success, $result will be an Object Array (fetchAll) or an Object (fetchRow) or a value (fetchOne)

On failure, call ->getError to display the SQL error message

Run a query using 'bound' params and return results

To bind parameters to a query, pass the column identifier and value to ->bind(). Repeat this for each bound parameter in order.

$sql = "SELECT * FROM `names` WHERE firstname = :firstname";

$db->query($sql);

$db->bind(':firstname', 'Chris');

$results = $db->fetchAll(); 

or

$results = $db->query($sql)->bind(':firstname', 'Chris')->fetchAll();

Query Results

On failure, call ->getError to display the SQL error message

if($results){
    foreach($results as $result){
        echo $result->{$column};
    }
} else {
    echo $db->getError();
}

Insert Records

Insert a record using 'bind' params

$table   = 'names';
$columns = array('firstname' => 'Fred', 'surname' => 'Bloggs');

$result = $db->insert($table,$columns);

Insert Results

if($result){
    echo $db->numRows() . ' records affected';
} else {
    echo $db->getError();
}

Last Insert ID

$id = $db->insertID();

Update Records

Update (all) records using 'bind' params

$table   = 'names';
$columns = array('firstname' => 'Fred', 'surname' => 'Bloggs');

$result = $db->update($table,$columns);

Update records using 'bind' params and 'where' string

$table   = 'names';
$columns = array('firstname' => 'Fred 2', 'surname' => 'Bloggs 2');
$where   = "firstname = 'Fred' AND surname = 'Bloggs'";  //'WHERE' is not needed, or spaces

$result = $db->update($table,$columns,$where);

Update specific records using 'bind' params and 'where'

$table   = 'names';
$columns = array('firstname' => 'Fred 2', 'surname' => 'Bloggs 2');
$where   = array('firstname' => 'Fred',   'surname' => 'Bloggs');

$result = $db->update($table,$columns,$where);

Update Results

if($result){
    echo $db->numRows() . ' records affected';
} else {
    echo $db->getError();
}

Delete Records

Delete records using a 'where' string

$table  = 'names';
$where  = "surname = 'Doe'";

$result = $db->delete($table,$where);

Delete records using a 'where' array

$table = 'names';
$where = array('surname' => 'Doe');

$result = $db->delete($table,$where);

Delete Results

if($result){
    echo $db->numRows() . ' records affected';
} else {
    echo $db->getError();
}

More examples are provided in 'examples.php'