Why to Fetch Database Records into an Array in Core PHP?

#php #webdev #pakistan #coding

In my last post here I explained "How to Fetch Database Records into a Multidimensional Array using Core PHP". But I left the question unanswered and did not provide details about why do we need to store database data in a multidimensional array while we are already storing the data in associative array through fetch_assoc() function.

Let me explain my point
In our last code we are storing and retrieving name and email thorough $row['name'], $row['email'] respectively. Then we are again saving this data in $data[0][$i] and $data[1][$i] respectively in a while loop.

The question is why we are storing name, email data in $data array. As this is a temporary storage and we do not need it as we can use $row array instead?

Here is the reason

Suppose we have employee data in our database as "name", "email", "phone", "salary", "loan-amount", "time_period".
As data shows employees have taken loans for a fixed period of time. Now our task is to calculate compound interest for each employee and we have to use this compound interest at 4 or 5 different places in this page.

Will it not be convenient that we calculate the interest once and use it again and again? instead of calculating interest again and again using $row array.

Here is the code for more explanation

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM table_name limit 15";
// Execute the SQL query
$result = $conn->query($sql);

$data = array();

// initialize counter
$i = 0;
// Process the result set
if ($result->num_rows > 0) {
  // Output data of each row
  while($row = $result->fetch_assoc()) {
 // Storing database data into array
    $data[0][$i] = $row['name'];
    $data[1][$i] = $row['email'];
    $data[2][$i] = (int)$row['salary']*6;
    // we are calculating interest once in a year while interst rate is 5%
    $data[3][$i] = $row['salary']*(1+1/5)^($row['time'] * 5);
    // increase counter by 1
    $i = $i+1;
  }
} else {
  echo "0 results";
}

$conn->close();
?>

<html>
<head><title>Custom PHP Script</title></head>

<body>
<?php

// display data
echo "<table border=1 cellspacing=0 cellpadding=3>";
for($v=0; $v<count($data[0]); $v++) {
echo "<tr>";
echo "<td>";
echo "Name ";
echo $v+1;
echo " : ";
echo "</td><td>";
echo $data[0][$v];
echo "</td><td>";
echo " ---- --- -- ";
echo "</td><td>";
echo "Email ";
echo $v+1;
echo " : ";
echo "</td><td>";
echo $data[1][$v];
echo "</td><td>";
echo " ---- --- -- ";
echo "</td><td>";
echo "6 Months Salary ";
echo $v+1;
echo " : ";
echo "</td><td>";
echo $data[2][$v];
echo "</td><td>";
echo " ---- --- -- ";
echo "</td><td>";
echo "Compound Interest ";
echo $v+1;
echo " : ";
echo "</td><td>";
echo $data[3][$v];
echo "</td>";

echo "</tr>";
}
echo "</table>";
?>

</body>

</html>

Here you can see we calculated compound interest once and stored it in $data[2][$v]. Now we can use $data[2][0], $data[2][1], $data[2][2] instead of writing that complex compound interest code every time.

Here is output of the above code