mysql query to an array

Question

I am new to php. I can't figure out how to query my db to list this mockup. For each array, I want to query the db to list only what is related to the left in ''. My db consist of 2 tables.

table1 = album

id, name

table2 = picture

id, album, picPath

$q_albums = array(

            'People' => array(
                        ALBUM_PATH.'4.jpg',
                        ALBUM_PATH.'11.jpg',
                        ALBUM_PATH.'10.jpg'),               
            'Nature' => array(
                        ALBUM_PATH.'2.jpg',
                        ALBUM_PATH.'3.jpg',
                        ALBUM_PATH.'5.jpg',
                        ALBUM_PATH.'6.jpg',
                        ALBUM_PATH.'7.jpg'),                            
            'Art'   => array(
                        ALBUM_PATH.'1.jpg',
                        ALBUM_PATH.'8.jpg',
                        ALBUM_PATH.'9.jpg',
                        ALBUM_PATH.'2.jpg'),                
            'Wilderness' => array(
                        ALBUM_PATH.'3.jpg',
                        ALBUM_PATH.'2.jpg',
                        ALBUM_PATH.'5.jpg',
                        ALBUM_PATH.'7.jpg',
                        ALBUM_PATH.'6.jpg'),                            
            'Photography' => array(
                        ALBUM_PATH.'8.jpg',
                        ALBUM_PATH.'1.jpg',
                        ALBUM_PATH.'9.jpg',
                        ALBUM_PATH.'12.jpg'),                           
            'Fashion' => array(
                        ALBUM_PATH.'11.jpg',
                        ALBUM_PATH.'4.jpg',
                        ALBUM_PATH.'10.jpg'),

        );

Be aware that mysql can only return 2d array, so you will need to fetch all needed data and then build such structure in php. — dev-null-dweller
– dev-null-dweller, Commented Dec 26, 2012 at 23:07

Ilmari Karonen · Accepted Answer · 2012-12-27 15:57:33Z

1

As dev-null-dweller notes, you need to build the array from the query results in a loop. Fortunately, it's pretty easy:

$sql = <<<END
  SELECT album.name AS albumName, picture.picPath AS picPath
  FROM album JOIN picture ON album.id = picture.album
END;

$res = $mysqli->query( $sql );
$q_albums = array();
while ( $row = $res->fetch_object() ) {
    // this line actually build the array, entry by entry:
    $q_albums[ $row->albumName ][] = $row->picPath; 
}
$res->close();

Edit: As Mr. Radical notes, if you want your results to include empty albums, you should change the JOIN to a LEFT JOIN. If you do that, the query will return a row with a null picPath for any empty albums, so you'll have to deal with those null paths somehow. One way would be to include something like the following code after building the array:

foreach ( $q_albums as $album => &$pics ) {
    // remove dummy null entries from empty albums
    if ( !isset( $pics[0] ) ) array_shift( $pics );
}

edited Dec 27, 2012 at 15:57

answered Dec 26, 2012 at 23:24

Ilmari Karonen

50.5k9 gold badges96 silver badges156 bronze badges

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

1 Comment

ducsu Over a year ago

Thank you illmari for the snippet. Made some adjustments and got it to work.

Mr. Radical · Accepted Answer · 2012-12-26 23:33:01Z

1

@Ilmari Karonen I would use LEFT JOIN instead of JOIN. SELECT album.name AS albumName, picture.picPath AS picPath FROM album LEFT JOIN picture ON album.id = picture.album

answered Dec 26, 2012 at 23:33

Mr. Radical

1,8381 gold badge20 silver badges30 bronze badges

3 Comments

Mr. Radical Over a year ago

If you want to expand the database in the future I would like to recommend using a number as ID value for your albums. It is faster to query a number value compared to a string value. Furthermore, you are less likely to make mistakes. E.g if an album is called People in album and the name is people in picture you would run in to troubles.

Ilmari Karonen Over a year ago

If the OP wants the results to include empty albums, then you're right, they should use LEFT JOIN. They'd then also have to adjust their code to ignore any null image paths in the data (either while building the array or while processing it).

Mr. Radical Over a year ago

Yes that is true. For a SQL solution OP could can do a RIGHT JOIN or put picture before the join and album after the join statement to exclude null images. Drawback is that this works the other way around and this SQL query could result in null albums. Better would be would be to put " WHERE picture.picPath <>'' " after picture.album to exclude albums without a picture. Finally, for a very good PHP method I would recommend using Ilmari Karonen code above.

Collectives™ on Stack Overflow

mysql query to an array

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related