CODEWITHSUNDEEP
phpmysqlsqlmariadbmariadb-10.4
universe11
universe11

Reputation: 1063

how can I add all images in a array but show only one car

I have a table named cars. Then I have a table named car_infos and then I have a table car_photos.

So if I have 3 images on my car_photos and want to select all cars then my rows shows the car x3. If I have 4 images then my rows shows the car x4. So if I have 2 cars in my table and I select all cars then it shows 8 times. 4 for each image. If I group it by ID then it shows correctly but how can I put my images that the car have in a array ?

query:

<?php
  session_start();
  require_once __DIR__ . '/../config/db.php';

  $handy = array();
  $handy['records'] = array();
  $data = json_decode(file_get_contents('php://input'), true);

  if($stmt = $pdo->prepare("
  
    SELECT 
    
      cars.id,
      cars.name,
      
      car_info.car_id,
      car_info.bedingungen,
      car_info.beschreibung,
      car_info.price,
      car_info.c_year,
      car_info.mileage,
      car_info.engine,
      car_info.transmission,
      car_info.ps,
      car_info.max_kmh,
    
      car_photos.car_id,
      car_photos.image,
      car_photos.added
      
    FROM cars

    LEFT JOIN car_info 
      ON car_info.car_id = cars.id

    LEFT JOIN car_photos
      ON car_photos.car_id = cars.id

    GROUP BY `cars`.`id`

    ORDER BY car_photos.added ASC LIMIT 100;")) {

  if(!$stmt->execute()) {
    print_r($stmt->errorInfo());
  } else {
    while($row = $stmt->fetch()) {

      $handyList = array(
          'id' => $row['id'],
          'name' => $row['name'],
          'bedingungen' => $row['bedingungen'],
          'beschreibung' => $row['beschreibung'],
          'price' => $row['price'],
          'c_year' => $row['c_year'],
          'mileage' => $row['mileage'],
          'engine' => $row['engine'],
          'transmission' => $row['transmission'],
          'ps' => $row['ps'],
          'max_kmh' => $row['max_kmh'],
          'image' => $row['image'],
      );

      array_push($handy['records'], $handyList);

    }

    http_response_code(200);

    echo json_encode($handy);

  }
}

?>

So if I group it then it shows correclty but I only get one photo result not the others... I want to have a car with all the images that the car have. But with group it shows only one photo.

Upvotes: 0

Views: 44

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31812

For newer versions you can use JSON_ARRAYAGG() to group related photos to one column. You would need a query like this:

SELECT 
  cars.*,     -- list all required columns in the final code
  car_info.*, -- list all required columns in the final code
  JSON_ARRAYAGG(JSON_OBJECT(
    'image', car_photos.image,
    'added', car_photos.added
  )) as photos
FROM cars
LEFT JOIN car_info 
  ON car_info.car_id   = cars.id
LEFT JOIN car_photos
  ON car_photos.car_id = cars.id
GROUP BY cars.id
ORDER BY cars.id ASC LIMIT 100;

However, since your version (MariaDB 10.4) doesn't support JSON_ARRAYAGG() you should use GROUP_CONCAT() and CONCAT() as workaround. Change the SELECT part to

SELECT 
  cars.*,
  car_info.*,
  CONCAT('[', GROUP_CONCAT(JSON_OBJECT(
    'image', car_photos.image,
    'added', car_photos.added
  ) ORDER BY car_photos.added ASC), ']') as photos

It might even be prefered because GROUP_CONCAT supports sorting, while JSON_ARRAYAGG does not.

Then decode that JSON value in PHP:

Change

'image' => $row['image'],

to

'photos' => json_decode($row['photos'], true),

You will get a nested array and can access the images with something like

$handy['records'][5]['photos'][2]['image']

which would be the 3rd image from the 6th car (considering 0-indexing).

Upvotes: 1

Related Questions