PHPackages                             websitebeaver/simple-mysqli - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. websitebeaver/simple-mysqli

ActiveLibrary[Database & ORM](/categories/database)

websitebeaver/simple-mysqli
===========================

Wrapper class for MySQLi prepared statements

1.5.5(7y ago)623.9k14[1 issues](https://github.com/WebsiteBeaver/Simple-MySQLi/issues)MITPHPPHP >=7.1.0

Since Mar 30Pushed 7y ago9 watchersCompare

[ Source](https://github.com/WebsiteBeaver/Simple-MySQLi)[ Packagist](https://packagist.org/packages/websitebeaver/simple-mysqli)[ RSS](/packages/websitebeaver-simple-mysqli/feed)WikiDiscussions master Synced 1mo ago

READMEChangelogDependenciesVersions (8)Used By (0)

Simple MySQLi - MySQLi Wrapper
==============================

[](#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](http://php.net/manual/en/mysqli.info.php) 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](https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection).

Why Should I Use Simple MySQLi?
===============================

[](#why-should-i-use-simple-mysqli)

- Concise Code
- [Awesome fetch modes](#select)
- 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?
===============

[](#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
==================

[](#supported-versions)

PHP 7.1+

Install
=======

[](#install)

[Click here](https://github.com/WebsiteBeaver/Simple-MySQLi/blob/master/CHANGELOG.md) to view changes to each version.

**Composer**

```
composer require websitebeaver/simple-mysqli

```

Then include or require the file in your php page.

```
require 'vendor/autoload.php';
```

**Git**

Clone either the latest version or by tag.

```
//Get by version number
git clone https://github.com/WebsiteBeaver/Simple-MySQLi/tree/{your version number}

//Get the latest
git clone https://github.com/WebsiteBeaver/Simple-MySQLi.git

```

Then include or require the file in your php page.

```
require 'simple-mysqli.php';
```

Table of Contents
=================

[](#table-of-contents)

- [Examples](#examples)
    - [Create a New MySQL Connection](#create-a-new-mysql-connection)
    - [Insert, Update, Delete](#insert-update-delete)
        - [Insert](#insert)
        - [Update](#update)
        - [Delete](#delete)
        - [Update Same Values](#update-same-values)
    - [Select](#select)
        - [Fetch Each Column as Separate Array Variable](#fetch-each-column-as-separate-array-variable)
        - [Fetch Associative Array](#fetch-associative-array)
        - [Fetch Array of Objects](#fetch-array-of-objects)
        - [Fetch Single Row](#fetch-single-row)
        - [Fetch Single Row Like bind\_result()](#fetch-single-row-like-bind_result)
        - [Fetch Scalar (Single Value)](#fetch-scalar-single-value)
        - [Fetch Single Column as Array](#fetch-single-column-as-array)
        - [Fetch Each Column as Separate Array Variable](#fetch-each-column-as-separate-array-variable)
        - [Fetch Key/Value Pair](#fetch-keyvalue-pair)
        - [Fetch Key/Value Pair Array](#fetch-keyvalue-pair-array)
        - [Fetch in Groups](#fetch-in-groups)
        - [Fetch in Groups, One Column](#fetch-in-groups-one-column)
        - [Fetch in Groups, Object Array](#fetch-in-groups-object-array)
    - [Like](#like)
    - [Where In Array](#where-in-array)
        - [With Other Placeholders](#with-other-placeholders)
    - [Transactions](#transactions)
        - [Same Template, Different Values](#same-template-different-values)
        - [Transactions with Callbacks](#transactions-with-callbacks)
    - [Error Exception Handling](#error-exception-handling)
        - [Gotcha with Exception Handling](#gotcha-with-exception-handling)
    - [Freeing and Closing](#freeing-and-closing)
- [Documentation](#documentation)
    - [Constructor](#constructor)
    - [query()](#query)
    - [execute()](#execute)
    - [whereIn()](#wherein)
    - [numRows()](#numrows)
    - [affectedRows()](#affectedrows)
    - [info()](#info)
    - [rowsMatched()](#rowsmatched)
    - [insertId()](#insertid)
    - [fetch()](#fetch)
    - [fetchAll()](#fetchall)
    - [atomicQuery()](#atomicquery)
    - [transaction()](#transaction)
    - [freeResult()](#freeresult)
    - [closeStmt()](#closestmt)
    - [close()](#close)
- [Changelog](#changelog)

Examples
========

[](#examples)

Let's get straight to the point! The best way to learn is by examples.

Create a New MySQL Connection
-----------------------------

[](#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**

```
try {
  $mysqli = new SimpleMySQLi("localhost", "username", "password", "dbName", "utf8mb4", "assoc");
} catch(Exception $e) {
  error_log($e->getMessage());
  exit('Someting weird happened'); //Should be a message a typical user could understand
}
```

**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.

```
set_exception_handler(function($e) {
  error_log($e->getMessage());
  exit('Someting weird happened'); //Should be a message a typical user could understand
});
$mysqli = new SimpleMySQLi("localhost", "username", "password", "dbName", "utf8mb4", "assoc");
```

Insert, Update, Delete
----------------------

[](#insert-update-delete)

### Insert

[](#insert)

```
$insert = $mysqli->query("INSERT INTO myTable (name, age) VALUES (?, ?)", [$_POST['name'], $_POST['age']]);
echo $insert->affectedRows();
echo $insert->insertId();
```

### Update

[](#update)

```
$update = $mysqli->query("UPDATE myTable SET name = ? WHERE id = ?", [$_POST['name'], $_SESSION['id']]);
echo $update->affectedRows();
```

### Delete

[](#delete)

```
$delete = $mysqli->query("DELETE FROM myTable WHERE id = ?", [$_SESSION['id']]);
echo $delete->affectedRows();
```

### Update Same Values

[](#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](http://php.net/manual/en/mysqli.info.php) and converts the result string to an array. You can use this in other queries it supports as well.

```
$update = $mysqli->query("UPDATE myTable SET name = ? WHERE id = ?", [$_POST['name'], $_SESSION['id']]);
echo $update->affectedRows();
var_export($update->info()); //For more specific version
```

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.

```
['Rows matched' => 1, 'Changed' => 0, 'Warnings' => 0]
```

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.

```
$update = $mysqli->query("UPDATE myTable SET name = ? WHERE id = ?", [$_POST['name'], $_SESSION['id']]);
echo $update->rowsMatched(); //Rows Matched: 1
echo $update->affectedRows(); //Rows Changed: 0
```

Select
------

[](#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

[](#fetch-associative-array)

```
$arr = $mysqli->query("SELECT id, name, age FROM events WHERE id