PHP Basics

PHP MySQLi Read Records (Select Statement)

So far we have learned how to establish connection with mysql database using mysqli, create table, insert record. In this tutorial, we are going to learn how to print records from the table. For this we are going to use sql select statement.

The table from which I am going to fetch record is as below. The table name is emp_info

emp_id emp_name emp_age
1 Raj 22
2 Rahul 24
3 Vivek 26
4 Ajay 28
5 Suraj 29

Print Records from table (Select Statement)

So lets see how can we print all the records from the table emp_info

$conn=mysqli_connect(“localhost”,”root”,””,”employee_db”);

$sql=”select * from emp_info”;

$result=mysqli_query($conn,$sql);

if(mysqli_num_rows($result)>0){

while($row=mysqli_fetch_assoc($result)){

echo $row[“id”].” “.$row[“emp_name”].” “.$row[“emp_age”].”<br/>”;

}

}

Output: 

1 Raj 22

2 Rahul 24

3 Vivek 26

4 Ajay 28

5 Suraj 29

In the previous tutorial, I have mentioned to run sql command, we need to call the function mysqli_query which takes connection and sql as argument.

In the above, I have stored the mysqli_query in the result variable. Result variable contains two vital information and that is the number of records collected from the table and the collected records itself in the form of an array.

If no record is fetched then it will contain 0.

Now, to know the number of records it able to fetch, we need to call another mysqli function and that is mysqli_num_rows and pass this result as arugment.

It tells the number of records it has fetched. If no record is fetched then it will return 0.

Note: It is wise always to check number of fetched records. It is more than zero then only proceed.

If it has more than zero records, then its time to loop through. To fetch each record from the fetched result, we are required to call another mysqli function mysqli_fetch_assoc and pass the result as parameter.

Hence we have successfully able to print all the records.

Note: If you have records in millions in the table, then it is not wise to print all the records at once. Because it will consume server system resources which may cost to the company. The best way to print records from table is by setting limit of 100 or 10000 or 10000