SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    May 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow php/mysql query help

    Hi,

    I'm new to php and really just experimenting. I'm attempting to make a community site as a project and I'm trying to display "friends online" in a popup window.



    PHP Code:
    <?
    $curtime 
    time();

    // put all of user's friends into an array.
    $listfriends mysql_query("SELECT f_name FROM friend_list WHERE uname = '$uname'"); 

    while(
    $flist_row mysql_fetch_array($listfriends)){
    $fid $flist_row['f_name'];

    // loop "members" table for each of the user's friends.
    $search_result mysql_query("SELECT uname, last_action FROM members WHERE uname = '$fid' LIMIT 1");

    while(
    $search_row mysql_fetch_array($search_result)){ 
    $search_user $search_row['uname'];
    $searchlast_action $search_row['last_action'];
    }

    // checks their friend's last action against current time to see if they are still active on the website.
    $searchonline_status $searchcurtime $searchlast_action;
    if (
    $searchonline_status 600) {
    print 
    "$search_user<br>";
    }
    }
    ?>
    I'm just learning so I know the code is probably very ugly, but can anyone help me with this? The query is very slow when a user has a lot of friends on his friend list.

    Is there another way to do this that will run a faster query and use less server load.
    Last edited by Jelena; May 12, 2007 at 11:15.

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    16,448
    Mentioned
    160 Post(s)
    Tagged
    1 Thread(s)

    query

    Hi Codestatus, welcome to the forums,
    I'm not a database guru, but I think the script would run faster if the queries weren't nested. If you put the results from the first into a temp array, you could then use that for the second without the queries being nested. eg, maybe like
    PHP Code:
    $fid = array();
    while(
    $flist_row mysql_fetch_array($listfriends))
    {
        
    $fid[] = $flist_row['f_name'];

    then either do a while loop on the $fid array to run sequential queries, or maybe something like this would work for a single query
    PHP Code:
    $search_result mysql_query("SELECT uname, last_action FROM members WHERE uname IN '$fid' LIMIT 1"); 
    I imagine there's a better way using some kind of JOIN, but I'll have to let someone better with databases answer that.

  3. #3
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    In this example, a user with 1000 friends on his list is causing 1001 queries to the database (the original query plus 1000 queries to get the name).
    So, as MIttineague says, a JOIN is better.

    JOINs are difficult to understand at first, but very powerful:

    PHP Code:
    <?php
    $curtime 
    time();

    // put all of user's friends into an array.

    $sql "SELECT m.* FROM friend_list AS fl INNER JOIN members AS m ON m.uname=fl.f_name WHERE fl.uname = '$uname'";
    $listfriends mysql_query(&sql);

    while(
    $flist_row mysql_fetch_array($listfriends)){
        
    $search_user $list_row['uname'];
        
    $searchlast_action $list_row['last_action'];
        
    // checks their friend's last action against current time to see if they are still active on the website.
        
    $searchonline_status $curtime $searchlast_action;
        if (
    $searchonline_status 600) {
            print 
    "$search_user<br>";
        }
    }
    ?>
    I'd love to try explaining how this query works, but I'm not too good at that. Maybe someone else can explain it without being too confusing

    BTW: I've not actually tested that code, but it should work.

  4. #4
    SitePoint Member
    Join Date
    May 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Appreciate that guys! I'm going to try that out. I'll try to figure out how it works on my own but if someone else can explain it for me then even better!

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    one query returning multiple rows is always going to be ~way~ faster than multiple queries that return one row each
    Code:
    SELECT m.uname
         , m.last_action
      FROM friend_list as fl
    INNER
      JOIN members as m
        ON m.uname = fl.f_name 
     WHERE fl.uname = '$uname'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •