greetification greetification - 16 days ago 5
MySQL Question

Joining 2 mysql tables and generating an array

entries and images and have been trying every way I can find to join them to get the below result

entries:
entry_id
name

images:
entry_id
image_url


I'm using php and would like to be able to retrieve all associated image_url rows for a given entry_id and combine them with the other information from the entries table.

So I have something like:

entries:
1, Brian
2, Steve
3, Jane

images:
1, images/brian1.jpg
1, images/brian2.jpg
2, images/steve.jpg
3, images/jane_1.jpg
3, images/jane_2.jpg
3, images/jane_3.jpg


And would like to get an array back something like

array(3) {
[0]=>
array(3) {
["entry_id"]=>
string(1) "1"
["name"]=>
string(5) "Brian"
["images"]=>
array(2) {
["image_url"]=>
string(17) "images/brian1.jpg"
["image_url"]=>
string(17) "images/brian2.jpg"
}
}
[1]=>
array(3) {
["entry_id"]=>
string(1) "2"
["name"]=>
string(5) "Steve"
["images"]=>
array(1) {
["image_url"]=>
string(16) "images/steve.jpg"
}
}
[2]=>
array(3) {
["entry_id"]=>
string(1) "3"
["name"]=>
string(5) "Jane"
["images"]=>
array(3) {
["image_url"]=>
string(18) "images/jane_1.jpg"
["image_url"]=>
string(18) "images/jane_2.jpg"
["image_url"]=>
string(18) "images/jane_3.jpg"
}
}
}


Thank you!

Answer

Having tested none of this, I assume you want something like this:

SELECT * FROM entries INNER JOIN images ON images.entry_id = entries.entry_id

Then loop through those results:

$entries = array();

while ($row = mysql_fetch_assoc($data))
{
    if (!isset($entries[$row['entry_id']]))
    {
        $row['images'] = array();
        $entries[$row['entry_id']] = $row;
    }

    $entries[$row['entry_id']]['images'][] = $row['image_url'];
}

Then you can loop through this return:

foreach ($entries as $entry)
{
    foreach ($entry['images'] as $image);
    // do something interesting
}

That should about do it for you, but you may have to modify some things.