Jump to content


Photo

SubQueries Selecting Random Pictures for Posts that contain Multiple Pictuers.


  • Please log in to reply
3 replies to this topic

#1 chillexistence

chillexistence
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 28 January 2006 - 05:54 AM

I have two Tables:
Table 1: POSTS
post_id, title, description, post_time

Table 2: PICTURES
picture_id, post_id, filename, viewed


[!--sizeo:3--][span style=\"font-size:12pt;line-height:100%\"][!--/sizeo--]Ok Basicaly i'm assigng multiple pictures to each post, but I want one SQL query statement to grab the latest 15 posts, but with each post I want it to only grab one picture (random) for each post, also want it to grab the sub of the hits for each picture under that post [!--sizec--][/span][!--/sizec--]

POSTS TABLE
-------------------------------+------------+
| post_id | title | description | post_time |
-------------------------------+------------+
| 1 | Th isis a new title | must be a description of some sorts | 1137906000 |
| 2 | 2nd post | yup 2nd post | 1138770000 |
| 3 | Big long Title | if i can use up 95 chars | 1137992400 |


PICTURES TABLE
+---------+------------+--------------------------------------------+
| post_id | picture_id | filename | viewed
+---------+------------+--------------------------------------------+
| 1 | 1 | 71631afterthestorm___southeastmichigan.jpg | 10 |
| 1 | 2 | 70438bridgesnow.jpg | 5 |
| 1 | 3 | 13813finallywhite.jpg | 3 |
| 1 | 4 | 46335thebeautyofsnow3.jpg | 1 |
| 1 | 5 | 2234itsbeginningtolooklikechristmas.jpg | 100 |
| 1 | 6 | 87418eveningglow2.jpg | 51 |
| 2 | 7 | 8765afterthestorm___southeastmichigan.jpg | 32 |
| 2 | 8 | 5521antisolarrays.jpg | 16 |
| 2 | 9 | 75361bluemosqueandhagiasofia.jpg | 5 |
| 2 | 10 | 42551bridgesnow.jpg | 6 |
| 2 | 11 | 27259darkshot.jpg | 8 |
| 2 | 12 | 2579doodlesbyjackfrost.jpg | 0 |
| 2 | 13 | 10944endofawinterday.jpg | 0 |
| 2 | 14 | 52402eveningglow2.jpg | 3 |
| 2 | 16 | 5692eveningsilk.jpg | 6 |
| 2 | 17 | 6489finallywhite.jpg | 10 |
| 2 | 18 | 97995fireinthesky.jpg | 15 |
| 2 | 20 | 61735fivemoreminutesanotherangle.jpg | 20 |
| 3 | 22 | 26410peppered.jpg | 25 |
| 3 | 23 | 69480onfrozenpond.jpg | 30 |
| 3 | 24 | 74988mountcheam.jpg | 6 |
| 3 | 25 | 67594momentsago.jpg | 3 |
| 3 | 26 | 10041lotsofsnow.jpg | 15 |
| 3 | 27 | 16440lightmeetsnight.jpg | 45 |
| 3 | 28 | 99927lateafternoon.jpg | 32 |
| 3 | 29 | 67191itsbeginningtolooklikechristmas.jpg | 12 |
| 3 | 30 | 22382iridescentclouds.jpg | 95 |
| 3 | 31 | 92893icysunrise.jpg | 1400 |
| 3 | 32 | 39515iceonthetrees.jpg | 6 |
| 3 | 33 | 63468huddletime.jpg | 5 |
| 3 | 34 | 72809heavysnowstorm.jpg | 4 |
| 3 | 35 | 20713heavyladened.jpg | 3 |
| 3 | 36 | 78126frigidsunset.jpg | 2 |
| 3 | 37 | 53299frigidmorning.jpg | 1 |
| 3 | 38 | 20698fivemoreminutesanotherangle.jpg | 0 |
| 3 | 39 | 71775firefoxtrashit.jpg | 5 |
| 3 | 40 | 50135finallywhite.jpg | 3 |
| 3 | 41 | 83561fireinthesky.jpg | 2 |
| 3 | 42 | 75422eveningsilk.jpg | 5 |
| 3 | 43 | 67954eveningglow.jpg | 6 |
+---------+------------+--------------------------------------------+



[!--sizeo:4--][span style=\"font-size:14pt;line-height:100%\"][!--/sizeo--]so an example of what i want the output to look like would be[!--sizec--][/span][!--/sizec--]

+---------+------------+--------------------------------------------+
| post_id | picture_id | filename | viewed | title | desc
+---------+------------+--------------------------------------------+
| 1 | 3 | 13813finallywhite.jpg | 170 |
| 2 | 13 | 10944endofawinterday.jpg | 121 |
| 3 | 38 | 20698fivemoreminutesanotherangle.jpg | 1705 |


If there's a better way to store the necessary data in database I'd put all for storeing it differently. Any help would be greatly appreciated.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 January 2006 - 08:21 PM

That's a little confusing. You can get the last 15 with a "ORDER BY post_time LIMIT 15", pull a random pictures with a subselect and an "ORDER BY RAND() LIMIT 1", but I don't know what you mean by "sub of hits" -- and the output you show has a "viewed" value that doesn't correspond to anything I can see in the tables. Please clarify.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 chillexistence

chillexistence
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 29 January 2006 - 10:08 PM

The solution is as follows:

$sql = "SELECT posts.post_id,
title,
description,
post_time,
(SELECT filename
FROM pictures
WHERE pictures.post_id = posts.post_id
ORDER BY RAND()
LIMIT 1) AS filename,
(SELECT sum(viewed)
FROM pictures
WHERE pictures.post_id = posts.post_id ) as viewed
FROM posts
WHERE post_time <= ". time() ."
ORDER BY posts.post_time DESC
LIMIT 15";

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 January 2006 - 11:04 PM

Oh, sum, not "sub" -- that makes much more sense... glad you got it working (and thanks for posting the working query).
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users