SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    May 2002
    Location
    Calgary
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Random Row AND Column(mySQL)

    Here is the deal. I have no problem selecting a random row in mysql, using the simple order by Rand(). However, I would now like to select a random row as usual, but now instead of selecting only 1 field, i want to select 1 field between 5 (at random). This statement will probably better explain this.

    My original statement: (I am using asp)
    "SELECT field1 FROM tablename WHERE username <> 'value' AND field1 <> '' ORDER BY RAND() limit 1"

    With my new statement I need to select a random field, between field1 and field5, where none of them are '' (blank).


    UPDATE - I guess what I mean is a random COLUMN between field1 and field5. In my original message I said field, I guess saying column is better to understand though.
    Last edited by nonstoptraffic; May 25, 2002 at 22:49.

  2. #2
    SitePoint Addict EvilDoppler's Avatar
    Join Date
    Dec 2001
    Location
    Perth, Australia
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    If you're using mysql then just put in a max on the limit side..

    Code:
    ...Order by Rand() limit 1, 5
    think that should work...

    cheers
    * Due to the last tax raise our prices has risen
    * - answers w/o thought $1 - answers w/ thought $3
    * - correct answers $10
    * - Dumb stares are still free

  3. #3
    SitePoint Member
    Join Date
    May 2002
    Location
    Calgary
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    I am already doing that with my random row, Order by Rand() limit 1, but now I need to select a random column between field1 and field5.

    thanks for the try though

  4. #4
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmmm, I'm sure theres a function in PHP to do that, but not sure of it, so try this.
    PHP Code:
    $rand round(rand(1,x)); //Change x to number of columns in table
    $sql mysql_query("SELECT field$rand FROM table...");
    //Note - UNTESTED 
    Sketch
    Aaron Brazell
    Technosailor



  5. #5
    SitePoint Member
    Join Date
    May 2002
    Location
    Calgary
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    I am actually already doing the exact same thing, except using vbscript because I use ASP. I am looking for a way to do it in pure mysql, to avoid the extra coding outside the statement. Thanks for trying though

  6. #6
    SitePoint Addict EvilDoppler's Avatar
    Join Date
    Dec 2001
    Location
    Perth, Australia
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    not really

    Hey again,

    uhmm..according to what you wrote in your original message

    My original statement: (I am using asp)
    "SELECT field1 FROM tablename WHERE username <> 'value' AND field1 <> '' ORDER BY RAND() limit 1"
    You're using the LIMIT 1..which basically just tells the sql to search from row 1 from where it finds the first unique/distinct result. MySQL will continue to search through the table till it doesn't find any further results.
    http://www.mysql.com/doc/L/I/LIMIT_optimisation.html

    That explains how LIMIT works with mysql.

    I think I hit my head on the wall with your question to start with but have looked a bit further into it.

    Since MySQL doesn't use recordsets it can be rather hard/impossible to select only between the first 5 rows without having an autonumbered row id to help you with.

    I'll look into it further and see what i can come up with.

    but i think that you could couple the rand() limit with a count statement..though seem to remember that count will kinda ruin the limit statement...hmmm i'll sort this out...can't believe i don't have any snippets or notes on something like that
    Last edited by EvilDoppler; May 29, 2002 at 07:23.
    * Due to the last tax raise our prices has risen
    * - answers w/o thought $1 - answers w/ thought $3
    * - correct answers $10
    * - Dumb stares are still free

  7. #7
    SitePoint Member
    Join Date
    May 2002
    Location
    Calgary
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post

    Okay now your just lost, lol

    I am using order by rand() limit 1 for a random row. What I am trying to do is choose a random column as well.

    For the time being, I am using vbscript to choose a number between 1 and 5, and then my statement looks like this:

    vbcode to generate a number between 1 and 5 called 'randnum'

    "Select field" & randnum & " from tablename where username <> '' AND field" & randnum & " <> '' ORder by rand() limit 1"

    Understand now?

    I am looking for a way to select a random column using ONLY a mysql statement, intead of having to incorporate my vbscript code into it.

  8. #8
    SitePoint Addict EvilDoppler's Avatar
    Join Date
    Dec 2001
    Location
    Perth, Australia
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    arggfff

    yeah..darn i'm lost..lol...

    but i see what you mean now. uhmmm...i'm not sure that's entirely possible but will look into it.

    sorry i can't be of any help (and my far-fetched drug-abused mind's ramblings earlier on)
    * Due to the last tax raise our prices has risen
    * - answers w/o thought $1 - answers w/ thought $3
    * - correct answers $10
    * - Dumb stares are still free

  9. #9
    SitePoint Member
    Join Date
    May 2002
    Location
    Calgary
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I hope it's possible. I'm a perfectionist when it comes to certain things. I just hate using vbscript when there is the possibility of a mysql standalone statement. Thanks for looking into it.

  10. #10
    SitePoint Addict EvilDoppler's Avatar
    Join Date
    Dec 2001
    Location
    Perth, Australia
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation urrgghh

    Hey again,

    i know exactly what you mean when you say that you'd rather have the sql query perform everything instead of relying on third-part scripts to do it for you.

    Been looking into the question a bit further and everybody i've asked this far (who uses mysql) has said that it's not fesable (incorrect spelling perchance ?) since mysql doesn't work with recordsets - that was also my initial thought on the issue but wouldn't have offered that bit of negative advice before knowing 100% that it was true.

    Sorry about that, find a way around it and as long as it works - who cares!!

    cheers
    * Due to the last tax raise our prices has risen
    * - answers w/o thought $1 - answers w/ thought $3
    * - correct answers $10
    * - Dumb stares are still free

  11. #11
    SitePoint Member
    Join Date
    May 2002
    Location
    Calgary
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool

    Okay. Thanks for all the help, I appreciate it. I will just bite the bullet and keep using vbscript along with the statement.



Bookmarks

Posting Permissions

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