Getting record at random from database

[cafeenman]

I haven't been able to figure this one out. On my photography page, I want to display an image at random whenever someone goes to the page. It would probably be best if the image didn't change when they refresh the page, but I haven't even looked at sessions yet, so really I just want to know how to select a random record.

http://www.paulkjohnson.com/photography/

<?PHP

$db = @mysql_connect(etc);
mysql_select_db('Photography');

if ($ImageID > 0)
    {
        $query = mysql_query("SELECT Subject,Filename,Year,Caption FROM photographs WHERE ImageID = '$ImageID'");
        $BasePath = 'images/';
        $Caption = mysql_result($query,0,"Caption");
        $Filename = mysql_result($query,0,"Filename");
        $FullPath = "$BasePath$Filename";
        echo "<p align=\"center\"><img src=$FullPath></p>";

        echo "<p align=\"center\">$Caption</p>";

        $Copyright = "Copyright © ";
        $Year =mysql_result($query,0,"Year");
        $Author = " Paul K. Johnson";
        echo "<p align=\"center\">$Copyright$Year$Author</p>";
        $query = mysql_query("update photographs set Count = Count +1 where ImageID='$ImageID'");
    }
else // display a random image
    {
      $query = mysql_query("SELECT * FROM photographs");
                // pick one at random
    }
?>

I know how to display it so all I need is the next line of code.

[scoutt]

<cough>http://www.snippetlibrary.com/code.p...ge%20Galleries</cough>

also why are you querying the DB 3 times for the samething. just query it once and then use the results from that one.

[cafeenman]

Originally posted by scoutt
also why are you querying the DB 3 times for the samething. just query it once and then use the results from that one.

because I don't know what I'm doing or what any of this stuff does. I just copy and paste and if it works, then it works. I'm figuring it out as I go along. One day I'll look back and say to myself 'Damn, Paul! You really had no clue, did you?!?'

[cafeenman]

That snippet doesn't really answer my question. I want to know how to figure out how many records are in the result set and then use that number to pick a random record.

In my particular case, I'm using it to select an image from the database, but it would be useful to know how to do it whenever I need to select random data.

I need to open the database to get the copyright info and the caption as well.

[chrisjk]

All you need is a one line SQL statement to get Mysql to pick a record for you

You don't need to generate a random number, you don't need to know how many records there are or anything, this statement will do...

SELECT * FROM users ORDER BY RAND() LIMIT 1

[cafeenman]

Outstanding! Thanks.

[cafeenman]

works great. Just have to upload it. Here's my new code.

if ($ImageID > 0)
    {
        $query = mysql_query("SELECT Subject,Filename,Year,Caption FROM photographs WHERE ImageID = '$ImageID'");
    }
else
    {
    // Select an image at random
    //SELECT * FROM users ORDER BY RAND() LIMIT 1
    $query = mysql_query("SELECT Subject,Filename,Year,Caption FROM photographs ORDER BY RAND() LIMIT 1");

    }

    $BasePath = 'images/';
    $Caption = mysql_result($query,0,"Caption");
    $Filename = mysql_result($query,0,"Filename");
    $FullPath = "$BasePath$Filename";
    echo "<p align=\"center\"><img src=$FullPath></p>";

    echo "<p align=\"center\">$Caption</p>";

    $Copyright = "Copyright © ";
    $Year =mysql_result($query,0,"Year");
    $Author = " Paul K. Johnson";
    echo "<p align=\"center\">$Copyright$Year$Author</p>";
    $query = mysql_query("update photographs set Count = Count +1 where ImageID='$ImageID'"); 

[chrisjk]

Originally posted by cafeenman
Outstanding! Thanks.

no problemo!

mysql can do a lot on it's own, it has a load of functions

[scoutt]

that's what I was thinking chris, thanks, but that script will come in handy later down the road, Paul

try this

 $BasePath = 'images/';
if ($ImageID > 0)
    {
        $query = mysql_query("SELECT Subject,Filename,Year,Caption FROM photographs WHERE ImageID = '".$_REQUEST['ImageID']."' ");
    }
else
    {
    // Select an image at random
    //SELECT * FROM users ORDER BY RAND() LIMIT 1
    $query = mysql_query("SELECT Subject,Filename,Year,Caption FROM photographs ORDER BY RAND() LIMIT 1");
    }
    while ($row = mysql_fetch_array($query)){
    $Caption = $row['Caption'];
    $Year = $row['Year'];
    $FullPath = "$BasePath$row['Filename'];
    echo "<p align=\"center\"><img src=\"$FullPath\"></p>";

    echo "<p align=\"center\">$Caption</p>";

    $Copyright = "Copyright © ";
    
    $Author = " Paul K. Johnson";
    echo "<p align=\"center\">$Copyright$Year$Author</p>";
   }

    $query = mysql_query("update photographs set Count = Count +1 where ImageID='".$_REQUEST['ImageID']."'"); 

not sure why you are adding 1 to the count everytime an image gets shown but try that.

[cafeenman]

OK. Thanks. I thought that's what this was doing:

mysql_result($query,0,"Caption");

So that actually runs another query? I thought it was just the result from the query.

I'm adding to the count to see which of my photos are most popular. Sort of a super-scientific market study. I should only have it in the part of the IF statement where the viewer actually selects a photo though. The way I have it, the randomly selected photo gets counted too which won't give me accurate results.

On the other hand, if it's displayed automatically and they would have chosen it, then it wouldn't get counted. Either way, it's not really accurate, but if I see some photos getting viewed way more than others, then they probably are good candidates for sales.

Damn... off topic, but I do so many creative things and I'm a horrible salesman. I need someone to market my stuff for me. I have a lot of things that are done and ready to go and just sitting around costing storage when I could have some income.

[scoutt]

correct that is what it does, but using the fetch_array is MUCH faster than the mysql_result. so try to use that. why get teh result 3 differetn times from the same query when you can do it once and get it all.

if you want to count the number of times the picture gets clicked on then add the count query under the if.

[Professional]

Perfect timing I was just making a PHP program to radomly select a row, its cut my coding down from 40 likes to 2B) good work