Retrieve Data from MySQLi Database using PHP

Retrieve Data from MySQLi

In the last two posts we discussed how to create a connection between MySQLi database and PHP, check that connection if the connection is established successfully, then how to create a database and how to close the connection in this post we see how to retrieve data from MySQLi database using PHP.

Let’s take the following table as an example which is in our database and we want to pick those values from database and show them in an HTML table.

mysqli database table
MySQLi database table

Include separate connection file: 

First, we have to create a connection with MySQLi database. If we have connection code in a separate file we just have to include that file in our all the other pages using include() function which take a file name as a parameter.


Select complete data from a table:

Now, after this, we will write a query which selects all the columns and their corresponding rows from MySQLi database.

mysqli database table entities
MySQL database table entities


Store a query in a string:

To use a query in PHP, we can store that query in a string variable e.g. $sql.


Run/Execute MySQLi query:

Now we have a query in a string type variable named $my_query to run that query () function of MySQLi class is used. As we create an object of MySQLi class name as $conn, so we will invoke that function using that object. And pass a query string in it.


Store results from database table in a variable:

This function runs the query and returns an object file which contains all the returning data which will be according to over query. So we save that object in a PHP variable.


Check for number of rows from the database table:

Now the $get_result variable contains a complete table of returning values. To fetch or to read that table row by row fetch_assoc () function is used and to count the number of rows in that table mysqli_num_rows() function is used. In Object oriented MySQL num_rows attribute is used to count the-the number of rows in returning an object.

To check the number of rows are greater than 0 we apply simple, if condition so that if the returning object has no rows then nothing happen with result tables.


Get content of rows from database result: 

Now to get each column value in each row one by one we use an associative array which read column values from every row one by one using an index value equal to the column name.

To loop through all the rows, we use the above statement in a while or for loop.


Display data from a single database column:

To get the values of the column, we have to echo each column simply like.

The complete code of how to retrieve values from MySQL database is following: