Download the PHP package websitebeaver/simple-mysqli without Composer
On this page you can find all versions of the php package websitebeaver/simple-mysqli. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
Download websitebeaver/simple-mysqli
More information about websitebeaver/simple-mysqli
Files in websitebeaver/simple-mysqli
Package simple-mysqli
Short Description Wrapper class for MySQLi prepared statements
License MIT
Informations about the package simple-mysqli
Simple MySQLi - MySQLi Wrapper
Using MySQLi prepared statements is a great way to prevent against SQL injection, but it can start feeling tedious after a while. I thought this could be improved a little, which is why wanted to create an easy to use MySQLi database wrapper, while also ensuring that the SQL queries aren't broken up into proprietary syntactic sugar chaining. This way, so you can have extremely concise code, while still keeping your SQL syntax intact. The purpose of this class is to make using plain SQL queries as enjoyable as possible, without being an ORM. In a lot of ways, I modeled this class after what I believe the general syntax for vanilla MySQLi/PDO should be.
I specifically chose MySQLi over PDO to have the versatiliy to use MySQL-specific features. Currently, the only ones I'm using are mysqli::info and proper closing/freeing methods. Unfortunately, asynchronous queries don't have support for prepared statements yet, so I'll wait until they do to implement them.
On a side note, if you'd like to know how to use MySQLi the "vanilla way", check out this tutorial on MySQLi Prepared Statements.
Why Should I Use Simple MySQLi?
- Concise Code
- Awesome fetch modes
- SQL queries are the vanilla
- Accounts for most common uses
- Variable type is optional
- Bind variables and values (not sure how useful this is though)
Why Not Use It?
- No named parameters
- If you need to use some of the more obscure MySQLi functions, then this is certainly not the right fit for you.
- Only supports MySQL and MariaDB
The purpose of this class is to keep things as simple as possible, while accounting for the most common uses. If there's something you'd like me to add, feel free to suggest it or send a pull request.
Supported Versions
PHP 7.1+
Install
Click here to view changes to each version.
Composer
Then include or require the file in your php page.
Git
Clone either the latest version or by tag.
Then include or require the file in your php page.
Table of Contents
- Examples
- Create a New MySQL Connection
- Insert, Update, Delete
- Insert
- Update
- Delete
- Update Same Values
- Select
- Fetch Each Column as Separate Array Variable
- Fetch Associative Array
- Fetch Array of Objects
- Fetch Single Row
- Fetch Single Row Like bind_result()
- Fetch Scalar (Single Value)
- Fetch Single Column as Array
- Fetch Each Column as Separate Array Variable
- Fetch Key/Value Pair
- Fetch Key/Value Pair Array
- Fetch in Groups
- Fetch in Groups, One Column
- Fetch in Groups, Object Array
- Like
- Where In Array
- With Other Placeholders
- Transactions
- Same Template, Different Values
- Transactions with Callbacks
- Error Exception Handling
- Gotcha with Exception Handling
- Freeing and Closing
- Documentation
- Constructor
- query()
- execute()
- whereIn()
- numRows()
- affectedRows()
- info()
- rowsMatched()
- insertId()
- fetch()
- fetchAll()
- atomicQuery()
- transaction()
- freeResult()
- closeStmt()
- close()
- Changelog
Examples
Let's get straight to the point! The best way to learn is by examples.
Create a New MySQL Connection
One of the aspects of MySQLi I actually like a lot is the fact that error reporting is automatically turned off. Unfortunately I wasn't able to replicate this, as I throw an excpetion on the the constructor, therefore potentially exposing the parameter values. This is why I turned on mysqli reporting by doing mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
— since you'll be wrapping it in a try/catch
block anyway. So you must either wrap it around in a try/catch
block or create your own custom handler. Make sure you use $e->getMessage()
and not $e
so your password isn't exposed. Either way, you must solely report your errors in your error log. To do this, make sure your php.ini
file has the following settings in production: display_errors = Off
and log_errors = On
. Keep in mind that using echo
, die
or exit
to print the error message is extremely dangerous as well.
Try/Catch
Custom Exception Handler
This is pretty neat, since you can avoid nesting. It is commonly used to redirect to a single error page, but can be used like the following as well. You can reassign this to give specific messages on your AJAX pages as well. This will catch all of your exceptions on each page this is used on. So you'll either have to call set_exception_handler()
on each page or use restore_exception_handler()
to revert to the previous one.
Insert, Update, Delete
Insert
Update
Delete
Update Same Values
The problem with affectedRows()
is that it will literally just tell you if any rows are affected. So if it returned 0, you wouldn't know if that means that the WHERE clause didn't match or that you updated the row with the same values. One solution Simple MySQLi offers is to use info()
, which utilizes mysqli::info and converts the result string to an array. You can use this in other queries it supports as well.
Here's what info()
would print. This could be useful for checking if you updated your values with the exact same as the old ones.
This is nice and all, but it might be more convenient in some cases to just change the behavior of affectedRows()
to use rows matched, rather than rows changed.
Select
You can either fetch your entire result in an array with fetchAll()
or loop through each row individually with fetch()
, if you're planning on modifying the array. You could obviously use fetchAll()
for any scenario, but using fetch()
is more efficient memory-wise if you're making changes to the array, as it will save you from having to loop through it a second time. However, from my experience, most queries don't need any modifications, so fetchAll()
should primarily be used. If you just need one row, then obviously fetch()
should be used.
Fetch Associative Array
Output:
Fetch Array of Objects
Output:
You can even fetch into a new instance of a class.
Output:
Fetch Single Row
Output:
Fetch Single Row Like bind_result()
Fetch Scalar (Single Value)
This is an ideal way of fetching a scalar. Using the MySQL COUNT
function gives you a number, so you can easily check for truthiness, as no rows would give you a value of 0.
But what if you were to fetch a single value from your database in scenario where the column could have a boolean value, like NULL or 0? This would make it impossible to distinguish between no rows or a falsy values. This is why I'd suggest using numRows()
in every other case instead.
Fetch Single Column as Array
Output:
Fetch Each Column as Separate Array Variable
Output:
Fetch Key/Value Pair
Output:
Fetch Key/Value Pair Array
Output:
Fetch in Groups
Output:
Fetch in Groups, One Column
Output:
Fetch in Groups, Object Array
You can even pass in a class name, like you would with 'obj'.
Output:
Like
Where In Array
With Other Placeholders
Transactions
This is probably my favorite aspect of this class, since the difference in terms of lines of code is absurd. This will also automatically rollback if affectedRows()
is less than one, in case zero rows are affected, which wouldn't trigger an exception. If any error occurs, it will append the message to your error log. Additionally, it will throw any exception if affectedRows()
equals zero.
Same Template, Different Values
Transactions with Callbacks
The regular way of doing transactions in Simple MySQLi with atomicQuery()
is exceedingly concise and can be used in most cases. However, sometimes you might want a little more control. For instance, under the hood, it only checks if each query's affectedRows()
is greater than one. This isn't suitable for a query like INSERT multiple or DELETE/UPDATE query that affects multiple rows.
There's also no need to start the transaction, nor deal with rollbacks. If you want to rollback, simply throw an exception, and it'll rollback for you, while printing the exception solely in the error log. Execute allows you to efficiently reuse your prepared statement with different values.
Error Exception Handling
Either wrap all your queries with one try/catch
or use the set_exception_handler()
function to either redirect to a global error page or a separate one for each page. Don't forget to take out echo in production, as you obviously do not need the client to see this information.
Gotcha with Exception Handling
For some reason, mysqli_sql_exception
doesn't correctly convert errors to exceptions when too many bound variables or types on bind_param()
. This is why you should probably set a global error handler to convert this error to an exception. I'm showing how to convert all errors to exceptions, but it should be noted that a lot of programmers view this as controversial. In this case you only really have to worry about E_WARNING
anyway.
Try/Catch
Custom Exception Handler
Freeing and Closing
Freeing the result and closing the prepared statement is very simple.
You can even chain them.
Documentation
Constructor
Parameters
- string $host - Hostname or an IP address, like localhost or 127.0.0.1
- string $username - Database username
- string $password - Database password
- string $dbName - Database name
- string $charset = 'utf8mb4' (optional) - Default character encoding
- string defaultFetchType = 'assoc' (optional) - Default fetch type. Can be:
- 'assoc' - Associative array
- 'obj' - Object array
- 'num' - Number array
- 'col' - 1D array. Same as
PDO::FETCH_COLUMN
Throws
- SimpleMySQLiException if
$defaultFetchType
specified isn't one of the allowed fetch modes - mysqli_sql_exception If any mysqli function failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
query()
Description
All queries go here. If select statement, needs to be used with either fetch()
for single row and loop fetching or fetchAll()
for fetching all results.
Parameters
- string $sql - SQL query
- array|string|int $values = [] (optional) - values or variables to bind to query; can be empty for selecting all rows
- string $types = '' (optional) - variable type for each bound value/variable
Returns
$this
Throws
- mysqli_sql_exception If any mysqli function failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
execute()
Description
Used in order to be more efficient if same SQL is used with different values. Is really a re-execute function
Parameters
- array $values = [] (optional) - values or variables to bind to query; can be empty for selecting all rows
- string $types = '' (optional) - variable type for each bound value/variable
Returns
$this
Throws
- mysqli_sql_exception If any mysqli function failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
whereIn()
Description
Create correct number of questions marks for WHERE IN()
array.
Parameters
- array $inArr - array used in WHERE IN clause
Returns
string Correct number of question marks
Throws
- mysqli_sql_exception If any mysqli function failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
numRows()
Description
Get number of rows from SELECT.
Returns
- int $mysqli->num_rows
Throws
- mysqli_sql_exception If any mysqli function failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
affectedRows()
Description
Get affected rows. Can be used instead of numRows() in SELECT
Returns
- int $mysqli->affected_rows
Throws
- mysqli_sql_exception If any mysqli function failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
info()
Description
A more specific version of affectedRows() to give you more info what happened. Uses $mysqli::info under the hood. Can be used for the following cases
Returns
- array Associative array converted from result string
Throws
- mysqli_sql_exception If any mysqli function failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
rowsMatched()
Description
Get rows matched instead of rows changed. Can strictly be used on UPDATE. Otherwise returns false
Returns
- int Rows matched
Throws
- mysqli_sql_exception If any mysqli function failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
insertId()
Description
Get the latest primary key inserted
Returns
- int $mysqli->insert_id
Throws
- mysqli_sql_exception If any mysqli function failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
fetch()
Description
Fetch one row at a time
Parameters
- string $fetchType = '' (optional) - This overrides the default fetch type set in the constructor. Check here for possible values
- string $className = 'stdClass' (optional) - Class name to fetch into if 'obj' $fetchType
- array $classParams = [] (optional) - Array of constructor parameters for class if 'obj' $fetchType
Returns
- 1 array row of
$fetchType
specified - Scalar If 'col' type selected
- NULL if at the end of loop (same behavior as vanilla MySQLi)
Throws
- SimpleMySQLiException
- If $fetchType specified isn't one of the allowed fetch modes
- If fetch mode specification is violated
- mysqli_sql_exception If any mysqli function failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
fetchAll()
Description
Fetch all results in array
Parameters
- string $fetchType = '' (optional) - This overrides the default fetch type set in the constructor. Check here for possible values.
fetchAll()
also has additional fetch modes:- 'keyPair' - Unique key (1st column) to single value (2nd column). Same as
PDO::FETCH_KEY_PAIR
- 'keyPairArr' - Unique key (1st column) to array. Same as
PDO::FETCH_UNIQUE
- 'group' - Group by common values in the 1st column into associative subarrays. Same as
PDO::FETCH_GROUP
- 'groupCol' - Group by common values in the 1st column into 1D subarray. Same as
PDO::FETCH_GROUP | PDO::FETCH_COLUMN
- 'groupObj' - Group by common values in the first column into object subarrays. Same as
PDO::FETCH_GROUP | PDO::FETCH_CLASS
- 'keyPair' - Unique key (1st column) to single value (2nd column). Same as
- string $className = 'stdClass' (optional) - Class name to fetch into if 'obj' $fetchType
- array $classParams = [] (optional) - Array of constructor parameters for class if 'obj' $fetchType
Returns
- array Full array of
$fetchType
specified - array [] if no results
Throws
- SimpleMySQLiException
- If $fetchType specified isn't one of the allowed fetch modes
- If fetch mode specification is violated
- mysqli_sql_exception If any mysqli function failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
atomicQuery()
Description
Just a normal transaction that will automatically rollback and print your message to your error log. Will also rollback if affectedRows()
is less than 1.
Parameters
- array|string $sql - SQL query; can be array for different queries or a string for the same query with different values
- array $values - Values or variables to bind to query
- array $types = [] (optional) - Array of variable type for each bound value/variable
Throws
- SimpleMySQLiException If there is a mismatch in parameter values, parameter types or SQL
- mysqli_sql_exception If transaction failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
transaction()
Description
Do stuff inside of transaction
Parameters
- callable $callback($this) - Closure to do transaction operations inside. Parameter value is
$this
Throws
- mysqli_sql_exception If transaction failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
freeResult()
Description
Frees MySQL result
Returns
$this
Throws
- mysqli_sql_exception If mysqli function failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
closeStmt()
Description
Closes MySQL prepared statement
Returns
$this
Throws
- mysqli_sql_exception If mysqli function failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
close()
Description
Closes the MySQL connections
Throws
- mysqli_sql_exception If mysqli function failed due to
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
Changelog
Changelog can be found here