CODEWITHSUNDEEP
phpmysql
Eric Norcross
Eric Norcross

Reputation: 4306

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!

Upvotes: 1

Views: 1037

Answers (4)

Michael
Michael

Reputation: 10464

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.

Upvotes: 1

hendrathings
hendrathings

Reputation: 3755

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

  1. select all data in table entries
  2. 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);

Upvotes: 0

elin3t
elin3t

Reputation: 1911

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

Upvotes: 0

shapeshifter
shapeshifter

Reputation: 2967

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

Upvotes: 0

Related Questions