PHP - Retrieving the Data from the Database

    Retrieving data is also the same process using the mysql_query function. Apart from using the mysql_query function, there are other functions that you can consider.

    1. Using mysql_fetch_array function

    PHP supports the use of mysql_fetch_array that returns the rows in the form of either an associative array or numeric array. This is highly preferred to fetch data from the table within the database.

    Example: 

    <?php  
   $db_host = 'localhost:3036';
   $db_user = 'root';
   $db_pass = 'rootpassword';
   $db_conn = mysql_connect($db_host, $db_user, $db_pass);   
   if(! $db_conn ) {
      die('Connection failed: ' . mysql_error());
   }   
$db_sql = 'SELECT stu_id, stu_name, stu_salary FROM student';
   mysql_select_db('demo_db');
   $db_retval = mysql_query( $db_sql, $db_conn );
if(! $db_retval ) {
      die('data cannot not be retrieved: ' . mysql_error());
   }
   while($db_row = mysql_fetch_array($db_retval, MYSQL_ASSOC)) {
      echo "STU ID :{$db_row['stu_id']}  <br> ".
         "STU NAME : {$db_row['stu_name']} <br> ".
         "STU ADDRESS : {$db_row['stu_address']} <br> ".
         "--------------------------------<br>";
   }
   echo "Retrieved data successfully\n";
   mysql_close($db_conn);
?>

    In the above example, the value of the rows get stored in the $db_row variable and you must use the curly braces whenever you try to insert an array value to the string directly. Also, the mysql_fetch_array function uses the second parameter MYSQL_ASSOC constant which returns the row values as an associative array where you can access the data using their name.

    2. Using mysql_fetch_assoc function

    Another method for fetching the data from the database is using mysql_fetch_assoc function which also fetches and returns the brown as an associative array form. The only change is the function and the parameter that is passed else it will work same as the mysql_fetch_array function.

    Example 1: 

    <?php
  $db_host = 'localhost:3036';
   $db_user = 'root';
   $db_pass = 'rootpassword';
   $db_conn = mysql_connect($db_host, $db_user, $db_pass);   
   if(! $db_conn ) {
      die('Connection failed: ' . mysql_error());
   }
$db_sql = 'SELECT stu_id, stu_name, stu_salary FROM student';
   mysql_select_db('demo_db');
   $db_retval = mysql_query( $db_sql, $db_conn );
if(! $db_retval ) {
      die('data cannot not be retrieved: ' . mysql_error());
   }
   while($db_row = mysql_fetch_array($db_retval)) {
      echo "STU ID :{$db_row['stu_id']}  <br> ".
         "STU NAME : {$db_row['stu_name']} <br> ".
         "STU ADDRESS : {$db_row['stu_address']} <br> ".
         "--------------------------------<br>";
   }
   echo "Retrieved data successfully\n";
   mysql_close($db_conn);
?>

    Also, you can pass another parameter to mysql_fetch_assoc function that is mysql_num constant which allows you to access the data of the rows using that constant index.

    Example 2: 

    <?php
   $db_host = 'localhost:3036';
   $db_user = 'root';
   $db_pass = 'rootpassword';
   $db_conn = mysql_connect($db_host, $db_user, $db_pass);
   if(! $db_conn ) {
      die('Connection failed: ' . mysql_error());
   }
$db_sql = 'SELECT stu_id, stu_name, stu_salary FROM student';
   mysql_select_db('demo_db');
   $db_retval = mysql_query( $db_sql, $db_conn );
if(! $db_retval ) {
      die('data cannot not be retrieved: ' . mysql_error());
   }
   while($db_row = mysql_fetch_array($db_retval, MYSQL_NUM)) {
      echo "STU ID :{$db_row[0]}  <br> ".
         "STU NAME : {$db_row[1]} <br> ".
         "STU ADDRESS : {$db_row[2]} <br> ".
         "--------------------------------<br>";
   }
   echo "Retrieved data successfully\n";
   mysql_close($db_conn);
?>

    3. Releasing memory using PHP

    Once you fetch the data it takes up some memory, so it is necessary that once the result is out that memory should be released to manage the memory consumption. PHP allows us to use the mysql_free_result function to serve that purpose. This statement can be used at the end of the program before closing the connection.

    Example: 

    <?php
   $db_host = 'localhost:3036';
   $db_user = 'root';
   $db_pass = 'rootpassword';
   $db_conn = mysql_connect($db_host, $db_user, $db_pass);
   if(! $db_conn ) {
      die('Connection failed: ' . mysql_error());
   }
$db_sql = 'SELECT stu_id, stu_name, stu_salary FROM student';
   mysql_select_db('demo_db');
   $db_retval = mysql_query( $db_sql, $db_conn );
if(! $db_retval ) {
      die('data cannot not be retrieved: ' . mysql_error());
   }
   while($db_row = mysql_fetch_array($db_retval, MYSQL_NUM)) {
      echo "STU ID :{$db_row[0]}  <br> ".
         "STU NAME : {$db_row[1]} <br> ".
         "STU ADDRESS : {$db_row[2]} <br> ".
         "--------------------------------<br>";
   }  
mysql_free_result($db_retval);
   echo "Retrieved data successfully\n";   
   mysql_close($db_conn);
?>

    People are also reading:

    Previous Tutorial Next Tutorial