chillexistence

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";

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.

i'm hacing some issues when linkin two tables together, and i'm not getting the results that i want... like sometimes i get the same result over again for the number of times there is something in the one table.... two tables table 1 has 3 colums, ID, Image_ID, Gallery_ID table 2 has bunch of columsb main colum to focus on is image_id, see what i want to happen is i want it to list all the pictures that appear in lets say Gallery_ID = 2, i can get this querry to work PERFECTLY, but i aslo want to be able to display which pictures DO NOT appear in Gallery_ID = 2, but this i can't get to work for the love of god, and then i aslo want it to show me which pictures do appear in the gallery adn which do not appear in the gallery, but on this querry i get a whole bunch of stuff doubled up

Hi, I'm somewhat new to the whole PHP & MySQL database and currently working on a complex project and need some assistance. I have 3 basic tables, Table 1 contains the following info Image_ID, & all related pictures info Table2 contains the follwoing info Gallery_ID, Gallery_Title, Gallery_Description Table3 contains 3 things ID, Gallery_ID, Image_ID what i want to do is be able to assign multiple pictures to multiple galleries and some images might appear in several galleries. So of course a sample entry in table 3 might be ID-1, Gallery_ID-12, Image_ID-16 but where i need the help is I want to be able to add, and update which galleries images are in, so i'm putting all the possible galleries into a drop-down box that allows multiple selections, but I'm not sure how to have it search to see if the image already exists in the gallery, and if not then add it or update it, is there an easy approach without running several queries all at once.