PHP Classes

MySQLi Prepared Statements: Execute MySQL queries using prepared statements

Recommend this page to a friend!
  Info   View files Example   View files View files (3)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not yet rated by the usersTotal: 193 This week: 1All time: 8,544 This week: 560Up
Version License PHP version Categories
mysqli-prepared-stmt 1.0.0GNU General Publi...5.3PHP 5, Databases
Description 

Author

This class can execute MySQL queries using prepared statements.

It can connect to a given MySQL database using the MySQLi extension.

The class can execute plain SQL queries or queries with parameters using prepared statements to pass the parameter values.

The parameter values are passed along the SQL query string in a single call as an array that defines either the parameter value and its type.

Queries that retrieve result sets return the results as arrays. The range of result set rows to be returned can be limited.

Picture of Sung-Whi Kim
Name: Sung-Whi Kim <contact>
Classes: 1 package by
Country: France France
Age: 56
All time rank: 4152103 in France France
Week rank: 411 Up15 in France France Up

Recommendations

SQL injection safe DB layer
I want DB layer that have SQL Injection safe structure

Example

<?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>';







  Files folder image Files  
File Role Description
Accessible without login Plain text file db_test.sql Data The sql script to create the sample databse
Plain text file db.php Class The class file
Accessible without login Plain text file example.php Example Sample script showing usage

 Version Control Unique User Downloads Download Rankings  
 0%
Total:193
This week:1
All time:8,544
This week:560Up