多个MYSQL查询与多个php foreach循环

Question

Database structure:

id  galleryId                 type     file_name       description
1   `artists_2010-01-15_7c1ec`  `image`  `band602.jpg`   `Red Umbrella Promo`
2   `artists_2010-01-15_7c1ec`  `image`  `nov7.jpg`      `CD Release Party`
3   `artists_2010-01-15_7c1ec`  `video`  `band.flv`      `Presskit`

I'm going to pull images out for one section of an application, videos on another, etc. Is it better to make multiple mysql queries for each section like so:

$query = mysql_query("SELECT * FROM galleries WHERE galleryId='$galleryId' && type='image');

...Or should I be building an associative array and just looping through the array over and over whenever I need to use the result set?

Thanks for the thoughts.

Answer 1

It depends what's more important: readability or performance. I'd expect a single query and prefilling PHP arrays would be faster to execute, since database connections are expensive, but then a simple query for each section is much more readable.

Unless you know (and not just hope) you're going to get a huge amount of traffic I'd go for separate queries and then worry about optimising if it looks like it'll be a problem. At that point there'll be other things you'll want to do anyway, such as building a data access layer and adding some caching.