Jump to content

Archived

This topic is now archived and is closed to further replies.

chillexistence

SubQueries Selecting Random Pictures for Posts that contain Multiple Pictuers.

Recommended Posts

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

[b]POSTS TABLE[/b]
-------------------------------+------------+
| 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 |


[b]PICTURES TABLE[/b]
+---------+------------+--------------------------------------------+
| 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--][b]so an example of what i want the output to look like would be[/b][!--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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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";

Share this post


Link to post
Share on other sites
Oh, sum, not "sub" -- that makes much more sense... glad you got it working (and thanks for posting the working query).

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.