...

View Full Version : random distinct



durangod
08-06-2011, 05:22 AM
so if i have a total of 600 rows. and i want to do random distinct for the total about of rows then i can do this right.




$query = "SELECT * FROM images ORDER BY Rand() LIMIT $totalrows Distinct";




the objective is that every time the initial page is accessed it will have a different set of images on it. and we dont want the same image to appear multiple times and then of course i can use that array on my pagination

will that query work, did i do that right.

bullant
08-06-2011, 06:02 AM
will that query work, did i do that right.

did it work when you tried it?

Old Pedant
08-06-2011, 08:10 AM
Here's a hint for you: No.

You can't put DISTINCT after the LIMIT.

But here's another hint: You don't need DISTINCT when you are selecting from only a single table.

bullant
08-06-2011, 08:12 AM
But here's another hint: You don't need DISTINCT when you are selecting from only a single table.

you do if a column in a single table contains the same value in multiple rows and you want only the distinct values.

guelphdad
08-06-2011, 05:30 PM
DISTINCT works on the entire row though so unless selecting from a single column you are going to get multiple rows if any of the other data differs.

Old Pedant
08-07-2011, 05:55 AM
Exactly...so with the statement "god" was using, unless he had two or more 100% identical records, he doesn't need DISTINCT.

I admit I made that assumption. Mea culpa. I figured he was worried about getting distinct random numbers, but he doesn't even need truly random values to get reasonable random ordering of the rows. (Though he *almost* surely will, by nature, with RAND.)

I will say, though, that *THIS* part of his desires may well not come true:

the objective is that every time the initial page is accessed it will have a different set of images

If, for example, he uses LIMIT 5 then it is more than possible that he could get the same first 5 images more than once. It's even within the laws of probability that he might get the same first 5 images twice (or more times!) in a row. It's just unlikely. It becomes more and more unlikely as there are more records to select from.

durangod
08-07-2011, 07:15 AM
thanks for your help just fyi i am not a "god" lol the name is short for
durango dave so i just put durangod yes i know i should have put a _ or somethiing but i been using that name forever and its not the first time someone accused me of thinking i was "god" of which i am not and dont pretent to me, i am a mortal lol...

thanks again it was a big help...

durangod
08-07-2011, 05:37 PM
ok rand worked but and it appears that if i do not give it a limit then i will do rand for all rows which is what i want.

now i need to sort out and only display members that have a picture. not a site generic image. If they have uploaded an image their user id will be listed in the pictures table.

so i think i can do this.



// get an array of user id from pic table

$query="SELECT user_id FROM picture;
$result = mysql_fetch_object($query,$link);
//so result should be a list of all the user id in the pictures table

now i think i can do this but not sure if i can mix a function inside a query?

$querypic="SELECT * FROM members WHERE approved='1' AND paused='N' AND in_array($userid,$result) ORDER BY Rand()";
$onlypic = mysql_fetch_object($querypic,$link);


// something like that should give me only those userid that have actually uploaded images so we dont display generic site default images on page.


any thoughts on that?


UPDATE actually im kinda looking at this bass ackwards right i should be doing somthing like this right and forget about the other array?




$querypic="SELECT * FROM members WHERE approved='1' AND paused='N' LEFT JOIN ON pictures WHERE pic_userid=mem_id ORDER BY Rand()";
$onlypic = mysql_fetch_object($querypic,$link);



this is where i get stumpted WHERE pic_userid=mem_id
can i compare two table value names?


UPDATE2 ok this is what i came up with but its not pulling anything and i know i have a ton of members in pictures table.




$query="SELECT * FROM members LEFT JOIN pictures on mem_userid=pic_userid WHERE approved='1' AND paused='N' ORDER BY Rand()";

durangod
08-07-2011, 07:24 PM
i got it lol should have been pict_userid... works fine



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum