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 10 of 10
  1. #1
    New to the CF scene
    Join Date
    Sep 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with displaying random records

    I have the following table:

    ID Job Category
    1 Clerk Admin
    2 Secretary Admin
    3 Typist Admin
    4 Reception Admin


    Now, I want to display 5 jobs within the Admin Category, but I want them pulled randomly from the database. ORDER BY RAND won't work because there are only 4 records. I need to display 5 records as it doesn't matter if the same record is displayed more than once. How do I do this?

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,959
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Code:
    select * from temp_table t left join temp_table t1 on t.f<> t1.f
    ORDER BY RAND()
    limit 5
    As you said, you don't care if same record appears more than once.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    temp_table? t.f? Where did those come from, Bubikol???

    And if there are only 4 records in temp_table, that will *NOT* get more than 4 records.

    One simple answer:
    Code:
    SELECT X.* FROM 
      ( SELECT 1 AS dummy, * FROM table
        UNION
        SELECT 2 AS dummy, * FROM table
      ) AS X
    WHERE X.jobcategory LIKE '%Admin%'
    ORDER BY RAND() LIMIT 5
    But it's not terribly efficient.

    I would be strongly tempted to do this in PHP/JSP/ASP code, instead.

    Read the 4 records into an array and then just pick a random array element 5 times.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,959
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Where did those come from, Bubikol???
    From my db.

    I gather f is field and t is table to enlightened people (-:


    The proof that you get what I state in that post, 4 records produces 5 (more actualy):
    Code:
    DROP TABLE IF EXISTS `temp_table`;
    CREATE TABLE IF NOT EXISTS `temp_table` (
      `latin` varchar(50) COLLATE utf8_slovenian_ci NOT NULL,
      UNIQUE KEY `latin` (`latin`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;
    
    -- Dumping data for table test.temp_table: 4 rows
    DELETE FROM `temp_table`;
    
    INSERT INTO `temp_table` (`latin`) VALUES
    	('Amanita battarrae'),
    	('Amanita ceciliae'),
    	('Amanita citrina f. alba'),
    	('Amanita crocea');
    
    select * from temp_table t left join temp_table t1 on t.latin<> t1.latin
    ORDER BY RAND()
    limit 5
    Last edited by BubikolRamios; 11-25-2012 at 11:14 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #5
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,592
    Thanks
    0
    Thanked 645 Times in 635 Posts
    Quote Originally Posted by Old Pedant View Post
    But it's not terribly efficient.

    I would be strongly tempted to do this in PHP/JSP/ASP code, instead.

    Read the 4 records into an array and then just pick a random array element 5 times.
    I agree - as more than the total number of records in the table is needed it will always be more efficient to just rread in all the entries and then select them without having to read the same record from the database more than once.

    Only when the number of records required is significantly less than the number of entries in the table would 'sorting' the table in the database be more efficient and that too becomes inefficient when you have a large enough number of records and only need to retrieve a small number.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    AHHH! My mistake. You used <> for the JOIN! I didn't read carefully enough. I automatically read it as =.

    Sneaky trick!

    Could have also done [icpde]t CROSS JOIN t1[/icode] and omitted the ON condition entirely.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New to the CF scene
    Join Date
    Sep 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post

    Read the 4 records into an array and then just pick a random array element 5 times.
    What is the PHP code to do this?

  • #8
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    PHP code isn't necessary. You've asked a mysql question and been given a mysql answer without regard to the front end application (the two don't have to be used together in other words).

    If you mean you'd like to use PHP to step through those results and point them to another page you can indeed do that.

    Do you have phpmyadmin at all? If so you can run the query above and then click on the PHP button and it will generate necessary code.

    If you don't have phpmyadmin then you can look at the mysql functions in the php manual and it will show you examples. look in the php manual under mysql_fetch_array for an example.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    No, no, Guelphdad: Read his message again.

    He would like to know how to do as Felgall suggested in post #5.

    I don't use PHP, so I'm not going to attempt an answer. (It would be trivial in ASP, where the method GETROWS() converts the entire set of records to an array, automatically. <grin/>)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,592
    Thanks
    0
    Thanked 645 Times in 635 Posts
    Quote Originally Posted by Old Pedant View Post
    It would be trivial in ASP, where the method GETROWS() converts the entire set of records to an array, automatically. <grin/>)
    In PHP mysqli_result::fetch_all also converts the entire set of records to an array, automatically (it used to be done using mysql_fetch_array in the now obsolete mysql_ interface) Presumably there's also a PDO call that does it as well for those using that in PHP.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.


  •  

    Posting Permissions

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