2 Questions(Select from table using another table, Limit database size issue)

okay i’ve got 2 questions first…

  1. 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");
  1. 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?)

I think that there could be 2 ways of doing the select you want.

You could formulate something which is equivalent of:

select id from profiles where id NOT IN (1,2,3);

MySQL - Using the “IN” clause | George Truong

Or you could look at using an outer join Coding Horror: A Visual Explanation of SQL Joins

Sorry to not be more concrete in my answer, but you might be best asking this as a separate question in the dedicated mysql forum.

Cups is on the right track. Here’s an example:

    $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.

Here is an article that explains it in a bit more depth.

Thank you for directing me to that article…
And solving me my problem

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.