PHP / MySQL multidimensional array

Question

I am new to multidimensional arrays and ran into a problem and cannot wrap my head around it. I have been trying to turn this:

Array
(
    [1] => Array
        (
            [cat_id] => 1
            [cat_name] => Schilderijen
            [cat_description] => Omschrijving bij schilderijen
            [artists] => Array
                (
                    [artist_id] => 1
                    [lastName] => ..some value
                )

        )

)
Array
(
    [1] => Array
        (
            [cat_id] => 1
            [cat_name] => Schilderijen
            [cat_description] => Omschrijving bij schilderijen
            [artists] => Array
                (
                    [artist_id] => 4
                    [lastName] => ..some value
                )

        )

)

into something like this, so I can call a category and list the related artists underneath:

Array
(
    [1] => Array
        (
            [cat_id] => 1
            [cat_name] => Schilderijen
            [cat_description] => Omschrijving bij schilderijen
            [artists] => Array
                (
                    [artist_id] => 1
                    [lastName] => ..some value
                    [artist_id] => 4
                    [lastName] => ..some value
                )

        )

)

I am using the following code:

$cat_id = 1;

$query = "SELECT * FROM `categorie_has_artists` ";
$query .= " JOIN `categories` ON categories.cat_id = categorie_has_artists.cat_id AND categorie_has_artists.cat_id = :cat_id"; 
$query .= " JOIN `artists` ON artists.artist_id = categorie_has_artists.artist_id";

$stmt = $dbh->prepare($query);
$stmt->bindParam(':cat_id', $cat_id, PDO::PARAM_INT);
$stmt->execute();

while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
  $array[$row['cat_id']] = array('cat_id' => $row['cat_id'], 'cat_name' => $row['cat_name'], 'cat_description' => $row['cat_description'], 'artist' => array());
  $array[$row['cat_id']]['artist'][] = array('artist_id'=>$row['artist_id'], 'lastName' => $row['lastName']);
}

Answer 1

I haven't used PHP in a while this might not work.

Better way would be to check if atleast one artist subarray exists instead of $isSet flag

$cat_id = 1;

$query = "SELECT * FROM `categorie_has_artists` ";
$query .= " JOIN `categories` ON categories.cat_id = categorie_has_artists.cat_id AND categorie_has_artists.cat_id = :cat_id"; 
$query .= " JOIN `artists` ON artists.artist_id = categorie_has_artists.artist_id";

$stmt = $dbh->prepare($query);
$stmt->bindParam(':cat_id', $cat_id, PDO::PARAM_INT);
$stmt->execute();

$isSet = FALSE;

while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
  if(!$isSet) {
    $array[$row['cat_id']] = array('cat_id' => $row['cat_id'], 'cat_name' => $row['cat_name'], 'cat_description' => $row['cat_description'], 'artist' => array());
    $isSet = TRUE;
  } else {
    $array[$row['cat_id']]['artist'][] = array('artist_id'=>$row['artist_id'], 'lastName' => $row['lastName']);
  }
}

This is the other way to do it (also might not work)

$cat_id = 1;

$query = "SELECT * FROM `categorie_has_artists` ";
$query .= " JOIN `categories` ON categories.cat_id = categorie_has_artists.cat_id AND categorie_has_artists.cat_id = :cat_id"; 
$query .= " JOIN `artists` ON artists.artist_id = categorie_has_artists.artist_id";

$stmt = $dbh->prepare($query);
$stmt->bindParam(':cat_id', $cat_id, PDO::PARAM_INT);
$stmt->execute();

while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
  if(!isset($array[$row['cat_id']][$row['cat_name']][$row['cat_description']])) {
    $array[$row['cat_id']] = array('cat_id' => $row['cat_id'], 'cat_name' => $row['cat_name'], 'cat_description' => $row['cat_description'], 'artist' => array());
  } else {
    $array[$row['cat_id']]['artist'][] = array('artist_id'=>$row['artist_id'], 'lastName' => $row['lastName']);
  }
}