<?php
/*
* This script provides an example to use the db.php class
*
* Created By: Sung-Whi Kim
*
* Created Date: 23/6/2015
*
*/
/* SET THE DATABASE CONNECTION PARAMETERS AS CONSTANTS */
/* THESE CAN BE SET IN A COMMON INCLUDE FILE */
const DB_HOST = 'localhost'; //such as localhost, dbserver.host.net, 10.10.1.1, etc
const DB_USER = 'db_test';
const DB_PASSWORD = 'db_test';
const DB_NAME = 'db_test';
//include the class
include_once('db.php');
/* CREATE A NEW INSTANCE OF THE CLASS */
/* IT CAN ALSO BE USED AS A PROTOTYPE TO EXTEND ANOTHER CLASS */
/* I USE IT FOR ANY CLASSES THAT REQUIRE A DATABASE CONNECTION */
$db = new db();
/* -------------------------------------------------------------------------------------------------------------
* A SIMPLE QUERY WITHOUT A RECORD SET. IT WILL RETURN A TRUE OR FALSE DEPENDING ON THE OUTCOME.
*/
$sql = "INSERT INTO mytable SET col1 = 'my value'";
$result = $db->query($sql);
echo '----- Simple sql query result = ' . $result . ' ------<br>';
echo '<br><br>';
/* -------------------------------------------------------------------------------------------------------------
* A SIMPLE QUERY WITH A RECORD SET. IT WILL RETURN AN ASSOCIATIVE ARRAY WITH THE DATA.
*/
$sql = "SELECT * FROM mytable";
$data = $db->getRecordSet($sql);
echo '----- Simple recordset ------<br>';
print_r($data);
echo '<br><br>';
/* -------------------------------------------------------------------------------------------------------------
* AN EXAMPLE WITH THE USE OF THE ESCAPE FUNCTION. THE OPTIONAL PARAMETER SET TO TRUE BY DEFAULT RETURNS
* AN ESCAPED STRING ALREADY SINGLE QUOTED FOR USE IN A QUERY.
*/
$test_string = 'my value';
$sql = "SELECT * FROM mytable WHERE col1 = " . $db->escape($test_string, true);
$data = $db->getRecordSet($sql);
echo '----- Simple recordset with escape function ------<br>';
print_r($data);
echo '<br><br>';
/* -------------------------------------------------------------------------------------------------------------
* AN EXAMPLE WITH THE USE OF THE getRowLimitSql() FUNCTION. THIS WILL APPEND A ROW LIMIT SQL STATEMENT TO THE
* END OF YOUR QUERY. THIS IS FOR USE IN PAGINATING DATA.
*/
//set variables
$number_of_rows = 30;
$page_number = 1;
//set initial sql statement
$sql = "SELECT * FROM mytable";
//add row limits for use in pagination of data
$sql .= $db->getRowLimitSql($number_of_rows, $page_number);
//run query
$data = $db->getRecordSet($sql);
echo '----- Simple recordset with row limits ------<br>';
print_r($data);
echo '<br><br>';
/* -------------------------------------------------------------------------------------------------------------
* AN EXAMPLE WITH THE USE OF THE PREPARED STATEMENTS. IT TAKES IN THE PARAMETERS AS AN ARRAY. BELOW IS AN EXAMPLE
* $parameters[] = Array( 'type' => 's', 'value' => 'my value' )
*
*/
//set sql statement
$sql = 'SELECT * FROM mytable WHERE col1 = ?';
//set input parameter
$value = 'my value';
$parameters[] = Array( 'type' => 's',
'value' => $db->escape($value, false)
);
//run prepared statement and return record set
$data = $db->getRecordSetPreparedStatement($sql, $parameters);
echo '----- Prepared statement recordset ------<br>';
print_r($data);
echo '<br><br>';
|
Download
