PHP Inner Join duplicate

[bigheadedd]

Hi,

 

I imagine I am doing this completely the wrong way, but i'm a little stumped either way.

 

I currently have three tables:

 

images

pages

img_assigned

 

the relation is this:

 

images.image_id

                        \

                          img_assigned.img_id

                          img_assigned.page_id

                        /

pages.page_id

 

The idea is that there are lots of different images and pages, and each page can reference the image.

So for example for page id 8, there would be the following in the img_assigned table:

-

page_id: 8

img_id: 22

-

page_id: 8

img_id: 36

 

This all works kind of how I want it do, however, when calling the information with this query:

$result = mysql_query("SELECT * FROM pages 
INNER JOIN img_assigned ON pages.page_id=img_assigned.page_id
INNER JOIN images ON img_assigned.img_id=images.image_id 
WHERE (page_id='8')")or die(mysql_error());

 

It collects the records how I want it to, except when I do a while loop to go through the data like this

 

while($row=mysql_fetch_array($result))
  {
  echo $row['page_content'];
  echo $row['img_id'];
  }

it displays everything twice (or however many records are in img_assigned). Is there any way to get the main data just once, and then loop through the other data?

 

I am using innodb with a relation between the three tables.

 

I hope that makes enough sense, and if anyone could help that would be great!

 

Thanks,

E

[requinix]

First, a SELECT * will grab everything from every table referenced. Do you want all the information from the images and pages and img_assigned tables? Probably not.

 

To deal with the duplication you can do one of two things:

1. Use a second query. First one gets the page data, second one gets the images.

2. Select just the information you want from the pages and images table, grab the first row and use it to determine the page data, then use your loop to get the image data.

[smoseley]

You could also get creative with GROUP_CONCAT:

$sql = "SELECT pages.*, GROUP_CONCAT(img_assigned.img_id) AS img_ids
        FROM pages 
        INNER JOIN img_assigned ON pages.page_id=img_assigned.page_id
        INNER JOIN images ON img_assigned.img_id=images.image_id 
        WHERE page_id = 8
        GROUP BY page_id";

$result = mysql_query($sql) or die(mysql_error());

while($row=mysql_fetch_array($result))
  {
  echo $row['page_content'];
  echo $row['img_ids'];
  }

[fenway]

Just remember that group_concat has a very small max length by default.