从PHP优化数据库查询

Question

I'm building a site Pinterest like-style, is in a very early stage of development.

I can do it without any problems, but I'm wonder to know if it is the best way.

Objective:

Extract all image data from the database, then, for each, extract data from the table "favorites", count the favorites of the photo, and then, if there is an active user session, whether the user marked as favorite.

Problem

The site shows by default 54 photos, then, for those 54 photos, the program must to look the favorites for each. This represents many requests to the server and the database, I have thought it of the form:

$images = 'SELECT * FROM IMAGES photo_id ORDER BY LIMIT 54';

foreach ( $images as $image ) {
   $lookFavorite = 'SELECT * FROM favorites WHERE photo_id ='. $ image ['photo_id'] ';
}

Database tables structure

Photos

photo_id    INT (auto increment)
user_nick   VARCHAR (25)
photo_path  VARCHAR (255)
photo_title VARCHAR (150)
photo_theme VARCHAR (60)
date        TIMESTAMP

Favorites

photo_id  INT
user_nick VARCHAR (25)
date      TIMESTAMP

Important

PHP version 5.3, using MySQL.
The program I'm building is object oriented [I'm using PDO'].

Answer 1

drop the loop and do

$lookFavorite = 'SELECT * FROM favorites';

create an array of everything, and then cycle through what you need from that array based on what you get from

$images = 'SELECT * FROM IMAGES photo_id ORDER BY LIMIT 54';

this way, there are only two queries ran instead of 55

or

$images = 'SELECT * FROM photos INNER JOIN favorites ON (favorites.photo_id = photos.photo_id) ORDER BY favorites.photo_id LIMIT 54';

to do it in a single query