Optimizing database queries from 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'].

What you're looking for is a join: dev.mysql.com/doc/refman/5.0/en/join.html — jeremyharris
– jeremyharris, Commented Mar 11, 2013 at 20:36
You are leaving yourself open to SQL injection attacks. It's good that you're using PDO, so now you can go learn about how to use parametrized queries with PDO. — Andy Lester
– Andy Lester, Commented Mar 11, 2013 at 20:58
Yes, it's only an example, I wanna know how to do secures queries! — Tomás Juárez
– Tomás Juárez, Commented Mar 11, 2013 at 21:07

Joe Meyer · Accepted Answer · 2013-03-11 20:40:49Z

2

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`

answered Mar 11, 2013 at 20:40

Joe Meyer

4,4452 gold badges23 silver badges28 bronze badges

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

Comments

bizzehdee · Accepted Answer · 2013-03-11 20:36:13Z

0

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

answered Mar 11, 2013 at 20:36

bizzehdee

21.2k13 gold badges49 silver badges79 bronze badges

2 Comments

user557846 Over a year ago

or use a join and then there's only 1

Tomás Juárez Over a year ago

But, if the table favorites have 150.000 records, It's convenient extract all the data instead of make 54 queries?

Wil Hunt · Accepted Answer · 2013-03-11 20:44:48Z

0

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.

answered Mar 11, 2013 at 20:44

Wil Hunt

492 bronze badges

Comments

san4o · Accepted Answer · 2013-03-11 20:52:09Z

0

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). ')';

answered Mar 11, 2013 at 20:52

san4o

1961 silver badge7 bronze badges

Collectives™ on Stack Overflow

Optimizing database queries from PHP

Objective:

Problem

Database tables structure

Photos

Favorites

Important

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Objective:

Problem

Database tables structure

Photos

Favorites

Important

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related