Joining 2 mysql tables and generating an array

Question

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!

What have you tried? What sort of SQL query are you using and then what are you doing on the PHP side? (Trying to work out whether you have a SQL problem or a PHP problem) — mathematical.coffee
– mathematical.coffee, Commented Oct 26, 2012 at 0:29

Martin Schneider · Accepted Answer · 2016-11-22 17:45:48Z

1

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.

edited Nov 22, 2016 at 17:45

Martin Schneider

15.7k8 gold badges63 silver badges64 bronze badges

answered Oct 26, 2012 at 1:27

Michael

10.5k1 gold badge39 silver badges51 bronze badges

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

2 Comments

Eric Norcross Over a year ago

Thanks, this worked great with a minor tweek: $query = " SELECT * FROM entries INNER JOIN images ON images.entry_id = entries.entry_id "; $result = mysql_query($query) or die(mysql_error()); $entries = array(); while ($row = mysql_fetch_assoc($result)) { if (!isset($entries[$row['entry_id']])) { $row['images'] = array(); $entries[$row['entry_id']] = $row; } $entries[$row['entry_id']]['images'][] = $row['image_url']; }

Michael Over a year ago

great - you should accept the answer then if it worked for you.

shapeshifter · Accepted Answer · 2012-10-26 00:30:57Z

0

Do you have your database connection code sorted?

I believe your looking for a full outer join. Check this resource for SQL examples for the different joins.

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

answered Oct 26, 2012 at 0:30

shapeshifter

2,9672 gold badges27 silver badges40 bronze badges

1 Comment

shapeshifter Over a year ago

Oh just realised you want to group the image rows under an images column. Your going to need to nest a select.

elin3t · Accepted Answer · 2012-10-26 00:33:21Z

0

The easy way is something like this

 select * from entries, images where images.entry_id = entries.entry_id group by entries.entry_y;

making the join is something like this:

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

answered Oct 26, 2012 at 0:33

elin3t

1,9101 gold badge24 silver badges29 bronze badges

Comments

hendrathings · Accepted Answer · 2012-10-26 01:55:01Z

For display array like that, you cannot use join, try this :

select all data in table entries
foreach table entries, when foreach include table images

Ex:

$q_e = "SELECT * FROM entries"; $result_entry = mysql_query($q_e);

$entries = array(); $images = array();

while($entry = mysql_fetch_array($result_entry)) {
  $q_i = "SELECT * FROM `images` WHERE `entry_id` = '".$entry['entry_id']."'";
  $result_image = mysql_query($q_i);

  while($image = mysql_fetch_array($result_image)) {
      $images[] = array(
          'image_url' => $image['image_url']
      );
  }

  $entries[] = array (
      'entry_id' => $entry['entry_id'],
      'name' => $entry['name'],
      'images' => $images,
  );
}

echo 'pre>';var_dump($entries);

Collectives™ on Stack Overflow

Joining 2 mysql tables and generating an array

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related