SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jun 2004
    Location
    Ottawa
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation PLEASE HELP...Need to randomize!

    I have this script that I really need help with. Well, it's a profiles page and when I do a search I would like to have the profiles on the search results page to randomize. Now, I have no idea to make this work...I guess I'm a newbie. I have pasted the entire search page below, hopefully someone can help out. I have talked to someone about it and all they said that I need to add an "ORDER BY" parameter to the SQL query???

    Thanks to anyone that can help...can some please repost the new edited version of it?

    Script
    ==============================================
    <?
    if($action == process_search)
    {
    // Collecting search tokens & search tables

    $sql_from = "dt_profile as p, dt_members as m (dt_profile as p, dt_members as m)";
    $sql_where = "";
    $sql_vars = "*, p.name, p.gender, p.country, m.age";
    if ($_SERVER['REQUEST_METHOD'] == "POST" || $_SERVER['REQUEST_METHOD'] == "post")
    {
    foreach($_SESSION as $k => $v)
    {
    if(substr($k, 0, 7) != "search_")
    continue;
    $_SESSION[$k] = "";
    }
    }
    switch($search_type)
    {
    case member_code:

    $sID = $search_member_code;
    $sID = preg_replace("/[a-z]+/i",'', $sID);

    $sql_where = "p.member_id='$sID' and p.member_id = m.id";
    $search_query = "SELECT $sql_vars, p.id, p.country FROM $sql_from WHERE

    p.status='1' ".($sql_where == "" ? "" : " AND $sql_where");

    break;
    case quick_search:

    foreach($_POST as $k => $v)
    {
    if(substr($k, 0, 7) != "search_")
    continue;
    $_SESSION[$k] = $v;
    }
    $nWas = 0;

    if($search_gender != "")
    {
    if ($search_looking_for != "")
    {
    $sql_where = "p.looking_for='$search_gender' and p.gender='$search_looking_for' ";
    }
    else
    {
    $sql_where = "p.looking_for='$search_gender' ";
    }
    $nWas = 1;
    }

    if($search_age_from != "")
    {
    $sql_where .= ($nWas ? "and " :

    "")."m.age>='$search_age_from' ";
    $nWas = 1;
    }
    if($search_age_to != "")
    {
    $sql_where .= ($nWas ? "and " :

    "")."m.age<='$search_age_to' ";
    $nWas = 1;
    }
    if($search_radius != "" && $search_zipcode != "")
    {
    $search_zipcode = substr(trim($search_zipcode), 0,

    5);
    $zip_lookup_query = "select longw, latn from

    dt_zips where zipcode = '$search_zipcode' ";
    $longlatn = q($zip_lookup_query);
    $longlatn = f($longlatn);

    if ($longlatn["longw"] != "")
    {
    $longitude = $longlatn['longw'];
    $latitude = $longlatn['latn'];
    }
    else
    {
    $longitude = 0;
    $latitude = 180;
    }
    $sql_where .= ($nWas ? "and " : "") . "

    sqrt(power(p.longitude - $longitude, 2) + power(p.latitude - $latitude, 2))*69 < $search_radius ";
    $nWas = 1;
    }

    if ($search_piconly != "")
    {
    $sql_where .= ($nWas ? "and " : "")." f.member_id = p.member_id and f.filename_1 <> '' and p.member_id

    = m.id ";
    $nWas = 1;
    $sql_from = "dt_profile AS p, dt_photos as f, dt_members as m ";
    }
    else
    {
    $sql_where .= ($nWas ? "and " : "")." p.member_id = m.id ";
    $nWas = 1;
    $sql_from = "dt_profile AS p, dt_members as m ";
    }

    $query = "SELECT count(p.id) as count1 FROM $sql_from WHERE

    (p.status='1' ".($sql_where == "" ? ")" : " AND $sql_where)");
    $rec_count = f(q($query));
    $rec_count = $rec_count["count1"];
    $page_count = 0;
    if($records_per_page >= $rec_count)
    {
    $search_query = "SELECT $sql_vars, p.id, p.country FROM

    $sql_from WHERE p.status='1' ".($sql_where == "" ? "" : " AND $sql_where");
    }
    else
    {
    $page_count = $rec_count / $records_per_page;
    $page_count = ceil($page_count);
    settype($page_num, "integer");
    if(!$page_num){$page_num++;}
    if($page_num > $page_count)
    {
    $page_num = $page_count;
    }
    $from = ($page_num - 1) * $records_per_page;
    $search_query = "SELECT $sql_vars, p.id FROM $sql_from

    WHERE p.status='1' ".($sql_where == "" ? "" : " AND $sql_where")." limit $from, $records_per_page";

    }
    break;
    case profile_name:
    $sql_where = "p.name LIKE '%$profile_name%' and

    p.member_id = m.id";
    $search_query = "SELECT $sql_vars, p.id FROM $sql_from WHERE p.status='1'

    ".($sql_where == "" ? "" : " AND $sql_where");

    break;


    default:
    foreach($_POST as $k => $v)
    {
    //echo $k."|".$v."<br>";
    if(substr($k, 0, 7) != "search_")
    continue;
    $_SESSION[$k] = $v;
    }

    $nWas = 0;

    if($search_gender != "")
    {
    if ($search_looking_for != "")
    {
    $sql_where = "p.looking_for='$search_gender' and p.gender='$search_looking_for' ";
    }
    else
    {
    $sql_where = "p.looking_for='$search_gender' ";
    }
    $nWas = 1;
    }

    if($search_age_from != "")
    {
    $year = date("Y") - $search_age_from;
    $sql_where .= ($nWas ? "and " :

    "")."m.age>='$search_age_from' ";
    $nWas = 1;
    }

    if($search_age_to != "")
    {
    $year = date("Y") - $search_age_to;
    $sql_where .= ($nWas ? "and " :

    "")."m.age<='$search_age_to' ";
    $nWas = 1;
    }

    if($search_city != "")
    {
    $sql_where .= ($nWas ? "and " : "")."p.city LIKE

    '%$search_city%' ";
    $nWas = 1;
    }

    if($search_state != "")
    {
    $sql_where .= ($nWas ? "and " : "")."p.state LIKE

    '%$search_state%' ";
    $nWas = 1;
    }
    if ($search_piconly != "")
    {
    $sql_where .= ($nWas ? "and " : "")."f.member_id = p.member_id and p.member_id = m.id

    and f.filename_1 <> ''";
    $nWas = 1;
    $sql_from = "dt_profile AS p, dt_members as m, dt_photos as f";
    }
    else
    {
    $sql_where .= ($nWas ? "and " : "")."p.member_id = m.id ";
    $nWas = 1;
    $sql_from = "dt_profile AS p, dt_members as m";
    }


    $fields_list = array(
    // "name of control in html form" =>

    "database field"
    "search_country" => "country",
    "search_race" => "race",
    "search_marital_status" =>

    "marital_status",
    "search_religion" => "religion",
    "search_drinking" => "drinking",
    "search_smoking" => "smoking",
    "search_food" => "food",
    "search_occupation" => "occupation",
    "search_education" => "education",
    "search_birth_month" => "birth_month",
    "search_eye_color" => "eye_color",
    "search_hair_color" => "hair_color",
    "search_body_type" => "body_type",
    "search_height" => "height"


    );

    while(list($control, $field) = each($fields_list))
    {
    if(gettype($$control) == "array")
    {
    $cntr = $$control;
    $in_c = "";
    for($i = 0; $i < count($cntr); $i++)
    {
    $in_c .= "'".$cntr[$i]."', ";
    }
    if($in_c != "")
    {
    $in_c = substr($in_c, 0, -2);
    $sql_where .= ($nWas ?

    "and " : "")."p.$field in ($in_c) ";
    }
    }
    else
    {
    if($$control != "")
    {
    $sql_where .= ($nWas ?

    "and " : "")."p.$field = '".$$control."' ";
    $nWas = 1;
    }
    }
    }


    if(isset($search_languages)&&(count($search_languages) > 0))
    {
    $in_c = "";
    for($i = 0; $i < count($search_languages); $i++)
    {
    $in_c .= "'".$search_languages[$i]."', ";
    }
    if($in_c != "")
    {
    $in_c = substr($in_c, 0, -2);
    $sql_where .= ($nWas ? "and " : "")."((p.lang_1 in ($in_c)) or

    (p.lang_2 in ($in_c)) or (p.lang_3 in ($in_c))) ";
    $nWas = 1;
    }
    }

    if(isset($search_interests)&&(count($search_interests) > 0))
    {
    $in_c = "";
    for($i = 0; $i < count($search_interests); $i++)
    {
    $in_c .= "'".$search_interests[$i]."', ";
    }
    if($in_c != "")
    {
    $in_c = substr($in_c, 0, -2);
    $query = "select profile_id from dt_interests_x where interest_id

    in ($in_c)";
    $rst = q($query);
    $int_in_c = "0, ";
    while($row = f($rst))
    {
    $int_in_c .= $row["profile_id"].", ";
    }
    $int_in_c = substr($int_in_c, 0, -2);
    $sql_where .= ($nWas ? "and " : "")."p.id in ($int_in_c) ";
    $nWas = 1;
    }
    }

    // Creating search query:

    $query = "SELECT count(p.id) as count1 FROM $sql_from WHERE p.status='1'

    ".($sql_where == "" ? "" : " AND $sql_where");
    $rec_count = f(q($query));
    $rec_count = $rec_count["count1"];
    $page_count = 0;
    if($records_per_page >= $rec_count)
    {
    $search_query = "SELECT $sql_vars, p.id FROM $sql_from WHERE

    p.status='1' ".($sql_where == "" ? "" : " AND $sql_where");
    }
    else
    {
    $page_count = $rec_count / $records_per_page;
    $page_count = ceil($page_count);
    settype($page_num, "integer");
    if(!$page_num)$page_num++;
    if($page_num > $page_count)
    {
    $page_num = $page_count;
    }
    $from = ($page_num - 1) * $records_per_page;


    $search_query = "SELECT $sql_vars, p.id FROM $sql_from WHERE p.status='1' ".($sql_where == "" ? "" : " AND

    $sql_where")." limit $from, $records_per_page";
    }
    }

    $profiles = q($search_query);
    //echo $search_query;
    include "templates/search_results.ihtml";
    }
    else
    {
    foreach($_SESSION as $k => $v)
    {
    if(substr($k, 0, 7) != "search_")
    continue;
    session_unregister($_SESSION[$k]);
    }
    session_unset();
    session_destroy();
    include "templates/easy_search.ihtml";
    }
    ?>
    ================================================
    Last edited by jagis; Jun 11, 2004 at 02:06.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    all you need to do is add ORDER BY RAND() to your query

    can some please repost the new edited version of it?
    dreaming in technicolor
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jun 2004
    Location
    Ottawa
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, thanks but I don't work with SQL code at all and have no idea where I would put it in the script to have it work...I'm hoping someone can place the randomize code into the above original code for me.

    Thank you.

  4. #4
    SitePoint Member
    Join Date
    Jun 2004
    Location
    Ottawa
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could someone/anyone please help apply the "ORDER BY RAND()" function to the original script I have posted in this forum? I'm really in need of someones good help at the moment. Thanks so much.

  5. #5
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Add what Rudy said at the end of the query where you want to retrieve the records randomly.

    Like this...

    PHP Code:
    $search_query "SELECT $sql_vars, p.id FROM $sql_from WHERE p.status='1' ".($sql_where == "" "" " AND $sql_where")." ORDER BY RAND()"
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  6. #6
    SitePoint Member
    Join Date
    Jun 2004
    Location
    Ottawa
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks to everyone who tried to help out with the script. I have found someone who fixed this for me.

    Thanks again guys, I appreciate all the help.

    Jagis


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
  •