An extremely simple MySQLi query class for prepared statements.
To initialize a new instance of the Query
class, simply pass in your mysqli
object:
$mysqli = new mysqli('127.0.0.1', 'my_user', 'my_password', 'my_db');
if ($mysqli->connect_error) {
die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}
$query = new Query($mysqli);
For more information about creating a new connection, see: https://www.php.net/manual/en/mysqli.construct.php
This class is made up of the following methods:
select()
- Simple select statementsinsert()
- Simple insert statementsupdate()
- Simple update statementsdelete()
- Simple delete statementsquery()
- Simple queries, acceptingbind_param
parameter types
The first 4 methods automatically convert their parameter values to their corresponding data types for the bind_param
method.
The supported types for this class are converted by using the gettype()
value of the variable, into the bind_param
type using the table below:
Type | Character | Description |
---|---|---|
string | s | corresponding variable has type string |
null | s | corresponding variable has type string |
integer | i | corresponding variable has type integer |
float | d | corresponding variable has type double |
For more information about bind_param
types, see: https://www.php.net/manual/en/mysqli-stmt.bind-param.php#refsect1-mysqli-stmt.bind-param-parameters
Out of the CRUD methods, the select()
method is the only method that returns an array - the data to be returned. The rest simply return a boolean based on whether the query was able to successfully insert, update, or delete.
A simple SELECT
statement, with no parameters:
$data = $query->select('SELECT * FROM `pets`');
return $data;
A simple SELECT
statement, with parameters:
$params = [
'M',
];
$data = $query->select('SELECT * FROM `pets` WHERE `sex` = ?', $params);
return $data;
A simple INSERT
statement:
$params = [
'Thumper',
'Sabrina',
'Mini Lop',
'M',
'2020-08-22',
];
$return = $query->insert('INSERT INTO `pets` VALUES (?, ?, ?, ?, ?)');
return $return;
A simple UPDATE
statement:
$params = [
'Oliver',
'Thumper',
];
$return = $query->insert('UPDATE `pets` SET `owner` = ? WHERE `name` = ?');
return $return;
A simple DELETE
statement:
$params = [
'Thumper',
];
$return = $query->insert('DELETE FROM `pets` WHERE `name` = ?', $params);
return $return;
For more advanced usages, you can use the query()
method directly:
$types = 'ss';
$params = [
'2020-01-01',
'2020-12-31',
];
$data = $query->query('SELECT * FROM `pets` WHERE `death` BETWEEN ? AND ?', $types, $params);
return $data;
You may want to use this method to gain more control over the bind_param
types, without having this class attempt to convert them for you.
The above examples are given as an example. If you would like to use this exact table structure to test these examples directly, you can create this yourself by using the following CREATE TABLE
statement:
CREATE TABLE `pets` (
`name` VARCHAR(20),
`owner` VARCHAR(20),
`species` VARCHAR(20),
`sex` CHAR(1),
`birth` DATE,
`death` DATE
);
Which would look something like this, using a DESCRIBE
statement:
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
Primary keys, indexes and character sets have been removed for simplicity. The table structure itself was taken directly from the MySQL 'Creating Tables' documentation.