SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with retrieving data

    Hi,

    I'll put my function first:
    PHP Code:
    function myFunction($options false){
            
    $q "
                SELECT 
                    * 
                FROM 
                    "
    .DB_PREFIX."ticket t 
                    
                    LEFT JOIN 
                        "
    .DB_PREFIX."ticket_c c 
                    ON 
                        c.ticketID = t.ticketID 
                    
                WHERE t.requesterID = '"
    .$ID."'
                ORDER BY 
                    c.commentDate DESC
            "
    ;
            
            
    $result $this->query($q);
            while(
    $row $this->fetch_assoc($result)){
                
    $rows[] = $this->makeTicketRow($row);
            }
            return 
    '
                <table id="ticketTable">
                    <thead>
                        <tr>
                            <td>&nbsp;</td>
                            <td>TicketID</td>
                            <td>Category</td>
                            <td>Subject</td>
                            <td>Date Opened</td>
                            <td>Options</td>
                        </tr>
                    </thead>
                    <tbody>
                        '
    .@implode(''$rows).'
                    </tbody>
                </table>
                
            '
    ;
        } 
    The problem I'm basically having is that all data is returned fine, but the ticketID as used to join the tables is only returned for the first row. All other data is perfect. I'm new to JOINing tables so I'm not sure if that's where I'm going wrong?

    Any help would be great.

    Cheers,
    Rhys

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    You're using this inside an object? Show us the definitions for query, fetch_assoc and makeTicketRow.

  3. #3
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure.
    PHP Code:
    function getTicketStatusClass($TicketStatusID){
            switch(
    $TicketStatusID){
                case 
    1;
                    return 
    'awaiting';
                    break;
                case 
    2;
                    return 
    'received';
                    break;
                case 
    3;
                    return 
    'closed';
                    break;
            }
        }
        
    function 
    getTicketOptions($TicketStatusID$TicketID){
            switch(
    $TicketStatusID){
                case 
    1;
                    return 
    '
                        <a href="'
    .URL.'view-ticket/'.$TicketID.'/?status=3">
                            <img src="'
    .URL_IMG_SYSTEM.'close.png" title="Close Ticket" />
                        </a>'
    ;
                    break;
                case 
    2;
                    return 
    '
                        <a href="'
    .URL.'view-ticket/'.$TicketID.'/?status=3">
                            <img src="'
    .URL_IMG_SYSTEM.'close.png" title="Close Ticket" />
                        </a>'
    ;
                    break;
                case 
    3;
                    return 
    '
                        <a href="'
    .URL.'view-ticket/'.$TicketID.'/?status=2">
                            <img src="'
    .URL_IMG_SYSTEM.'open.png" title="Re-open Ticket" />
                        </a>'
    ;
                    break;
            }
    }

    function 
    makeTicketRow($row){
            return 
    '
                <tr>
                    <td><div class="ticketStatus '
    .$this->getTicketStatusClass($row['ticketStatus']).'">&nbsp;</div></td>
                    <td><a href="'
    .URL.'view-ticket/'.$row['ticketID'].'/">'.$row['ticketID'].'</a></td>
                    <td>'
    .$this->htmlspecialchars($row['ticketCategory']) . '</td>
                    <td><a href="'
    .URL.'view-ticket/'.$row['ticketID'].'/">'.$row['ticketTitle'].'</a></td>
                    <td>'
    .date('F j, Y, g:i a'$row['ticketDate']).'</td>
                    <td class="ticketOptions"><a href="'
    .URL.'view-ticket/'.$row['ticketID'].'/">
                            <img src="'
    .URL_IMG_SYSTEM.'view.png" title="View Ticket" />
                        </a>
                        '
    .$this->getTicketOptions($row['ticketStatus'], $row['ticketID']).'
                    </td>
                </tr>
            '
    ;
            
            
    }

    //query and fetch_assoc tried and tested working site-wide:

    public function fetch_assoc($result){
            if(!
    $result$this->dbError('Empty MySQL resource.');
            
    $data = @mysql_fetch_assoc($result);
            if(
    $data) return $this->stripslashes_deep($data);
            else return 
    false;
    }

    public function 
    query($q){
            if(empty(
    $q)) $this->dbError('Empty MySQL Query.');
            if(
    $this->linkID == 0$this->connect();
            
    $temp = @mysql_query($q$this->linkID);
            if(!
    $temp$this->dbError('Invalid Query : '.mysql_error().'<br />'.$q);
            return 
    $temp;

    Cheers,
    Rhys

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    WHERE t.requesterID = '".$ID."'

    Where is $ID being filled in? I dont see it in your function definition...(should be passed as a parameter...)

  5. #5
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry that's was to save the detective work
    It's actually: '".$this->escape_string($this->getUserID())."'

    echo'd the query and it's fine in that sense. I can provide a screenshot of the output to show what I mean.

    http://i51.tinypic.com/4u6vx3.png

    As you can see, there is no ID for any other row than the first. The links support this.

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    Okay so the query is returning 3 rows of data - it's just not showing you an ID for the other two rows (It IS however returning a title for each). This is good information to have.

    Stupid question i'm sure, but... do those other two rows have ID #'s?

  7. #7
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not stupid at all, in fact you just gave me the answer! Thanks. The other 2 don't have any links to the second table ticket_c. Now the problem I have is, the rows in ticket won't have any data in ticket_c until somebody adds a comment.

    Is there a was to still show the rows IDs regardless of the data in title_c, as that is only required if there is any comments? The alternative is to not have any links or IDs on that row until it has been answered and a comment added.

    I don't use JOINs often

    Thanks!
    Rhys

  8. #8
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    Not sure if this will work, but try this instead for your query:

    $q = "
    SELECT
    t.ticketID,t.ticketStatus,t.ticketCategory,t.ticketDate
    FROM
    ".DB_PREFIX."ticket t

    LEFT JOIN
    ".DB_PREFIX."ticket_c c
    ON
    c.ticketID = t.ticketID

    WHERE t.requesterID = '".$ID."'
    ORDER BY
    c.commentDate DESC
    ";

  9. #9
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    That worked great! Thanks StarLion, you've been a great help.

  10. #10
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    Something's nagging me about that....

    I want to say it should instead be...
    $q = "
    SELECT
    t.ticketID,t.ticketStatus,t.ticketCategory,t.ticketDate
    FROM
    ".DB_PREFIX."ticket t

    LEFT JOIN
    (SELECT ticketID,commentDate FROM ".DB_PREFIX."ticket_c GROUP BY ticketID ORDER BY commentDate DESC ) AS c
    ON
    c.ticketID = t.ticketID

    WHERE t.requesterID = '".$ID."'
    ORDER BY
    c.commentDate DESC
    ";

    Because you only want each ticket to show up ONCE, right?

  11. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    EDIT: no, i'm being paranoid now. Left join will make sure each only shows up once.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the problem with "ticketId" only showing a value for the first row is easy to understand once you know what's causing it

    it is the dreaded, evil "select star" and you were warned not to use it

    SELECT * will return all columns from all tables in the FROM clause

    so the query result set actually has two columns called tickedId, the first one from the first table, t, and the second one from the second table, t_c

    and since it's a LEFT OUTER JOIN, the valule in the second one will often be NULL

    i don't do php, but i'm pretty sure that a php reference to a column called "ticketId" will reference the second one

    solution: don't use the dreaded, evil "select star" -- specify only the columns you want, and if you have multiple columns with the same name that need to be returned, give them distinct column aliases

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's right yeh. I put another comment into ticket_c and like you guessed, the ticket showed up twice. Thanks once again, I have a lot to learn.

  14. #14
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    ah. I should listen to those little voices in my head. >.> <.<

    The above query will group the comments table up first, meaning that the join will then have a 1-0/1 relationship, rather than 1-*

    course, you could also group them in the initial query after the join... not... entirely sure which would be quicker... Rudy?

  15. #15
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great, thanks StarLion and r937. Both very helpful. I have so much to learn, and there I was thinking I'm an expert or something

  16. #16
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    Off Topic:

    I never consider myself an expert, because I know there's always so much more that I could learn. It is my goal to learn something every day of my life.

  17. #17
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Exactly.

    Off Topic:
    A Pilot with 100 hours thinks he knows it all.
    A Pilot with 1,000 hours knows he knows it all.
    A Pilot with 10,000 hours knows he'll never know anything.
    *Words of a Captain, not me


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
  •