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']);
}

first array should be outside loop, second one should be in loop. make $isSet = false; outside loop then inside loop add if(!$isSet) { firstArray...; $isSet = TRUE; } else { secondArray...; } — SSpoke
– SSpoke, Commented Jan 5, 2014 at 12:44
You should not have same array key (artist_id,lastName) with different values in same array. — Awlad Liton
– Awlad Liton, Commented Jan 5, 2014 at 12:51

SSpoke · Accepted Answer · 2014-01-05 12:54:38Z

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']);
  }
}

edited Jan 5, 2014 at 12:54

answered Jan 5, 2014 at 12:48

SSpoke

5,85210 gold badges79 silver badges130 bronze badges

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

1 Comment

Fabian Over a year ago

Thanks for the tips on the isset. This works for me:

foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $artist[] = array ( 'artist_id' => $r['artist_id'], 'lastName' => $r['lastName'] ); } $stmt->execute(); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { if(!isset($category)) { $category[] = array ( 'cat_id' => $r['cat_id'], 'cat_description' => $r['cat_description'], 'artists' => $artist ); } }

Collectives™ on Stack Overflow

PHP / MySQL multidimensional array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related