PHP Classes

File: example.php

Recommend this page to a friend!
  Classes of Sung-Whi Kim   MySQLi Prepared Statements   example.php   Download  
File: example.php
Role: Example script
Content type: text/plain
Description: Sample script showing usage
Class: MySQLi Prepared Statements
Execute MySQL queries using prepared statements
Author: By
Last change: Initial upload
Date: 9 years ago
Size: 3,269 bytes
 

Contents

Class file image Download
<?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>';