PHP Classes

MySQLi Prepared Statements: Execute MySQL queries using prepared statements

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

Author

Sung-Whi Kim

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

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 (3)  
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

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
Downloadmysqli-prepared-stmt-2015-06-24.zip 3KB
Downloadmysqli-prepared-stmt-2015-06-24.tar.gz 3KB
Install with ComposerInstall with Composer
 Version Control Unique User Downloads Download Rankings  
 0%
Total:193
This week:0
All time:8,556
This week:206Up

  Applications that use this package  
No pages of applications that use this class were specified.

Add link image If you know an application of this package, send a message to the author to add a link here.