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

PHP
1

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?)
2

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.

3

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.

4

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

5

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.