PHP Classes

File: DB_examples.php

Recommend this page to a friend!
  Classes of Jeff Williams   Ultimate MySQL PDO Database Class   DB_examples.php   Download  
File: DB_examples.php
Role: Example script
Content type: text/plain
Description: Examples
Class: Ultimate MySQL PDO Database Class
Access MySQL databases using the PDO extension
Author: By
Last change:
Date: 5 years ago
Size: 2,916 bytes


Class file image Download
// Include the database utility functions

/* -- SQL to create the test table used in these examples
CREATE TABLE `test_table` (
  `name` varchar(35) NOT NULL,
  `age` int(10) unsigned DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)

// Connect to the database
$error = DB::Connect('username', 'password', 'database', 'hostname');

// If there was an error, stop and display it
if ($error) die($error);

// Execute a SQL query and return whether it was successful or not
// (FYI: You can also use DB::Safe() to return a safe quoted string for SQL)
$sql = "INSERT INTO test_table (name, age, active) VALUES ('Sophia', 20, true)";
$success = DB::Execute($sql);

// Execute a SQL query with placeholders (better because it stops SQL Injection hacks)
$sql = 'INSERT INTO test_table (name, age, active) VALUES (:name, :age, :active)';
$values = array('name' => 'Lucas', 'age' => 45, 'active' => true);
$success = DB::Execute($sql, $values);

// Execute the same SQL statement but only in debug mode
// In debug mode, the record will not be saved
$success = DB::Execute($sql, $values, true);

// Execute a SQL query to return an array containing all rows
$sql = 'SELECT * FROM test_table';
$rows = DB::Query($sql);

// Show the array

// Execute a SQL query using placeholders; this will return an array with all rows
$sql = 'SELECT id, name, age FROM test_table WHERE active = :active';
$values = array('active' => true);
$rows = DB::Query($sql, $values);

// Execute the same query in debug mode
$rows = DB::Query($sql, $values, true);

// Let do the same query without using SQL
$columns = array('id', 'name', 'age');
$where = array('active' => true);
$rows = DB::Select('test_table', $columns, $where);

// We can make more complex where clauses in the Select, Update, and Delete methods
$columns = array('id', 'name', 'age');
$where = array(
'active IS NOT NULL',
'id > 10',
'UPPER(name) LIKE %JEN%'
$rows = DB::Select('test_table', $columns, $where);

// Let's sort by ID and run it in debug mode
$rows = DB::Select('test_table', $columns, $where, 'id', true);

// Grab one value - get the name of the person in the record with ID 1
$value = DB::SelectValue('test_table', 'name', array('id' => 1));

// Insert a new record
$values = array('name' => 'Riley', 'age' => 30, 'active' => false);
$success = DB::Insert('test_table', $values);

// Try it in debug mode
$success = DB::Insert('test_table', $values, true);

// Update an existing record
$update = array('age' => 35);
$where = array('name' => 'Riley');
$success = DB::Update('test_table', $update, $where);

// Delete records
$where = array('active' => false);
$success = DB::Delete('test_table', $where);