ORDER BY RAND() (MySQL 5.0.45)

[Errant_Shadow]

As made obvious by the title of the thread, I'm trying to retrieve a random number of rows from MySQL.

 

But more than that I'm trying to retrieve a number of random and *unique* rows from MySQL; see, I'm running into the problem where it will return identical rows. Now, I blame this mostly on the fact that I have a very small number of rows right now, but none the less there are enough there that I shouldn't -have- to see identical rows. So is there any way that I can have it return only unique rows -- never the same row more than once?

[Ken2k7]

How are you currently writing it?

[Errant_Shadow]

It's using an inner join with a bunch of WHERE clauses...

$query = 'SELECT * FROM characters, bikes WHERE characters.name != "' . $_SESSION['character'] . '" AND characters.level = "' . $racerArray[0]['level'] . '" AND bikes.class = "'. $racerArray[0]['class'] . '" ORDER BY RAND() LIMIT ' . $_POST['flaCompetition'];

 

(simplified)

$query = 'SELECT * FROM table1, table2 WHERE "this" AND "that" ORDER BY RAND() LIMIT #';

[Errant_Shadow]

Any advice at all?

[Errant_Shadow]

I know. My brain is kind of fried trying to track all of the data I'm using, but I'll try to be as simple as I can to convey my problem.

 

So what I have is an array of arrays. It is called racerArray and it is composed of all the character data for each racer participating in the race that my script runs. This is *technically* ordered by index, but the index starts at 1 rather than 0, so its easier to think about it as an associative array where each key represents a racer's position in the race, from first to last.

 

The individual characer arrays are assembled from data I fetch from MySQL and contain the character's name, account name, current experience points, etc etc, but more importantly, it contains their skill ranks, their bike's stats, and the damage value for each part (which is in it's own sub array inside the character array).

 

Additionally, I have a multidimensional associative array much in the same fashion for holding the track. It is called the trackArray and each element (starting at 1) holds the data for that individual segment, which includes the segment's length, the condition, a hazard for the racers to overcome, and an array of stat modifiers to apply to each racer's bike stats.

 

So basically, what I need to do is walk through the trackArray, and at each segment, use the difficulty mod, the length, the condition and hazard information, and the array of stat modifiers to calculate how each racer did; if they crashed, how much damage each part took on this segment, of a part broke down, and then (if they made it through the segment) how well they did in a way I can compare to each other racer.

 

Now, I have a model that does all that and worked well enough for my purposes, but i've had to strong-arm, and make work-arounds for a lot of it and I was just hoping that there might be an easier way to accomplish my end goal.

 

Edit: I will post some code, but I'm swamped at the moment and I have to clean up all my neurotic notes and such first.

[Errant_Shadow]

what the hell... how did this... get here? *confused* I swear to god I hit reply from a different topic, but it posted here for some reason...

[Maq]

what the hell... how did this... get here? *confused* I swear to god I hit reply from a different topic, but it posted here for some reason...

 

You must have posted it.  I was confused myself because I clicked on both of your posts consecutively.  You may be able to still edit it.