Non-repeating random projects

[dongabonga]

Hi all...

 

This is for my design portfolio website which has only one page and is supposed to display one project at a time with its details... I want random projects to appear upon each visit or refresh or click of the 'Next Random Project' button...

 

I'm a newbie at PHP and thru extensive search and research i have conjured up the following code which is doing the needful, only to some extent... Irrelevant CSS/HTML has been excluded from the following code...

 

<?php include("login.php");

$query = "SELECT * FROM Projects, ORDER by rand() LIMIT 1 ";

$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {

$title = $row['project_title'];
$client = $row['client_name'];
$category = $row['category_name'];
$year = $row['project_year'];
$location = $row['clientlocation_name'];
$details = $row['project_details'];
$bg = $row['project_imageurl'];

}

mysql_free_result($result);
mysql_close();
?>

 

 

Problem is that the random feature is proving to be not THAT random and a lot of projects are frequently repeated...

 

I think this could be sorted out by storing the randomly viewed projects in a PHP session so that they are not repeated during that particular session...

 

Total number of projects in the MySQL database would be around 100 so lets suppose a user has viewed all 100 projects appearing in random order, it would be ideal if they start appearing randomly again...

 

Any code snippets would help this newbie...

 

Thanks in advance!

[jcbones]

rand() has it's problems, and is notoriously slow.  Is there an auto-increment primary key by chance?  This would be unique for each row in the database, and would make it much easier to track which rows had been selected, as well as making it easier to select a new row.

 

[WebStyles]

how about this (it's just an idea, that i've used before and worked fine for me):

 

Grab all id's from database and place in a session array if array doesn't exist yet: (assuming you unique id field is called `id`)

if(!isset($_SESSION['ids']) || empty($_SESSION['ids']){
   $_SESSION['ids'] = array();
   $q = mysql_query("SELECT `id` FROM `Projects`");
   while ($row = mysql_fetch_array($result)) {
      $_SESSION['ids'][] = $r['id'];
   }
}
// reorder array in random fashion every time the page is loaded (why the hell not?)
shuffle($_SESSION['ids']);
//grab an element from end of array while removing it from array (so it never repeates)
$id = array_pop($_SESSION['ids']);
// now just use that id to retrieve project from database.
// when $_SESSION['ids'] is empty, it should reload again in a different order.

 

* untested code, may contain typos and need some tweeking.

 

hope it helps

[jcbones]

WebStyles, looking at his code, I'm not sure he has an `id` field.

[requinix]

Normally I hate using ORDER BY RAND() LIMIT $N but sometimes it can be very handy.

 

Track two values in the session: a random number and an offset...

if (!isset($_SESSION["project rand n"])) $_SESSION["project rand n"] = rand();
if (!isset($_SESSION["project rand offset"])) $_SESSION["project rand offset"] = 0;

$query = "SELECT * FROM Projects ORDER BY RAND({$_SESSION["project rand n"]}) LIMIT {$_SESSION["project rand offset"]}, 1";
$_SESSION["project rand offset"]++;

The trick is that RAND($N) will always produce the same sequence of numbers for a given $N.

 

 

A better solution would be, say, to append a random list of projects, keyed to the user, to a table every time the list starts up.

INSERT INTO Projects_random (userID, projectID) SELECT $userID, ID FROM Projects ORDER BY RAND()

Then store an offset and do a SELECT+JOIN. Advantages: the list is randomized once and you can access them in sequential order. Disadvantages: need an extra table, and if you don't clean up records (eg, using a date/time field in the table to tell when the items were added) then the table will get huge.

 

rand() has it's problems, and is notoriously slow.

Really? For starters it's not meant to be a "good" RNG. It's fairly fast; the perceived slowness comes from trying an ORDER BY RAND() LIMIT 1 on a large table.

[WebStyles]

@jcbones yeah, maybe not... but it could still work if the project names are unique or something.

[dongabonga]

Thanks for looking into this guys...

 

@jcbones: Yes there is an auto-increment primary key 'project_id'

@WebStyles: After some tweaking i tried what you suggested and it now the page gives me an error: "Query was empty"

 

Following is what i tried:

 

<?php include("login.php");

session_start();

if(!isset($_SESSION['ids']) || empty($_SESSION['ids']))
{
$_SESSION['ids'] = array();
$q = mysql_query("SELECT project_id FROM Projects, ORDER by rand() LIMIT 1");
$result = mysql_query($q) or die(mysql_error()); 
while ($row = mysql_fetch_array($result))
	{
	$_SESSION['ids'][] = $row['project_id'];
	}
}
// reorder array in random fashion every time the page is loaded (why the hell not?)
shuffle($_SESSION['ids']);
//grab an element from end of array while removing it from array (so it never repeates)
$id = array_pop($_SESSION['ids']);
// now just use that id to retrieve project from database.
// when $_SESSION['ids'] is empty, it should reload again in a different order.

echo $id;

mysql_free_result($result);
mysql_close();
?>

 

I wanted to see which 'project_id' comes up hence i have 'echo $id;' ... I don't know if its the correct way or not...

 

Any ideas how i can get the page to display the if id of each project that is randomly chosen from the db?

[WebStyles]

you wont be able to pull out all the project ids if you don't remove LIMIT 1 from your sql query.