Get random rows - With specific details on it

Databases
1

I am trying to get random rows where the ‘ID’ is different from $my_profile->GetID() And ‘sex’ is equal to $thesex
And the ID different from “user_id_was_interested_in” in InterestedList Where the user_id_choose different from $my_profile->GetID().
And trying to check if between birthday dates $to and $from…

The Problem is when I refresh the page sometimes I get the result. and sometimes It tells me that it didn’t catch anything…

The Code:


// This is setting the dates from age to age
$from = 16;
$to = 18;
$from = date("Y-m-d", mktime(0, 0, 0, date("m"),   date("d"),   date("Y")-$from));
$to = date("Y-m-d", mktime(0, 0, 0, date("m"),   date("d"),   date("Y")-$to-1));
$to = date("Y-m-d",strtotime(date("Y-m-d", strtotime($to_year_to_search)) . " -1 day"));

// Now getting a random row between the age's
$result2 = mysql_query("SELECT * FROM `profiles` WHERE id != '".$my_profile->GetID()."' AND id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `profiles` ) AND sex = '".$thesex."' AND id NOT IN (SELECT user_id_was_interested_in FROM InterestedList WHERE user_id_choose='".$my_profile->GetID()."') AND birthday BETWEEN '".$to."' AND '".$from."' ORDER BY id LIMIT 1") or die(mysql_error());
2

I’d query a recordset of possible results. Assign it to an array. Then use the rand function to find a random id to point to within your result set.

3

What if its a big table (ex. 100,000 rows) ? wouldn’t it take lots of time?

4

mm well I changed it to this way (and its working…):
Searching the amount of rows on the query I’m running
then using random function in php and getting random row…
the problem is I am running the query twice…
isn’t there a better way of doing it?
If I will have a big table wouldn’t it take lots of time to get random row?


// NOT IMPORTANT,Just getting the ages dates...
	$from_year_to_search = date("Y-m-d", mktime(0, 0, 0, date("m"),   date("d"),   date("Y")-$from));
	$to_year_to_search = date("Y-m-d", mktime(0, 0, 0, date("m"),   date("d"),   date("Y")-$to-1));
	$to_year_to_search = date("Y-m-d",strtotime(date("Y-m-d", strtotime($to_year_to_search)) . " -1 day"));

// this is the search which gets a random rows
		$result = mysql_query("SELECT * FROM `profiles` WHERE id != '".$my_profile->GetID()."' AND sex = '".$thesex."' AND id NOT IN (SELECT user_id_was_interested_in FROM 
				InterestedList WHERE user_id_choose='".$my_profile->GetID()."') AND birthday BETWEEN '".$to_year_to_search."' AND '".$from_year_to_search."' ") or die(mysql_error());
		$found = mysql_num_rows($result);
		if ($found > 0)
		{
			$flag_place = rand(0, $found-1);
			$result2 = mysql_query("SELECT * FROM `profiles` WHERE id != '".$my_profile->GetID()."' AND sex = '".$thesex."' AND id NOT IN (SELECT user_id_was_interested_in FROM 
				InterestedList WHERE user_id_choose='".$my_profile->GetID()."') AND birthday BETWEEN '".$to_year_to_search."' AND '".$from_year_to_search."' LIMIT $flag_place,1") or die(mysql_error());
		}
5

Run the query to get all the matching rows, then once you have the result set in an array in php, use the array_rand() function in php to get the required number of random rows.

6

Alright I just did it and it works…
But I think that my first solution was faster…
Since I was not setting it all into the array just getting the query number of rows…
and then getting random number between the query number of rows
and after that searching with another query limit to the random number i have received…
so what is the better solution which will work faster with a big table ? (ex. 100,000 rows?)

7

danrevah, have a read through this thread (http://www.sitepoint.com/forums/mysql-182/speedy-order-rand-operation-727493.html), where different techniques for getting random rows are discussed and compared.