PHP

PHP mysqli class to connect with MySQL database

PHP mysqli class is one of the multiple ways to connect with MySQL database using PHP. In this post we will learn about PHP mysqli class, it’s useful methods and properties.

Following logical steps will show you how to use mysqli class to access MySQL database in PHP:

  1. Connecting with MySQL database using PHP
  2. Creating a Table in MySQL Database using PHP mysqli
  3. Inserting data in MySQL database using mysqli class
  4. Update data using mysqli class in PHP
  5. Fetch Associated array from MySQL table
  6. Fetch table row as object using mysqli class

Connecting with MySQL database using PHP mysqli

To establish connection, we need to create an object of PHP mysqli class. Once the object is created, we will check it’s connect_errno property. If connection is successful, connect_errno property will hold 0 otherwise it will hold the MySQL error code as described here.

Notice the order of the parameters of mysqli constructor, it is host,username,password and database name respectively.

Creating a Table in MySQL Database using PHP mysqli

To create a new table in database, we use query() method of mysqli class. This method returns FALSE if query fails. In queries, where a result set is expected, query() method will return result set and for other successful queries query() method returns TRUE.

CREATE TABLE is SQL command therefore, discussing it is not in the scope of this article. We store SQL query in a string, later this string is passed as first and only required parameter for mysqli query() method.

Inserting data in MySQL database using mysqli class

Inserting records in MySQL database using mysqli class is similar to the previous section as the process is same but we will use a different SQL Command INSERT INTO for this purpose.

The mysqli class affected_rows property is useful in insert and update operations as it hold the number of rows, which query has affected .i.e. in this case, it will hold 1 as this query is inserting one row in table.

For inserting multiple records at once, we just need to change the SQL command INSERT INTO as follows:

Update data using mysqli class in PHP

Updating the record in MySQL table using mysqli class is pretty straight forward.

Fetch associated array from MySQL table using mysqli

Reading data from MySQL table using PHP mysqli extension involves retrieving a result set from table and then iterate through it by fetching record as associative array one by one.

As shown in code snippet above, the query() method is now returning a result set instead of TRUE or FALSE as in insert and update operations discussed in previous sections. Here we are getting the students records from MySQL table and creating a HTML table to view it in a browser.

num_rows property of result set object is having the number of rows or records, this result set is consisting of, therefore it is a good idea to evaluate num_rows property before using the result set to read data.

While reading data from database table, it is very important to learn how fetch_assoc() method of result set object works. Whenever we call fetch_assoc() method on result set object, it gives us one row and move internal data pointer to next row, which means if we call fetch_assoc() again, it will return next row and so on. Ultimately if we use fetch_assoc() in a while loop, it will execute on each iteration and returns a new row. If there is no record left fetch_assoc() will return NULL which will cause while loop to exit.

As fetch_assoc() returns associative array, The key of the array is column name of the table and value of the array is the column value for current row. Building up of HTML table is straight forward and don’t need any explanation.

Fetch table row as object using mysqli class

To fetch record as object instead of associative array, we just have to replace fetch_assoc() method with fetch_object().

Conclusion

In this tutorial, I tried to focus on practical and code oriented approach to teach this topic. Please share your valuable feedback by commenting.

About Zohaib Shah

Passionate software engineer with expertise in Django, Laravel and NodeJS. Working with different SaaS based products and API connected apps. Software Architect of UK's leading online travel agency. Get in touch
View all posts by Zohaib Shah →

Leave a Reply

Your email address will not be published.