HELP: Getting a row only once in MySQL

Hello, I have table with multiple rows. They are each labeled with a separate id. I am randomly calling rows. How can I prevent a row from being randomly selected twice?

Thanks in advance

 

take a additional true / false field say check with default value false for all rows

After you select a row make the check field true and select only those which are false

Regards

Alex

 

While that could work, the problem is if I change the values, other users will get those values. Maybe I have to use cookies?

 

oh you want the selection seperate for each user?

In that case its lil complicated

Regards

Alex

 

For that you may need to introduced a new column (say counter) which will save that how many times this row has been displayed. Steps to do this

1. Select some random rows ORDER BY counter DESC.
2. Increment the counter against those select rows ids

 

But if I did that, other users would be affected by the column changing. I want each user to be able to load each row once randomly. Would I have to use cookies to achieve this?

 

You may need to use cookies or sessions depending upon your system.

 

I don't know how much resources this would consume (depends on the size of your table), but how about using temporary tables? You can use the previous solution by mwasif to increase a counter per chosen row. That way mysql handles the creation / deletion of the temp table for each user (when the session is over, temp table is removed automatically by mysql and each user gets its own temp table).

From http://dev.mysql.com/doc/refman/5.0/en/create-table.html:

Hope it helps!

 

In the PHP application, each connection is treated as a session. Connection will be closed once the page execution is completed hence it will loose the temporary table.

Temporary table is not a solution at all in this scenario.

 

I still need help! Is there a way that I can use cookies to solve this?

 

I may be able to help, I just need more information about the application and how the random row is used. I have a couple of options, which may help or at least point to in a direction to solve the problem. One question that comes to mind is this per session or do you need to persist across multiple visits?

 

The random row's query is like this:

$query  = "SELECT id FROM src ORDER BY RAND() LIMIT 1";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$id = $row['id'];

PHP:

No, I don't need it to persist across multiple visits. It would probably be ideal, but I don't need that to happen if its too difficult.

 

May I ask how many rows are in the source table? Just thinking of other options. Off the top of my head, I am thinking that you can save the ID(s) to the session or other storage. Change the SQL statement to select ID(s) where the ID is NOT IN the rows in the session or other storage.

 

There will be quite a few as its constantly updating and being added to.

OK, I thought of this as well, but how should I store the IDs? I thought of using cookies, but how do I store multiple IDs? A different cookie for each ID? That would be a lot. I have to know how to store these IDs.

Thanks for helping out btw.

 

If you want to use cookies as the storage mechanism, you would re-write (update) the cookie contents each time you select a new ID number or as you suggested create a cookie array. I myself would keep the values in session, just a consideration.

Cookie and arrays from PHP.NET:

<?php
// set the cookies
setcookie("cookie[three]", "cookiethree");
setcookie("cookie[two]", "cookietwo");
setcookie("cookie[one]", "cookieone");

// after the page reloads, print them out
if (isset($_COOKIE['cookie'])) {
foreach ($_COOKIE['cookie'] as $name => $value) {
echo "$name : $value <br />\n";
}
}
?>

 

How would I do it in sessions? I might go with that method instead.

 

Here is a simple example:

session_start(); // start the PHP session
$_SESSION['id'] = $id; // store the random id to session
echo "ID = ".$_SESSION['id']; // retrieve data from session
session_destroy(); // destroy the PHP session

Again, just a crude example. For your specific requirements, I would use an array and save it to session. I hope this helps. Also, please refer to PHP documentation as I am a .NET developer and new to PHP

 

OK, I knew that, but its the array business that I'm having trouble with. I would have to store each id in an array. So the array would always be expanding. Then I would have to have a query where the id isn't one of the id's in the array. This is what I'm having trouble with, it requires contemplation and it isn't in the PHP documentation.

 

If you do not want to use an array, then check to see if the session variable is set and concatenate the new ID, i.e. build a comma-delimited list. No array manipulation required.

<?php
session_start();
if(isset($_SESSION['id']))
$_SESSION['id'] = $_SESSION['id'].",".$id;
else
$_SESSION['id'] = $id;
echo "ID(s) = ". $_SESSION['id'];
?>

Hope this helps!

 

OK thanks, but how would the query work?