<?php
// Include the database utility functions
require_once('DB.php');
/* -- SQL to create the test table used in these examples
CREATE TABLE `test_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(35) NOT NULL,
`age` int(10) unsigned DEFAULT NULL,
`active` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
*/
// 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
print_r($rows);
// 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);
?>
|
Download
