How to Retrieve Data from MySQL Database using PHP

Hello folks! welcome back to a new section of our tutorial on PHP. In this section of our PHP tutorial, we will be studying about how to Retrieve Data from the MySQL Database using PHP.

How can a Data be Retrieved?

Data can be fetched from MySQL tables by executing the SQL SELECT statement using the mysql_query function. You have several options to fetch data from MySQL.

The most frequently used option is the PHP mysql_fetch_array() function. This function returns back row as an associative array, a numeric array or both. This function returns False if no rows are found.

Example

Try the following example below to display all the records from the employee table -

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   $sql = 'SELECT emp_id, emp_name, emp_salary FROM employee';
   mysql_select_db('test_db');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   
   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "EMP ID :{$row['emp_id']}  <br> ".
         "EMP NAME : {$row['emp_name']} <br> ".
         "EMP SALARY : {$row['emp_salary']} <br> ".p
         "--------------------------------<br>";
   }
   
   echo "Fetched data successfully\n";
   
   mysql_close($conn);
?>

READ: How to Insert Data into MySQL Database using PHP

The content of the rows are assigned to the variable $row and the values in row are then printed out.

Note: Never forget to include curly brackets when you want to put an array value directly into a string.

In the example above, we have made use of the MYSQL_ASSOC constant as the second argument to mysql_fetch_array(), so that it returns row as an associative array. With an associative array you can access the field by using their name instead of using the index.

PHP makes available to it's users another function called mysql_fetch_assoc() which also returns row as an associative array.

Example

Try the following example below to display all records from employee table using the mysql_fetch_assoc() function.

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   $sql = 'SELECT emp_id, emp_name, emp_salary FROM employee';
   mysql_select_db('test_db');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   
   while($row = mysql_fetch_assoc($retval)) {
      echo "EMP ID :{$row['emp_id']}  <br> ".
         "EMP NAME : {$row['emp_name']} <br> ".
         "EMP SALARY : {$row['emp_salary']} <br> ".
         "--------------------------------<br>";
   }
   
   echo "Fetched data successfully\n";
   
   mysql_close($conn);
?>

READ: A Definitive Guide to PHP Bugs Debugging

You can also use MYSQL_NUM constant, as next argument to mysql_fetch_array(). This is going to cause the function to return an array with a numeric index.

Example

Try the below example to show all records from employee table by using MYSQL_NUM argument -

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   $sql = 'SELECT emp_id, emp_name, emp_salary FROM employee';
   mysql_select_db('test_db');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   
   while($row = mysql_fetch_array($retval, MYSQL_NUM)) {
      echo "EMP ID :{$row[0]}  <br> ".
         "EMP NAME : {$row[1]} <br> ".
         "EMP SALARY : {$row[2]} <br> ".
         "--------------------------------<br>";
   }
   
   echo "Fetched data successfully\n";
   
   mysql_close($conn);
?>

All the above three examples is going to produce same result.

READ: PHP Error and Exception Handling

Releasing Memory

It is a very good practice to release the cursor memory at the end of each SELECT statement. This task can be done using the mysql_free_result() function.

Example

Try the following example below to release cursor memory -

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   $sql = 'SELECT emp_id, emp_name, emp_salary FROM employee';
   mysql_select_db('test_db');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   
   while($row = mysql_fetch_array($retval, MYSQL_NUM)) {
      echo "EMP ID :{$row[0]}  <br> ".
         "EMP NAME : {$row[1]} <br> ".
         "EMP SALARY : {$row[2]} <br> ".
         "--------------------------------<br>";
   }
   
   mysql_free_result($retval);
   echo "Fetched data successfully\n";
   
   mysql_close($conn);
?>

READ: A Guide to PHP Arrays

Alright guys! This is where we are rounding up for this tutorial post. In our next tutorial guide, we will be discussing about how to use Paging through PHP.

Feel free to ask your questions where necessary and we will attend to them as soon as possible. If this tutorial was helpful to you, you can use the share button to share this tutorial.

Follow us on our various social media platforms to stay updated with our latest tutorials. You can also subscribe to our newsletter in order to get our tutorials delivered directly to your emails.

Thanks for reading and bye for now.