After the deprecation of PHP MySQL extension in 2011, people are slowly upgrading to better methods like PDO or MySqli to interact with database. Both PDO and MySqli are improved versions and offer an object-oriented API and number of enhancement over the regular MySql extension. But this article isn’t about choosing the best one, we are here to discuss the basic usage of MySqli. So without further ado, I’d like to show you how we can connect, select, insert, update and delete records using PHP MySqli. I am sure this list will come in handy in times of need
If you are running PHP version 5.3.0 +, MySqli should be available to use it right away, but in old PHP 5.0, 5.1, 5.2, extension is not enabled by default on Windows Systems, you must enable php_mysqli.dll DLL inside of php.ini. To enable the extension you need to edit your php.ini and remove comment (semi-colon) from the start of the line extension=php_mysqli.dll. In linux too when you install php5 mysql package, MySQLi automatically gets installed, more details about installation in Linux and Windows can be found here.
The following examples will not work in this lesson because there's no database called database_name in this site, you should use phpMyAdmin in your server to create your own database
Connect to Database
MySqli offers two ways to connect to the database, procedural and object oriented, the official recommended way to open a database connection is object oriented way. The procedural style is much similar to old MySql and it may be helpful to users who are just switching to MySqli, but it is not a recommended way.
Here’s how you open a database connection “object-oriented” style, which is a recommended way and we will only be using this style in all the examples below.
SELECT Multiple Records as Associative array
mysqli_fetch_assoc() : Below is the code to fetch multiple records as an associative array. The returned array holds the strings fetched from database, where the column names will be the key used to access the internal data. As you can see below, data is displayed in an HTML table.
SELECT Multiple Records as Array
fetch_array() : Function returns an array of both mysqli_fetch_row and mysqli_fetch assoc merged together, it is an extended version of the mysqli_fetch_row() function and both numeric and string can be used as keys to access the data.
SELECT Multiple Records as Objects
fetch_object() : To fetch database result set as an objects, just use MySqli fetch_object(). The attributes of the object represent the names of the fields found within the result set.
SELECT Single value
How about getting a single value from database using fetch_object.
Above code will return error if there’s no result, so here’s safer way to retrieve the value.
SELECT COUNT Total records of a table
Sometimes you may want to know total records of a table, especially for a pagination.
SELECT Using Prepared Statements
Another important feature of MySqli is the Prepared Statements, it allows us to write query just once and then it can be executed repeatedly with different parameters. Prepared Statements significantly improves performance on larger table and more complex queries. The queries are parsed separately by the server, making it resilient to malicious code injection.
The code below uses Prepared statement to fetch records from the database. ? placeholder in the SQL query acts like marker and will be replaced by a parameter, which could be string, integer, double or blob. In our case it’s a string $search_product.
Same query with multiple parameters:
INSERT a Record
Following MySQLi statement inserts a new row in the table.
Snippet below inserts same values using Prepared Statement. As discussed earlier the Prepared statements are very effective against SQL injection, you should always use prepared statement in situations like this.
Insert Multiple Records
To insert multiple rows at once, include multiple lists of column values, each enclosed within parentheses and separated by commas. Sometimes you want to know how many records have been inserted, updated or deleted, you can use mysqli_affected_rows for that occasion.
Update/Delete a Records
Updating and deleting records works similar way, just change to query string to MySql Update or delete
Update using Prepared Statement
Here’s how you update record using Prepared Statement
Delete Old Records
Delete all records that is 1 day old, or specify X days records you want to delete.