How to pick randomly from a table but not include the last picked entry

[GlenRogers]

Hi
I have a php header file that displays a random image from a mysql table each time the page is reloaded. I have done this just using rand() in the query. But doing it like this means that the same image could come up 3,4,5 times in a row! How would i make it random but never have an image come up twice in a row?

This is my file

<?php
   $output = '<div class="header">';
   $output .= '<img src="../images/siteimages/logo.png"alt="Logo image" />';
   $output.= '<div id="headerrotator">';
   include 'connect.php';
   $q ="SELECT * FROM headerrotatorimage WHERE rotator = 1 AND status = 1 ORDER BY RAND() LIMIT 1" or die (mysqli_error($link));
   $result = $link->query($q);

   while($row = mysqli_fetch_array($result)){
   $output .= "<img src='{$row['filename']}' alt='{$row['name']}. image' />";
   }
   $output.= '</div>';
   $output.= '<div id="wishlistmenu">';
   $output.= '<ul>';
   $output .= '<li><a href="wishlist.php">Login/Register</a> </li>';
   $output .= '<li><a href="wishlist.php">Wish List</a> </li>';
   $output .= '<li><a href="wishlist.php">Reviews/Testing</a> </li>';
   $output .= '<li><a href="wishlist.php">View Cart</a> </li>';
   $output .= '<li><a href="wishlist.php">Terms/Delivery</a> </li>';
   $output .= '<li><a href="wishlist.php">Contact Us</a> </li>';
   $output.= '</ul>';
   $output.= '</div>';
   $output .= '</div>';

   echo $output;
?>

Thanks for looking.................

[paulkd]

Untested.
Assuming your table contains an id column.
Set a cookie of the id of the last image displayed.
If this is first time (cookie doesn't exist) simply set a $cookieId of 1.

Amend your query to

$q ="SELECT * FROM headerrotatorimage WHERE rotator = 1 AND status = 1 and id <> $cookieId ORDER BY RAND() LIMIT 1" or die (mysqli_error($link));

[GlenRogers]

Where and how do i set the cookie?

I take it that it needs to be set before the query?

[GlenRogers]

This is what I have now.

   $output = '<div class="header">';
   $output .= '<img src="../images/siteimages/logo.png"alt="Logo image" />';
   $output.= '<div id="headerrotator">';
   include 'connect.php';
   $cookieid = 1;
   $q ="SELECT * FROM headerrotatorimage WHERE rotator = 1 AND status = 1 AND id <> $cookieid ORDER BY RAND() LIMIT 1" or die (mysqli_error($link));
   $result = $link->query($q);
   while($row = mysqli_fetch_array($result)){
   $cookieid = setcookie($row['id']);
   $output .= "<img src='{$row['filename']}' alt='{$row['name']}. image' />";
   }
   $output.= '</div>';
   $output.= '<div id="wishlistmenu">';
   $output.= '<ul>';
   $output .= '<li><a href="wishlist.php">Login/Register</a> </li>';
   $output .= '<li><a href="wishlist.php">Wish List</a> </li>';
   $output .= '<li><a href="wishlist.php">Reviews/Testing</a> </li>';
   $output .= '<li><a href="wishlist.php">View Cart</a> </li>';
   $output .= '<li><a href="wishlist.php">Terms/Delivery</a> </li>';
   $output .= '<li><a href="wishlist.php">Contact Us</a> </li>';
   $output.= '</ul>';
   $output.= '</div>';
   $output .= '</div>';

   echo $output;

But this is still doing the same, showing the same image multiple times in succesion.

You could use a session as opposed to a cookie, but as soon as the session is killed, you could have the same image show many times as you revisit.
The cookie sounds like the obvious answer if session isn't reliable in this instance.

1.check for a cookie
-if set - use this in your sql as directed previously - so retrieve a random image but not the one stored in the cookie
-if not set just retrieve use a random image
2.store the id of used image in cookie [setcookie()]

[GlenRogers]

Do I check for the cookie before the query? In the while loop? I'm quite confused on this now

Before writing the query, otherwise you don't know whether to include it in your SQL.

[paulkd]

I've changed cookieid to fileid seems more appropriate.

   $output = '<div class="header">';
   $output .= '<img src="../images/siteimages/logo.png"alt="Logo image" />';
   $output.= '<div id="headerrotator">';
   include 'connect.php';
   $fileid = isset($_COOKIE['fileid']) ? $_COOKIE['fileid'] : 1;
   $q ="SELECT * FROM headerrotatorimage WHERE rotator = 1 AND status = 1 AND id <> $fileid ORDER BY RAND() LIMIT 1" or die (mysqli_error($link));
   $result = $link->query($q);
   while($row = mysqli_fetch_array($result)){
   setcookie('fileid',$row['id']);
   $output .= "<img src='{$row['filename']}' alt='{$row['name']}. image' />";
   }
   $output.= '</div>';
   $output.= '<div id="wishlistmenu">';
   $output.= '<ul>';
   $output .= '<li><a href="wishlist.php">Login/Register</a> </li>';
   $output .= '<li><a href="wishlist.php">Wish List</a> </li>';
   $output .= '<li><a href="wishlist.php">Reviews/Testing</a> </li>';
   $output .= '<li><a href="wishlist.php">View Cart</a> </li>';
   $output .= '<li><a href="wishlist.php">Terms/Delivery</a> </li>';
   $output .= '<li><a href="wishlist.php">Contact Us</a> </li>';
   $output.= '</ul>';
   $output.= '</div>';
   $output .= '</div>';

   echo $output;

[GlenRogers]

Thank you guys.
paulkd that worked a charm
Thanks

For completeness, I'd have done something very similar:

$fileClause = isset($_COOKIE['fileid']) ? ' AND id != ' . intval($_COOKIE['fileid']) : '';
$q ="SELECT * FROM headerrotatorimage WHERE rotator = 1 AND status = 1$fileClause ORDER BY RAND() LIMIT 1" or die (mysqli_error($link));

Thereby, you don't get file #1 every time you vclear your cookies or visit the site for the first time.

[GlenRogers]

Thanks for that Al.
I didn't think about it starting at id 1. Im using one table for 3 different image container, for the first one rotator = 1, the second it is 2, the third it is 3. id 1 is always going to belong to rotator 1