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

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.

CodePudding user response：

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'],

'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).