Inserting MySQL table data into a PHP array

Question

I've been working on a website for a business that I own and I'm a bit rusty on my PHP and SQL. I was able to create a mock up shop / inventory array of what I want in PHP, but I am hung up on attempting to insert table data from a MySQL database into my PHP array. What I am trying to do is duplicate my mock up, however, rather than pulling from a defined array (s) in my inventoryArray.php, I'd like to pull data from a table I created in MySQL.

The mock up is as follows:

shop.php (current):

<?php foreach ($inventory as $handgun) {?>
    <div class="column">
        <h5><?php echo "$handgun[model]"?></h5>
        <img class="thumbnail"
            src="assets/style/images/inventory/pistols/<?php echo "$handgun[img]" ?>.png">
        <table class="shopTables">
            <tr>
                <th>MPN:</th>
                <td><?php echo "$handgun[mpn]"?></td>
            </tr>
            <tr>
                <th>UPC:</th>
                <td><?php echo "$handgun[upc]"?></td>
            </tr>
            <tr>
                <th>Accessories:</th>
                <td><?php echo "$handgun[accessories]"?></td>
            </tr>
            <tr>
                <th>Description:</th>
                <td><?php echo "$handgun[description]"?></td>
            </tr>
        </table>
        <a href="product-page.php" class="button expanded">View</a>
    </div>
<?php } ?>

inventoryArray.php (before):

    <?php
    $inventory = array(
         array(
             'action'        => "Striker-fired",
             'category'      => "Pistols",
             'cal_ga'        => "9mm",
             'manufacturer'  => "FN Herstal",
             'model'         => "FN 509 Midsize",
             'UPC'           => "845737010010",
             'img'           => "FN509M",
             'price'         => "$649"
         ),
         array(
             'action'        => "SA/DA",
             'category'      => "Pistols",
             'cal_ga'        => "9mm",
             'manufacturer'  => "CZ USA",
             'model'         => "CZ P01",
             'UPC'           => "806703911991",
             'img'           => "CZP01",
             'price'         => "$627"
         )
     );
    ?>

I did not change anything from shop.php, the following is my attempt at using data from my MySQL table in my inventoryArray.php file:

inventoryArray.php (after):

require_once ('../mysqli_connect.php');
$query = "SELECT * FROM firearms";

$response = @mysqli_query($dbc, $query);
while ($row= mysqli_fetch_array($response)) {
    $inventory = array(
        array(
            'model' => $row['model'],
            'img' => $row['img'],
            'mpn' => $row['mpn'],
            'upc' => $row['upc'],
            'accessories' => $row['accessories'],
            'description' => $row['description']
        )
    );
}

mysqli_close($dbc);

Though this inserts data from my table and into my array, inserting new inventory into my SQL table will not create a newly nested array similar to my mock-up. Instead, it seems to overwrite the original array.

$inventory = array( That overwrites $inventory every time. — Patrick Q
– Patrick Q, Commented Jan 23, 2019 at 20:44

AbraCadaver · Accepted Answer · 2019-01-23 20:56:03Z

4

Just fetch an associative array of the row into a new array and dynamically append to it:

while ($row = mysqli_fetch_assoc($response)) {
    $inventory[] = $row;
}

Really you can do just this:

while ($inventory[] = mysqli_fetch_assoc($response));

If there are more columns in the table and if you don't want them (doesn't matter) then select only the ones you want:

$query = "SELECT model, img, mpn, upc, accessories, description FROM firearms";

edited Jan 23, 2019 at 20:56

answered Jan 23, 2019 at 20:45

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rem C Over a year ago

Thanks! Worked out perfectly and suits my need :)

Sean · Accepted Answer · 2019-01-23 20:44:54Z

It is overwriting, as you are defining the variable on each loop. Instead of

while ($row= mysqli_fetch_array($response)) {
    $inventory = array(
        array(
            'model' => $row['model'],
            'img' => $row['img'],
            'mpn' => $row['mpn'],
            'upc' => $row['upc'],
            'accessories' => $row['accessories'],
            'description' => $row['description']
        )
    );
}

Define $inventory before your loop, and then add to it using $inventory[]

$inventory = array();
while ($row= mysqli_fetch_array($response)) {
    $inventory[] =
        array(
            'model' => $row['model'],
            'img' => $row['img'],
            'mpn' => $row['mpn'],
            'upc' => $row['upc'],
            'accessories' => $row['accessories'],
            'description' => $row['description']
        );
}

Collectives™ on Stack Overflow

Inserting MySQL table data into a PHP array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related