Retrieving Records in Php MySQL (Concept on Code Reusability)

USING MULTIDIMENSIONAL ARRAY TO FETCH MYSQL RECORDS IN  PHP:  

You may be pretty big fan of Php-Mysql, & if you are the application development geek or at all the new learner, you will  be frequently  involved in  retrieving the data from database. As following a general pattern, if you have 10 places to retrieve/display the  record , you will write the same fetch query-giving field names of the table for 10 time as I used to do the same. This is some what quite tedious task to write the same query again & again  Like in this way :

  $query = msyql_query($yourSql);
  while($row = mysql_fetch_array($query))
  {
$field1_value = $row['field1'];
$field2_value = $row['field2'];
  }

The limitation of this type of pattern is:
1) You can not display the field values or records execpt inside this while loop.
2) If you have 10  places like this to show records, you will struggle writing the same query for 10 times, specifying field names , variables names & all.

Infact this type of pattern was quite irritating to me also. If you are familar with joomla JDatabase features, it returns you all of the table records in multidimensional array with one single function $db->loadAssocList();

I was inspired with this type of joomla feature & thought why not to implement the same feature, using core php only?

So , here goes the function to  retrieve MySQL records in multidimensional array format so that you can use it any where. All you need to do is just pass the the table name & your where condition for selecting the records from table.

Check it out.




function getAllDataDetails($table, $where)
{
 if($where != '')
 {
  $sql = "SELECT * FROM $table WHERE $where";
 }
 else
 {
  $sql = "SELECT * FROM $table" ;
 }

 $resultArray = array();
 $row_num = 0 ;

 $query = mysql_query($sql);

 //count the number of fields in table
 $num_fields = mysql_num_fields( $query);
  //(resource,index) //returns filed name at particular index
 //Get the field names

 while($row = mysql_fetch_array($query))
 {
 
  for($field_index = 0 ; $field_index < $num_fields ; $field_index++)
  {
  $field_name =  mysql_field_name($query, $field_index) ; //firstly it will hold first field ,then 2nd etc
$resultArray[$row_num][$field_name] = $row[$field_name];
  }

  $row_num++;
 }

 return $resultArray;

}


Now for displaying the records  just call the above function:

$table_name = "myTable";
$where_condition = "where id > 10 ORDER BY id";
$tableRecordDetails = getAllDataDetails($table_name,$where_condition);

// tableRecordDetails  will be the multidimensional array

Now all you need to do is loop through an array , i am the pretty big fan of using foreach() loop pattern.

foreach($tableRecordDetails as $tableData )
{

//here goes your table records for ex-
        $id =  $tableData[fieldName1];  //this will be the field name of your table
$name = $tableData [fieldname2];    

}


No one would be happier than me , if you code,copy, paste or customize the code & implement it in your  web application. If you hava any queries, donot forget to post comment or you can directly reach to me at prem.singh.nepal@gmail.com

Thank you
Have a Browsing moment ahead