How to Fetch Database Records into a Multidimensional Array using Core PHP

#programming #corephp #website #web

Hi everyone! Today I want to share a simple way to fetch data from a MySQL database and store it in a multidimensional array using Core PHP. This is a very light-weight approach, perfect for small custom projects or CRMs.

<?php
$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'];
    // 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
for($v=0; $v<count($data[0]); $v++) {
echo "Name ";
echo $v+1;
echo " : ";
echo $data[0][$v];
echo " ---- --- -- ";
echo "Email ";
echo $v+1;
echo " : ";
echo $data[1][$v];

echo "<br>";
}

?>

</body>

</html>

In this logic, I am using two main indices in the $data array:

Index 0 stores all the Names.

Index 1 stores all the corresponding Emails.
This makes it easy to loop through the names and emails separately if needed.

Why do we need to store database data in an array?
Accessing data from a local array is much faster than performing repeated queries to the database server for every piece of information. You can load necessary data once into an array and work with it locally within the script's execution.

Please note that fetch_assoc() also returns database rows as PHP associative arrays.

I use similar logic while building custom CRMs for my clients at KM Sol. Check out my portfolio: https://webpk.online/a