Placing database data in multidimensional array

Question

For my webshop, I have 1 database with 3 relevant tables. In order to display the data, I was advised to place the data out of these databases in 3 two dimensional arrays so that I could visualize what I was doing. However, my only successful attempt of placing the data in the array resulted in the code overwriting itself, only showing the last data in the table.

Can anyone give me some suggestions on how to set up my array in a way that its automatically filled?

<?php
        //Create the Multiarrays. 2D, one per table.

        // Create connection
        $conn = new mysqli(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        //Open the database
        $sql = "SELECT * FROM productwindow";
        $result = $conn->query($sql);

        //Multiarray locatie 1. When the array starts here, the browser chokes.
            if ($result->num_rows > 0) {
                //multiarray locatie 2. When the array starts here, the browser also chokes.
                while($row = $result->fetch_assoc()) {

                    $product = array( //multiarray locatie 3. The problem is that the array seems to overwrite itself.
                        array(  //I want PHP to repeat this bit of code for every product in my database, so that I can use it later on.
                            "ID" => $row["ID"],
                            "Name" => $row["Productname"],
                            "Price" => $row["Pricetag"],
                            "Supply" => $row["Productsupply"],
                            "Tags" => $row["Tags"],
                            "Materials" => $row["Materials"],
                        ),
                    );
                }
                //Multiarray locatie 2 end woul be placed here
            } else {
                echo "0 results found. Please check database connection.";
            }
        //Multiarray locatie 1 end woul be placed here. 

        $conn->close();
        //      
        echo "<br/>";
        $searchby = "Pluimstaart";//The keyword that I used in earlier concepts to filter on specific products. I now need a for loop to procedurally run through the multiarray.
        $array_subjected_to_search = $product;
        $key = array_search($searchby, array_column($array_subjected_to_search, "Name")); //If it cannot be done via a foreloop, I would need an alternative to "array_search" to show the contents of my array.
        var_dump($array_subjected_to_search[$key]);//Used to see if I actually fill my array with data. It only shows the latest data.

        //From here on out I build the website like follows;
        echo "<br/>product data: " . $product[$key["Supply"]] . ".";
    ?> 

Thank you very much for your feedback!

Answer 1

Try to replace your code of if else code by this code and try

      $product[]=array();
      if ($result->num_rows > 0) {                
            while($row = $result->fetch_assoc()) {
                $product[]["ID"]=$row["ID"];
                $product[]["Name"]=$row["Productname"];
                $product[]["Price"]=$row["Price"];
                $product[]["Supply"]=$row["Supply"];
                $product[]["Tags"]=$row["Tags"];
                $product[]["Materials"]=$row["Materials"];                    
            }

        } else {
            echo "0 results found. Please check database connection.";
        }

Answer 2

The $product variable is being overwritten on each loop because you are making it equal a brand new array each time:

$product = array(...

Instead you should be appending elements to the end of an array, using either array_push, or this shorthand:

$product[] = [
    "ID" => $row["ID"],
    "Name" => $row["Productname"],
    "Price" => $row["Pricetag"],
    "Supply" => $row["Productsupply"],
    "Tags" => $row["Tags"],
    "Materials" => $row["Materials"]
];

Answer 3

I am not sure if this is what you meant...

$i = 0; 
$product = array();

while($row = $result->fetch_assoc()) {

   $product[$i] = array(

    "ID" => $row["ID"],
    "Name" => $row["Productname"],
    "Price" => $row["Pricetag"],
    "Supply" => $row["Productsupply"],
    "Tags" => $row["Tags"],
    "Materials" => $row["Materials"],

  );
$i++;
}