I am trying to select random rows from “profiles” table
where the id’s is different than ALL the user_id_that_won from Arena_Fights.
I was thinking about something like that:
(It doesn’t work and I wonder how could I do it…):
$result2 = mysql_query("SELECT * FROM profiles WHERE id != '(SELECT user_id_that_won FROM Arena_Fights WHERE user_id_choose='".$my_profile->GetID()."')' ORDER BY RAND() LIMIT 2");
My hosting service is limiting me to 100MB per database.
And I wonder if I will use more than 100MB and I am building a website which has alot of features…
how could I make my website database unlimited (is it okay to make new databases when the last one reached his limit?)
$sql = "SELECT *
FROM profiles
WHERE id NOT IN (
SELECT user_id_that_won
FROM Arena_Fights
WHERE user_id_choose='" . $my_profile->GetID() . "'
) ORDER BY RAND()
LIMIT " . $number_of_rows_wanted . ";
Although this starts to perform very badly once the database gets more then a couple hundred records.
Another alternative is to generate a list of random numbers between 1 and MAX(profiles.id) then use a WHERE IN clause and LIMIT by the number of results you need. It seems like more work, but it performs better.
Drop the ORDER BY and LIMIT clauses, instead, without them clauses, once you’ve got the result set in an array in PHP, use PHP’s array_rand() function to get x number of random rows from the result set.