Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9

Thread: random distinct

  1. #1
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,457
    Thanks
    275
    Thanked 32 Times in 31 Posts

    random distinct

    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.

    Code:
    $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.

  • #2
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    Quote Originally Posted by durangod View Post
    will that query work, did i do that right.
    did it work when you tried it?

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    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.

  • #4
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    Quote Originally Posted by Old Pedant View Post
    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.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    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.

  • Users who have thanked Old Pedant for this post:

    durangod (08-07-2011)

  • #7
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,457
    Thanks
    275
    Thanked 32 Times in 31 Posts
    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...

  • #8
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,457
    Thanks
    275
    Thanked 32 Times in 31 Posts
    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.

    Code:
    // 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?

    Code:
    $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.

    Code:
    $query="SELECT * FROM members LEFT JOIN pictures on mem_userid=pic_userid WHERE approved='1' AND paused='N'  ORDER BY Rand()";
    Last edited by durangod; 08-07-2011 at 06:32 PM. Reason: update

  • #9
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,457
    Thanks
    275
    Thanked 32 Times in 31 Posts
    i got it lol should have been pict_userid... works fine


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •