In last week, I was haven’t more work so I was experimenting a MySQLi extension in PHP and I thought that to check “how to connect to a MySQLi database with PHP and the MySQLi extension”. MySQLi stands for “MySQL improved” and its functionality is provided by MySQL 4.1+. As oppose of the old MySQL extension, the new one give us both a functional and an OO interface so that you can choose what you prefer. It also offers support prepared statements which are very useful for PHP queries.
MySQLi is the new extension by which you can connect to a MySQL database.
How to Install MySQLi?
If MySQLi extension is not enabled by default then enable its dll, php_mysqli.dll inside of php.ini by removing semi-colon before the line extension=php_mysqli.dll. By Default the MySQLi extension is enabled in PHP 5.3 and 5.3+.
MySQLi can be used as an object-oriented method or we can use its function separately for coding.In this article am going to explain MySQLi with both object method (OOP) & with separate functions.
What are the New Features in MySQLi
- An Object-oriented interface
- Support for prepared/execute statements
- Support Multiple statements from one query
- Support for transactions
- Enhanced debugging capabilities
- Includes variable binding
- Support SSL and compressed connections
- Replication support and many more.
Let’s check MySQLi step by step so you can get idea for that
How to connect with MySQL database using MySQLi?
Connection to a MySQL database using MySQLi is easy because connection method is same like regular methods with calling connect() function.PHP provides function for MySQLi connection too which is similar to MySQL connection function except some parameters. Let’s understand here how you can connect with MySQL using MySQLi connection function:
Syntax to connect MySQLi:
1 2 3 | $conn = mysqli_connect('host', 'username', 'password', 'database'); |
This connects to a database and stores the connection object as $conn.
If you want to use object-oriented interface, you can also specify your connection parameters by passing them to the constructor of the MySQLi object like
1 2 3 | $mysqli = new mysqli('host', 'username', 'password', 'database'); |
Sometimes you need some more options when connecting to a MySQL server then you can use the mysqli_init, mysqli_options and mysqli_real_connect functions which allow you to set different options for your database connection.Let’s see one example which shows MySQL connection with other parameters of MySQLi connect.
1 2 3 4 5 6 7 8 9 10 11 12 13 | $conn = mysqli_init(); $conn->options(MYSQLI_READ_DEFAULT_FILE, "SSL_CLIENT"); $conn->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5); $conn->real_connect('localhost', 'root', '', 'test'); if ($conn->connect_errno) { die("mysqli_connect failed: " . mysqli_connect_error()); } $conn->close(); |
How to execute Database Query using MySQLi?
Now we are connected with Database and we are ready to execute the query using MySQLi function.It is also very easy like MySQLi connection function, as it is very similar to the mysql_connect function in PHP.
1 2 3 | mysqli_query(mysqli link, string query [, int resultmode] ); |
The most commonly used function is mysqli_query but you can use mysqli_real_query() and mysqli_multi_query() functions.The mysqli_query() function returns a result set object.
NOTE: mysqli_multi_query() is used with Multiple Statements.
Here link have a connection link and query would be a database query that is insert, update, delete etc. result mode is either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT. By default, result mode is MYSQLI_STORE_RESULT.To use unbuffered resultset, you have to specify the optional parameter MYSQLI_USE_RESULT otherwise resultset is buffered.
How to fetch data in MySQLi?
After executing a query in MySQLi, You might be curious to know how I can fetch data in MySQLi and it is easy to understand or not. Yes, It is as easy as mysqli_query function.Let’s understand how you can fetch data using MySQLi function.
1 2 3 | array mysqli_fetch_assoc ( mysqli_result result ). |
There are three ways to fetch rows of results in the old MySQL extension:
- Enumerated array
- An associative array
- An object
But one of the advantages of the MySQLi extension as compared to the MySQL extension are prepared statements to fetch data
NOTE: Prepared statements have the ability to create queries that are more secure, with better performance, and more convenient.
To fetch data or rows, we have many functions which we can use and all are depending on the behavior of data that we want as a result.Mainly we are using mysqli_fetch_assoc which giving the result as an associate array. other we have mysqli_fetch_array,mysqli_fetch_object,mysqli_fetch etc.
How to close Connection and free-up the memory?
1 2 3 | mysqli_close($conn); |
Well, it’s optional step but it’s better and good to close connection and free-up the memory which was acquired by connection resources.
Now it’s time to check demo via example to be clear with MySQLi so let’s see one example which shows MySQL connection in PHP by MySQLi
Example to understand MySQLi in PHP
1 2 3 4 5 6 7 8 9 10 | $conn = mysqli_connect('localhost', 'root', '', 'test'); $query = mysqli_query($conn,"SELECT * from category"); if(mysqli_num_rows($query)>0){ $row = mysqli_fetch_array($query,MYSQLI_ASSOC); echo $row['cat_name']; } mysqli_close($conn); |
Example with Prepare statement for MySQLi in PHP
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $conn = mysqli_connect('localhost', 'root', '', 'test'); if ($conn->connect_errno) echo "Failed to connect to MySQL with MySQLI: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; $stmt_query = $conn->prepare('SELECT * FROM category') or die('Problem preparing query'); $stmt_query->execute(); $result = $stmt_query->get_result(); $row = $result->fetch_assoc(); // To fetch row from database echo $row['cat_name']; /* close connection */ $conn->close(); |
Finally, let’s see some difference between MySQL and MySQLi
MySQL
- slightly Faster than MySQLi
- Less security.
- Only a procedural interface
MySQLi
- slightly slower than MySQL
- More secure.
- provides both an object oriented and procedural interface
So At the end I must say MySQLi and MySQL are not two separate databases. You also like other MySQLi related articles are Buffered Vs Unbuffered Queries in PHP, UDF in MySQL and REPLACE Command in MySQL
If you are using MySQLi in PHP, do share your experience with us. If you find this tutorial useful, do share it with others on Facebook & Google Plus.
Comments (1)