CODEWITHSUNDEEP
phpmysqlsqlpdo
Tomás Juárez
Tomás Juárez

Reputation: 1514

Optimizing database queries from PHP

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'].

Upvotes: 0

Views: 158

Answers (4)

san4o
san4o

Reputation: 196

1 do not use * if you dont need all fields.

test on you server, but i think this solution work faster 

$images = 'SELECT photo_id, photo_path FROM photos LIMIT 54';
$ids = array();
foreach ( $images as $image ) {
  $ids[]= $image ['photo_id'];
}

$lookFavorite = 'SELECT * FROM favorites WHERE photo_id in ('. implode(',',$ids). ')';

Upvotes: 0

Wil Hunt
Wil Hunt

Reputation: 49

I think what you are looking for here is a LEFT JOIN.

SELECT * FROM images
LEFT JOIN favorites
ON images.photo_id = favorites.photo_id
WHERE images.photo_id IN (..array populated from first query..)

If you need to limit your results to just the first 54 images then you can just populate the WHERE clause of the JOIN with the appropriate predicate.

As a side note, I chose LEFT JOIN because I want to include all images in that list even if they have no favorites, but I want to not include favorites that are not relevant to the images I want.

Upvotes: 0

Joe Meyer
Joe Meyer

Reputation: 4405

You should use a Left join From Images to Favorites and then use GROUP BY's as needed to get your counts in case there are images that have 0 favorites.

SELECT * 
FROM `images` 
LEFT JOIN `favorites` ON `images`.`photo_id`=`favotites`.`photo_id`

Upvotes: 2

bizzehdee
bizzehdee

Reputation: 21003

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

Upvotes: 0

Related Questions