SQL Result Random loop through

Question

I have a SQL database that contains a field. I can get all of the elements in that field by:

      $result=mysql_query($sql);
      while($row=mysql_fetch_array($result)){
         $ftype  =$row['ftype']; //name of field is 'ftype'
         print $ftype}  //do something

I want a random element to be printed out each time the page is opened/refreshed.

Knowing that the result is an array containing the information i want, I want to randomly choose an element of the array. Also, if there are N elements in the array, I want to choose all N elements exactly once before I show any element again for second time. The process repeats itself. I know how to code something like this in java or python, but I don't think that's the way I should go.

I think I need to use javascript, but I'm just not sure what technology to use.

UPDATE:

Patrick's idea seems to be exactly what i was looking for. I thought I would share what I have now and maybe you can suggest optimization. I hope the intent in the code is obvious.

<?
session_start();
if (!isset($_SESSION['count']) || !isset($_SESSION['randomArray'])) {
      $count = 0;
      $randomArray = array();
      $sql="SELECT youtubeurl FROM Foodlist";
      $result=mysql_query($sql);
      while($row=mysql_fetch_array($result)){
           array_push($randomArray,$row['youtubeurl']);
      }
      shuffle($randomArray);
      $_SESSION['randomArray'] = $randomArray;
      $_SESSION['count'] = $count; 
} elseif ($_SESSION['count'] >= sizeof($_SESSION['randomArray'])){
   $_SESSION['count'] = 0;
   $randomArray = $_SESSION['randomArray'];
   shuffle($randomArray);
   $_SESSION['randomArray'] = $randomArray;
} else{
   $randomArray = $_SESSION['randomArray'];
   $count = $_SESSION['count'];
   echo $randomArray[$count];
   $_SESSION['count']++;
}
?>

I don't think this is going to involve Javascript!

Oliver Charlesworth
– Oliver Charlesworth

2011-06-23 00:26:27 +00:00
Commented Jun 23, 2011 at 0:26 — Oliver Charlesworth
– Oliver Charlesworth, Commented Jun 23, 2011 at 0:26

Patrick Perini · Accepted Answer · 2011-06-23 00:37:26Z

1

You can do this without Javascript, however you'll need to open/maintain a session.

Pseudocode:

data = data_from_mysql()

choice = random.choice(data, exclude = SESSION['choices'])
SESSION['choices'].append(choice)

print choice

if len(SESSION['choices']) == len(data):
    SESSION['choices'] = []

answered Jun 23, 2011 at 0:37

Patrick Perini

22.6k12 gold badges62 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

scifirocket Over a year ago

i dont know anything about sessions. can you point me to some good information so that I know i'm looking at the right stuff?

Patrick Perini Over a year ago

Absolutely. Check this out, it's the PHP manual for Sessions. They're pretty straightforward.

scifirocket Over a year ago

Do you think there is a more server-sided approach to this? I don't really want to create cookies unless I have to.

Patrick Perini Over a year ago

This is about as server-sided as it gets. There are no cookies necessitated by sessions (although they are often used in tandem). session.start(); if(!is_set($_SESSION['choices'])) { $_SESSION['choices'] = array(); } should be all you need to get started. From there, just access $_SESSION['choices'] like any other array.

scifirocket Over a year ago

I forgot i was in PHP for a minute.

|

JStead · Accepted Answer · 2011-06-23 03:33:08Z

0

If you want a random result let the dbms take care of it. Add this to the bottom of your sql query.

ORDER BY RAND()
LIMIT 1

answered Jun 23, 2011 at 3:33

JStead

1,73011 silver badges12 bronze badges

1 Comment

scifirocket Over a year ago

that would work, but I have a constraint on the amount of times a result can appear randomly. everything has to appear at least once before it can show up again. I bet you can't guess what type of content would need this kind of structure! (Hint: It's ads)

Collectives™ on Stack Overflow

SQL Result Random loop through

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related