MySQL select rows with multiple rows in another table

Question

I have these two tables:

Galleries - {id,title,description}
Images - {id,gallery_id,file}

Each gallery has multiple images in the images table.

How is it best to approach this:

A. Select all galleries than run a for-loop and for each re-query the database for each image that pertains to it.


$sql='select * from galeries';
$result=mysql_query($sql);
while($row=mysql_fetch_assoc($result))
{
    $sql2='select * from images where gallery_id='.$row['id'];
    //etc
}

B. Select all galeries and images using a left join thus getting gallery information mutiple times, as many times as a gallery has images.

$sql='select * from galleries left join images on galleries.id=images.gallery_id';`

Or is there a better way? Thank you.

How are you planning displaying it?

Tony Hopkinson
– Tony Hopkinson

2012-05-16 17:07:59 +00:00
Commented May 16, 2012 at 17:07 — Tony Hopkinson
– Tony Hopkinson, Commented May 16, 2012 at 17:07
One image, the rest goes to a JavaScript array.

Francisc
– Francisc

2012-05-16 17:10:56 +00:00
Commented May 16, 2012 at 17:10 — Francisc
– Francisc, Commented May 16, 2012 at 17:10

Tony Hopkinson · Accepted Answer · 2012-05-16 21:38:54Z

1

I don't like A, because of dulicates. I don't like B because there's an overhead for every query you run.

If I was storing data from both tables. I'd get galleries in one query and store them in an array. Then I'd get the images I needed for the minumu number of galleries in a loop, possibly cache them to avoid getting the again, but that would depend on a number of considerations

There again if it's not a large volume, I might shrug my shoulders and use whichever of A or B made my code more readable. They are both inefficient in their own ways, but working round that will add complexity to the design. Would it be worth it?

answered May 16, 2012 at 21:38

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

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

1 Comment

Francisc Over a year ago

I think your "C" option is the best. I have some 10 galleries on a single page with x images each. I will get the 10 galleries, then the images for those 10, then use PHP to match arrays from the two queries and create a galleries array. Thank you, Tony.

somnath · Accepted Answer · 2012-05-16 17:27:57Z

1

(A) is the easy way out but costs multiple queries to the database. If you database server is busy it will be even more so due to this.

(B) is the engineers way out and uses less database calls and is generally the most efficient database call. However, it will require you to use the group break logic we learnt in our colleges.

When I code, I go the (B) way. When people around me code, they mostly (99%+) code the (A) way! When I tell these people of my reason to use (B) they say that their client has money to buy better hardware if required!

Cheers!

answered May 16, 2012 at 17:27

somnath

1,3351 gold badge9 silver badges13 bronze badges

Comments

Ehsan Khodarahmi · Accepted Answer · 2012-05-16 17:57:50Z

1

I think B is the best choice, because in A you need to get a connection to the database for every query, then send almost same query(only with a different id in where clause) many times to the database, & fetch & process the result again, so you have an unnecessary process & data transfer overhead.
Many programmers don't use join statements because they think it makes their query too heavy! but unless your join tables have a huge amount of rows, I don't guess a simple join such as B cause any problem. Just take care about using join to avoid selecting unused rows or columns.
As the result, I can warrany that B is your best solution!

answered May 16, 2012 at 17:57

Ehsan Khodarahmi

4,97210 gold badges66 silver badges97 bronze badges

Comments

ZurabWeb · Accepted Answer · 2013-07-23 13:29:44Z

1

# you want to get latest 10 galleries, I assume
$query = 'SELECT * FROM `galleries` ORDER BY `id` DESC LIMIT 10';
$result = mysql_query($query);

while($row = mysql_fetch_object($result)) {
    $ids = isset($ids) ? ', '.$row->id : $row->id;
}

# you really don't want to get all the images
# limit amount to some number, ex: 200
$query = 'SELECT * FROM `images` WHERE `gallery_id` IN('.$ids.') ORDER BY `id` DESC LIMIT 200';
...

answered Jul 23, 2013 at 13:29

ZurabWeb

1,3781 gold badge13 silver badges21 bronze badges

1 Comment

ZurabWeb Over a year ago

This way you only run two simple queries without JOIN and GROUP BY clauses, mysql server is happy, your users are happy, your boss is happy :)

Collectives™ on Stack Overflow

MySQL select rows with multiple rows in another table

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related