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 3 of 3
  1. #1
    New Coder
    Join Date
    Mar 2012
    Location
    Leicester
    Posts
    21
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Pick x number of rows from mysql table and display with php

    Hi all,
    i am currently designing the welcome page for my website and i would like to display random images from my database on the home screen.
    i have many images stored in my database and each time someone visits the site i would like 4 random pictures to be selected from my db and displayed. i have heard about the rand() php function but heard that for large databases this is a bad one to use.
    i really am stumped on this, thanks in advance to anyone who can help

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,733
    Thanks
    80
    Thanked 4,516 Times in 4,480 Posts
    "large" means hundreds of thousands or millions of records.

    I doubt seriously that you have that many images in use.

    I would feel comfortable using RAND() with up to a few thousand records, though if your tables are "wide" (that is, if there are many many fields in each row of the table) there are some tricks you might want to use to improve the efficiency of your queries.
    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.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,733
    Thanks
    80
    Thanked 4,516 Times in 4,480 Posts
    For example, if you have an ID column as your PRIMARY KEY, you could do:
    Code:
    SELECT * FROM yourtable
    WHERE id IN (
        SELECT id FROM yourtable
        ORDER BY RAND()
        LIMIT 4 )
    That way the ORDER BY part of the query only has to fetch the IDs (presumably from the index, without touching the table, per se), pick 4 of them at random, and only then do you pick 4 full records from the entire table.
    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.


  •  

    Posting Permissions

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