Get Random Data from mysql Table

[intodesi]

Hello,

What I am trying to do is get semi random data from a MySql table.

 

I have a database named kidpatrol, which has 4 tables under it, kids, parents, cards, and tracking.

 

The first thing in my project that I would like to complete, would be figuring out how to grab a semi random row from the cards table, based on user selection and past events.

 

in the cards table, there are 4 rows, card_id, card_diff, card_name, card_text.

 

card_diff is for card difficulty, what i need to be able to do is when I display a choice of three difficulties (which i can do) and when that specific difficulty is chosen a random card is displayed, if that card has not been displayed before. so my script has to be able to

 

1) Get a random card based on difficulty.

2) Make sure that the Card has not been used before

    a) Using the tracking table to accomplish this.

 

Tracking table has three rows. kid_id, card_id, and date.

 

Any help would be great, basically I gave a whole bunch of info, but all i need to do is figured out how to grab the data from the cards table randomly after the kid selects difficulty, and then make sure that the specific card has not been used.

 

Thanks

[Alt_F4]

Hi

 

I would do something along these lines:

 

use a query and sub query to get the data needed - off the top of my head, something like :

 

SELECT card_id, card_name, card_text FROM cards WHERE card_diff="userinput" and card_id NOT IN (SELECT card_id FROM tracking) ORDER BY RAND()

 

NB: I haven't checked this code

 

that should give you a random card with difficulty of "userinput" that hasn't been used ever (as long as you are putting the details of the cards that have been used into the tracking table).

 

you may want to alter the query to suit your needs

 

hope that points you in the right direction

[intodesi]

Very Cool thank you very much for the example, I can see how what I want to do needs to be done, i didnt know about the "ORDER BY RAND()" string.

 

Thanks again, I will post back as soon as I am able to get it tested.

[corbin]

If the table is large, order by rand() can cause a huge performance loss.  It has to generate a random value for each row, keeping all of this data in memory (unless disk writes are needed because it's so large), and then it has to sort this unindexed, and -- ironically -- random data.

[Alt_F4]

that is true,

 

like i said it was off the top of my head 

 

you could also do it like this i believe - it will work much faster:

 

SELECT * FROM Table T JOIN (SELECT FLOOR(MAX(ID)*RAND()) AS ID FROM Table) AS x ON T.ID >= x.ID LIMIT 1;

 

*courtesy of Jay Paroline*

[corbin]

Never seen that before....  Pretty clever, assuming I'm not missing anything.

[Alt_F4]

yeah i know, i keep thinking the same thing

 

wish i'd though of it