Handling Mysql Operations on Large Tables with PHP

There might be a case where you have a table which stores a lot of data for a dataset, and data might range from 500 to 600 columns and maybe beyond. To process those data in your PHP application development that is fair you can do select and update query on that table but for complex operations like sum of few columns, or to perform any aggregate functions based on conditional logic on more than 300 columns can be frustrating.

We can have the solution which has an optimal way to handle these operations, by combining the aggregate function available in Mysql with PHP scripting to perform all such operations in a better and optimized way.

First and foremost requirement is to have large tables optimized by applying to index on respective columns so that searching and other queries can be executed faster on the system. Additionally, we can optimize it by considering the following scenarios.

SCENARIO 1

Let’s say, we have requirement of listing all columns whose values is greater than 1 and show those values. Assuming column names are in sequence like column_1, column_2, etc. We can run below code using PHP-Mysql to get necessary result

<?php
for($i=1;$i<=300;$i++){
    $colcount[]= "IF(col_".$i." > 1, CONCAT_WS(':','column_'".$i."',column_".$i."),'')"
    }
    $allcol = implode(",",$colcount);
    $query = mysql_query( "SELECT ".$allcol." FROM table1 WHERE id = 123");
?>

SCENARIO 2

It may be possible for ERP systems or CRM that it needs to have a kind of audit report where it will compare standard codes with actual applied codes and need to show report about efficiency or match of records. Consider 300 columns of process codes.

<?php 
$query = mysql_query("SELECT * FROM standardcodes WHERE processid = 131"); 
while ($row = mysql_fetch_array($query)) { 
  for($i=1;$i<=300;$i++){ 
   $stcode[] = $row['codes'.$i]; } 
  } 
    $actual = mysql_query("SELECT * FROM appliedcodes WHERE processid = 131"); 
    while ($row1 = mysql_fetch_array($actual)) { 
      for($j=1;$j<=300;$j++){ 
        if($row1['codes'.$j] == $row['codes'.$j]){ $correct++; } 
        if(in_array($row1['codes'.$j],$stcode) && $row1['codes'.$j] != $row['codes'.$j]){ $avail++; } 
        else { $incorrect++; } 
      } 
   } 
echo "Total Correct codes applied: ".$correct; 
echo "Total applied codes but in random order : ".$avail; 
echo "Total Incorre codes applied: ".$incorrect; ?>

Sample Result

The above example is for a single process of ERP if need to find for a complete batch then loop the processes and create an indexed array like $correct[$processid], so you can generate the report for the complete batch.

SCENARIO 3

There can be a chance to run multiple queries and get the output result in the single report or combine data of multiple tables in a single view to analyze it. To perform such things we have PHP function called “mysqli_multi_query” which can help us:

$query  = "SELECT emp_name,age FROM employeedetails ORDER BY age DESC LIMIT 0,3;";
$query .= "SELECT deptname,totalemployee FROM department ORDER BY totalemployee DESC LIMIT 0, 3;";
$query .= "SELECT department,CONCAT('$',operatingcost) as cost FROM operationscost ORDER BY operatingcost LIMIT 0, 3";;
 
 
if ($mysqli->multi_query($query)) {
    $i = 1;
    do {
              if ($result = $mysqli->store_result()) {
                   if($i == 1){
                    printf("%1\n", "Senior Employees (As per seniority)");
                }
            while ($row = $result->fetch_row()) {
 
                printf("%1       %2\n", $row[0],$row[1]);
            }
            $result->free();
        }
               if ($mysqli->more_results()) {
                if($i == 2){
                    printf("%1\n", "Department Employee strength");
                }
                elseif($i == 3){
                    printf("%1\n", "Operating cost of Department");
                }
        }
   $i++;} while ($mysqli->next_result());
}
$mysqli->close();

Sample Result

Conclusion

Hereby, I would like to end up my insights regarding multiple ways for handling the MYSQL operations for PHP, a Platform that is adopted by numerous Open Source Solutions.