SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get random rows - With specific details on it

    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:
    Code PHP:
    // 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. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    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. #3
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Code PHP:
    // 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());
    		}

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What if its a big table (ex. 100,000 rows) ? wouldn't it take lots of time?

  5. #5
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  6. #6
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    danrevah, have a read through this thread (http://www.sitepoint.com/forums/mysq...on-727493.html), where different techniques for getting random rows are discussed and compared.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •