SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2000
    Location
    Norcross, GA
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best way to optimize this query?

    PHP Code:
    $result mysql_query("
        SELECT 
            *, 
            DATE_FORMAT(call_start,'
    $dateformat2') as fcall_start 
        FROM 
            call_notes 
        WHERE 
            ticketid='
    $reviewticketid
        ORDER BY 
            
    $sql_order
            "
    ,$dbx);
    while (
    $row mysql_fetch_array($result)) {
        
    $noteid $row[noteid]; 
            
    $result2 mysql_query("SELECT note_text FROM call_notes_text WHERE noteid='$noteid'",$dbx);
            
    $row2 mysql_fetch_array($result2);
            
    $note_text stripslashes($row2[note_text]);
            
    $note_text nl2br($note_text);
        
    $checkuserid $row[userid]; 
            
    $result3 mysql_query("SELECT username, email FROM users WHERE userid='$checkuserid'",$dbx);
            
    $row3 mysql_fetch_array($result3);
            
    $username_notes $row3[username];
            
    $email $row3[email];
        
    $call_contact $row[call_contact]; 
            
    $result3 mysql_query("SELECT contact FROM facility_contact WHERE contactid='$call_contact'",$dbx);
            
    $row3 mysql_fetch_array($result3);
            
    $contact $row3[contact];
        
    $call_elapsed $row[call_elapsed]; 
    This section hasn't sat well with me ever since I wrote it. It works, but I am interested in combining these 4 queries into one.

    My problem is that the 'child' queries are dependant on data pulled out of the first table (call_notes).

    Help is greatly appreciated.

    Thanks, Mike

  2. #2
    Action! filmfoto's Avatar
    Join Date
    Dec 2001
    Location
    Sweden
    Posts
    278
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Best way to optimize this query?

    Originally posted by mstembri
    PHP Code:
    $result mysql_query("
        SELECT 
            *, 
            DATE_FORMAT(call_start,'
    $dateformat2') as fcall_start 
        FROM 
            call_notes 
        WHERE 
            ticketid='
    $reviewticketid
        ORDER BY 
            
    $sql_order
            "
    ,$dbx);
    while (
    $row mysql_fetch_array($result)) {
        
    $noteid $row[noteid]; 
            
    $result2 mysql_query("SELECT note_text FROM call_notes_text WHERE noteid='$noteid'",$dbx);
            
    $row2 mysql_fetch_array($result2);
            
    $note_text stripslashes($row2[note_text]);
            
    $note_text nl2br($note_text);
        
    $checkuserid $row[userid]; 
            
    $result3 mysql_query("SELECT username, email FROM users WHERE userid='$checkuserid'",$dbx);
            
    $row3 mysql_fetch_array($result3);
            
    $username_notes $row3[username];
            
    $email $row3[email];
        
    $call_contact $row[call_contact]; 
            
    $result3 mysql_query("SELECT contact FROM facility_contact WHERE contactid='$call_contact'",$dbx);
            
    $row3 mysql_fetch_array($result3);
            
    $contact $row3[contact];
        
    $call_elapsed $row[call_elapsed]; 

    I don't have your table setup, but try this:
    PHP Code:
    $result mysql_query("
        SELECT 
            *, 
            DATE_FORMAT(call_start,'
    $dateformat2') AS fcall_start 
        FROM 
            call_notes AS cn, call_notes_text AS cnt, users AS u, facility_contact AS fc
        WHERE 
            cn.ticketid='
    $reviewticketid' AND cn.noteid=cnt.noteid AND cn.userid=u.userid AND cn.call_contact=fc.contactid
        ORDER BY 
            
    $sql_order
            "
    ,$dbx); 





    Cheers.


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
  •