CODEWITHSUNDEEP
phpmysqljson
SmalliSax
SmalliSax

Reputation: 342

Generating JSON from products in Database, only takes the last row

The columns are set up like this in a table called "products"

+------------++---------------++----------------------++---------------++----------------+
| product_id  | product_name   | product_description   |  product_image |  product_price |
+------------++---------------++----------------------++---------------++----------------+
|      1      |     a1         |    good               |     url.jpg    |     150dkk     |
+------------++---------------++----------------------++---------------++----------------+
|      2      |     a2         |    nice               |     url.jpg    |     150dkk     |
+------------++---------------++----------------------++---------------++----------------+

Then here is my code, so I loop through my products table and push them into an array that will be JSON.

    //Create an array
    $json_response = json_decode('{"products":[]}');

    $sql = "SELECT * FROM products";
    $result = $conn->query($sql);
    while($row = mysqli_fetch_array($result)){
    $row_array['id'] = $row['product_id'];
    $row_array['name'] = $row['product_name'];
    $row_array['description'] = $row['product_description'];
    $row_array['image'] = $row['product_image'];
    $row_array['price'] = $row['product_price'];
}
    // Push the columns into the created array
    array_push($json_response->products, $row_array);

    // Encode into an object
    $oJsonResponse = json_encode($json_response);

    // Save it in a new file that holds the json from MySQL
    file_put_contents('products.json', $oJsonResponse);

The problem is that I only manage to get product with the id of 2 into my json file so the first product never get's saved in the .json file. When I do echo = "$row[product_name]"; - I get both products

Upvotes: 1

Views: 408

Answers (1)

heXer
heXer

Reputation: 314

Here you go (move array_push into while loop)

<?php

//Create an array
$json_response = array();

$sql = "SELECT * FROM products";
$result = $conn->query($sql);
while ($row = mysqli_fetch_array($result)) {
    $row_array['id'] = $row['product_id'];
    $row_array['name'] = $row['product_name'];
    $row_array['description'] = $row['product_description'];
    $row_array['image'] = $row['product_image'];
    $row_array['price'] = $row['product_price'];

    // Push the columns into the created array
    $json_response[] = $row_array;
}

// Encode into an object
$oJsonResponse = json_encode($json_response);

// Save it in a new file that holds the json from MySQL
file_put_contents('products.json', $oJsonResponse);

Upvotes: 2

Related Questions