SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Random PHP MySQL Query

    I am trying to use a PHP script I came across to generate random images from a mysql database.

    The script is not working and the only part of the script where I don't understand the mechanics is the mysql query which is as follows:

    PHP Code:
    $result mysql_query("SELECT *, jrandom*0+rand() as random FROM pictures ORDER BY random LIMIT 1") or die ("No Results"); 
    I keep getting "No Results". Does anyone know if something is wrong with this mysql query or does it seem right?
    Last edited by frankdux; May 29, 2004 at 14:08.

  2. #2
    SitePoint Member
    Join Date
    Apr 2002
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by frankdux
    I am trying to use a PHP script I came across to generate random images from a mysql database.

    The script is not working and the only part of the script where I don't understand the mechanics is the mysql query which is as follows:

    PHP Code:
    $result mysql_query("SELECT *, jrandom*0+rand() as random FROM pictures ORDER BY random LIMIT 1") or die ("No Results"); 
    I keep getting "No Results". Does anyone know if something is wrong with this mysql query or does it seem right?
    Wouldn't this work?

    PHP Code:
    $result mysql_query("SELECT * FROM pictures ORDER BY rand() LIMIT 1") or die ("No Results"); 

  3. #3
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by baze22
    Wouldn't this work?
    PHP Code:
    $result mysql_query("SELECT * FROM pictures ORDER BY rand() LIMIT 1") or die ("No Results"); 
    Yes, it would, but I think there is something in the select statement the way it is, that prevents a repeat selction. I think it's the part that says ", jrandom*0+rand()" but that's also the part that seems to be causing the problem.

  4. #4
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Admin, why was this thread moved? (from PHP to MySQL). It involves PHP and MySQL so what is the problem with it being in either section?

  5. #5
    SitePoint Member
    Join Date
    Apr 2002
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by frankdux
    Admin, why was this thread moved? (from PHP to MySQL). It involves PHP and MySQL so what is the problem with it being in either section?
    I'm guessing it was moved because it was more of a MySQL question than a PHP question. As for the:
    Code:
    jrandom*0+rand()
    I'm not sure what jrandom is. Is that a field in your database? Regardless the expression would always equal rand(), because the jrandom*0 would always equal 0 and then be added to rand().

    I don't understand the part about preventing a "repeat selection". Do you mean that it would prevent the same image from coming up twice in a row?

  6. #6
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by baze22
    I'm guessing it was moved because it was more of a MySQL question than a PHP question. As for the:
    Code:
    jrandom*0+rand()
    I'm not sure what jrandom is. Is that a field in your database? Regardless the expression would always equal rand(), because the jrandom*0 would always equal 0 and then be added to rand().

    I don't understand the part about preventing a "repeat selection". Do you mean that it would prevent the same image from coming up twice in a row?
    Yes, preventing the same image from coming up twice is what I meant. I was just guessing that's what it did, because where I've seen this script being used, the same image never comes up twice. There is nothing else in the rest of the script that could do this.

    Here is the full script:
    PHP Code:
    <?php 
    for ($i 1$i <= 10$i++) { 
    $result mysql_query("SELECT *, jrandom*0+rand() as random FROM pictures ORDER BY random LIMIT 1") or die ("No Results"); 
    $pic   mysql_fetch_array($result); 
        
    $picName  $pic["pic_name"]; 
        
    $urlTitle $pic["url_title"]; 
        
    $picThumb $pic["thumb_loc"]; 
        
    $tHeight  $pic["thumb_height"]; 
        
    $tWidth   $pic["thumb_width"]; 
        print 
    "<p><a href=\"http://www.yourdomain.com/photos/$urlTitle\"><img src=\"$picThumb\" heig 
    ht=\"
    $tHeight\" width=\"$tWidth\"></a>\n"
        print 
    "<br />&nbsp;<a href=\"http://www.yourdomain.com/photos/$urlTitle\">$picName</a></p>\n 
    "


    ?>

  7. #7
    SitePoint Member
    Join Date
    Apr 2002
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by frankdux
    Yes, preventing the same image from coming up twice is what I meant. I was just guessing that's what it did, because where I've seen this script being used, the same image never comes up twice. There is nothing else in the rest of the script that could do this.

    Here is the full script:
    PHP Code:
    <?php 
    for ($i 1$i <= 10$i++) { 
    $result mysql_query("SELECT *, jrandom*0+rand() as random FROM pictures ORDER BY random LIMIT 1") or die ("No Results"); 
    $pic   mysql_fetch_array($result); 
        
    $picName  $pic["pic_name"]; 
        
    $urlTitle $pic["url_title"]; 
        
    $picThumb $pic["thumb_loc"]; 
        
    $tHeight  $pic["thumb_height"]; 
        
    $tWidth   $pic["thumb_width"]; 
        print 
    "<p><a href=\"http://www.yourdomain.com/photos/$urlTitle\"><img src=\"$picThumb\" heig 
    ht=\"
    $tHeight\" width=\"$tWidth\"></a>\n"
        print 
    "<br />&nbsp;<a href=\"http://www.yourdomain.com/photos/$urlTitle\">$picName</a></p>\n 
    "


    ?>
    If you're wanting 10 pictures in random order with none repeated just do 1 query:
    Code:
    $result = mysql_query("SELECT * FROM pictures ORDER BY rand() LIMIT 10") or die ("No Results");
    and then process the 10 resulting records.

  8. #8
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by baze22
    If you're wanting 10 pictures in random order with none repeated just do 1 query:
    Code:
    $result = mysql_query("SELECT * FROM pictures ORDER BY rand() LIMIT 10") or die ("No Results");
    and then process the 10 resulting records.
    I tried the SELECT statement the way you suggested and it still does repeat. Any other ideas?... or maybe I'm doing something wrong.

  9. #9
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by frankdux
    I tried the SELECT statement the way you suggested and it still does repeat. Any other ideas?... or maybe I'm doing something wrong.
    Try using DISTINCT.

    Code:
    SELECT DISTINCT * FROM pictures ORDER BY RAND() LIMIT 10
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    what is jrandom???

    you say "it" still repeats, what is "it"?

    do you have an auto_increment column in that table?

    if so, you won't need SELECT DISTINCT *
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    what is jrandom???
    you say "it" still repeats, what is "it"?
    do you have an auto_increment column in that table?
    if so, you won't need SELECT DISTINCT *
    I didn't know what jrandom is, that's part of what I was trying to figure out.
    When I say "it", I am referring to pictures. I made a database with the following:
    1- picture description
    2- url to picture
    3 -url to picture thumbnail
    4 -thumbnail height
    5- thumbnail width

    I don't have an auto_increment column in the table, but I'll add one if that seems like a good idea.

    Basically what I am trying to do is select rows from a MySQL database randomly (which I know how to do). The tough question is, say you are selecting 5 rows for instance- how do you prevent repeat selections?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    prevent repeat selections using ORDER BY rand() LIMIT 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    prevent repeat selections using ORDER BY rand() LIMIT 5
    I did do that, but it still repeats.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    what is "it"?

    because you fer sure won't get repeated rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    what is "it"?
    because you fer sure won't get repeated rows
    Here is the page where I am trying to do this:
    http://www.zaptank.com/random_photos/random.php
    If you refresh a few times you can see you get repeats.


    When I say "it", I am referring to pictures. I made a database (see below) where each row has the path a picture and some related information.

    PHP Code:
    mysqlselect from pictures;
    +---------------------------+-----------------------+------------------------------------------------------------------+--------------+-------------+
    pic_name                  url_title             thumb_loc                                                        thumb_height thumb_width |
    +---------------------------+-----------------------+------------------------------------------------------------------+--------------+-------------+
    ParisThe Mona Lisa      | /louvre/image_6.html  | [url]http://www.frankiehots.com/photos/louvre/thums/th_monalisa.jpg[/url]   | 84           | 128         |
    ParisEros and Psyche    | /louvre/image_4.html  | [url]http://www.frankiehots.com/photos/louvre/thums/th_erospsyche.jpg[/url] | 84           | 128         |
    ParisPyramid            | /louvre/image_1.html  | [url]http://www.frankiehots.com/photos/louvre/thums/th_pyramid.jpg[/url]    | 84           | 128         |
    ParisLouvre Ceiling     | /louvre/image_10.html | [url]http://www.frankiehots.com/photos/louvre/thums/th_ceiling2.jpg[/url]   | 84           | 128         |
    ParisInside The Pyramid | /louvre/image_2.html  | [url]http://www.frankiehots.com/photos/louvre/thums/th_inpyramid.jpg[/url]  | 84           | 128         |
    ParisLouvre Corridor    | /louvre/image_3.html  | [url]http://www.frankiehots.com/photos/louvre/thums/th_louvre1.jpg[/url]    | 84           | 128         |
    ParisLouvre Ceiling 2   | /louvre/image_5.html  | [url]http://www.frankiehots.com/photos/louvre/thums/th_ceiling.jpg[/url]    | 84           | 128         |
    ParisEygptian Exhibit   | /louvre/image_8.html  | [url]http://www.frankiehots.com/photos/louvre/thums/th_egyptian.jpg[/url]   | 84           | 128         |
    +---------------------------+-----------------------+------------------------------------------------------------------+--------------+-------------+
    8 rows in set (0.00 sec

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    cool

    nice pics, by the way

    when you run SELECT * ORDER BY rand() LIMIT 5
    it is impossible to get a duplicate row

    there are only two ways you might get what looks like
    a duplicate, and they are:

    1. there are duplicate rows in your table, which you
    can easily confirm or deny by dumping the table

    2. your php script has a problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    cool

    nice pics, by the way

    when you run SELECT * ORDER BY rand() LIMIT 5
    it is impossible to get a duplicate row

    there are only two ways you might get what looks like
    a duplicate, and they are:

    1. there are duplicate rows in your table, which you
    can easily confirm or deny by dumping the table

    2. your php script has a problem
    Thanks for the compliments on the pics. I verified there are no duplicate rows in the table. Here is the latest version of the php script I am using:

    PHP Code:
    <?php 
    for ($i 1$i <= 5$i++) { 
    $result mysql_query("SELECT DISTINCT * FROM pictures ORDER BY rand() LIMIT 5") or die ("No Results"); 
    $pic   mysql_fetch_array($result);  
        
    $picName  $pic["pic_name"]; 
        
    $urlTitle $pic["url_title"]; 
        
    $picThumb $pic["thumb_loc"]; 
        
    $tHeight  $pic["thumb_height"]; 
        
    $tWidth   $pic["thumb_width"]; 
        print 
    "<p><a href=\"http://www.frankiehots.com/photos/$urlTitle\"><img src=\"$picThumb\" heig 
    ht=\"
    $tHeight\" width=\"$tWidth\"></a>\n"
        print 
    "<br />&nbsp;<a href=\"http://www.frankiehots.com/photos/$urlTitle\">$picName</a></p>\n 
    "



    ?>

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    first of all, i'm a coldfusion guy, but second,
    even i can see what the problem here is

    you are running the SELECT 5 times, and each time,
    you are displaying only one row from it

    dude, run the query just once, and display all 5 rows

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    first of all, i'm a coldfusion guy, but second,
    even i can see what the problem here is

    you are running the SELECT 5 times, and each time,
    you are displaying only one row from it

    dude, run the query just once, and display all 5 rows

    Ok thanks, now I just have to figure out how to do that.
    Last edited by frankdux; May 31, 2004 at 08:25.

  20. #20
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy is right, you are running the same query 5 times and that's what causing the problem.

    Use a while construct instead of for.

    PHP Code:
    $result mysql_query("SELECT * FROM pictures ORDER BY rand() LIMIT 5") or die ("No Results");
    while (
    $pic mysql_fetch_array($result)) {

        
    // display rows


    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  21. #21
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, wait I think I got it. It seems to work. I hope this isn't a dumb way to do it. I basically just put the for loop after the select statement. Here it is:
    PHP Code:
     <?php 
    $result 
    mysql_query("SELECT DISTINCT * FROM pictures ORDER BY rand() LIMIT 5") or die ("No Results"); 
    for (
    $i 1$i <= 5$i++) { 
    $pic   mysql_fetch_array($result);  
        
    $picName  $pic["pic_name"]; 
        
    $urlTitle $pic["url_title"]; 
        
    $picThumb $pic["thumb_loc"]; 
        
    $tHeight  $pic["thumb_height"]; 
        
    $tWidth   $pic["thumb_width"]; 
        print 
    "<p><a href=\"http://www.frankiehots.com/photos/$urlTitle\"><img src=\"$picThumb\" heig 
    ht=\"
    $tHeight\" width=\"$tWidth\"></a>\n"
        print 
    "<br />&nbsp;<a href=\"http://www.frankiehots.com/photos/$urlTitle\">$picName</a></p>\n 
    "



    ?>
    http://www.zaptank.com/random_photos/random.php

  22. #22
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know if that would work, though. Using while is much easier and faster if I'm not mistaken.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  23. #23
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Toly
    I don't know if that would work, though. Using while is much easier and faster if I'm not mistaken.
    Toly, you are right, that is the better way to do it. Thanks a lot r937 and Toly, I have it working now.

    http://www.zaptank.com/random_photos/random.php


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
  •