After the deprecation of PHP MySQL extension in 2011, alternative extension MySqli and PDO are available in PHP. MySqli and PDO are improved version and offer an object-oriented API and number of enhancement over the regular MySql extension. These extensions are much faster, efficient and totally secure against SQL injections.
Today I’d like to show you the basic usage of MySqli, such as connect, select, insert, update and delete records. I hope this list will come in handy for you.
- Installing MySqli
- Connect to Database
- SELECT Multiple Records as Associative array
- SELECT Multiple Records as Array
- SELECT Multiple Records as Objects
- SELECT Single value
- SELECT COUNT Total records of a table
- SELECT Using Prepared Statements
- INSERT Record
- INSERT Record (Prepared Statement)
- Insert Multiple Records
- Update & Delete Records
- Update using Prepared Statement
- Update or Insert record in single query
- Delete Old Records
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.
Connect to Database
MySqli offers two ways to connect to the database, procedural and object oriented, the recommended way to open a database connection is object oriented way, because it is secure, faster and efficient. The procedural style is much similar to old MySql and it may be helpful to users who are just switching to MySqli, but should keep away altogether.
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 (Cameron Spear style).
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.
INSERT a Record (Prepared Statement)
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 any given situations.
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.
Insert or Update if record already exists
People often ask how to INSERT a new row or UPDATE if record already exists. The answer is simple, using ON DUPLICATE KEY UPDATE Syntax in MySql query. This clause simply looks for duplicate values in UNIQUE or PRIMARY key and performs INSERT or UPDATE statement. It is pretty useful when you want to INSERT a new record or UPDATE if record already exist.
Just pass the UNIQUE or PRIMARY value such as ID of a row to perform update, or 0 to insert new row.
MySqli is clearly a winner over the regular MySql extension in PHP, and the implementation is also not that different. I just hope this article will help you migrate/build your projects in future. I have also included some example files in download section, download it for the reference and happy coding!