Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    New Coder
    Join Date
    Feb 2013
    Posts
    15
    Thanks
    9
    Thanked 0 Times in 0 Posts


    I need to get the timestamp for the last reply to a ticket from the parent id number. In this example would be the parent id number 16.

    I posted an image screenshot but it's not showing so here it is.

    http://www.flickr.com/photos/93602215@N06/8510774240/



    I need help. I am trying to grab the timestamp for the last reply to a ticket. IN this example it would belong to parent id number 16 and there are three entries (rows) so I need to grab the last reply so I can make a last reply date and time for the ticket reply.

    I am trying to attach a screenshot but still is not working.

    Mysql screenshot

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    This may look confusing, so to clarify I merged together two posts. Each one had a link at the top and bottom, so there may be confusion between the two.

    Anyways, you can use the MAX aggregate on this.
    Code:
    SELECT parent, MAX(`timestamp`) FROM table GROUP BY parent
    You can add a WHERE clause for the parent if you have a specific id to work with as well.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    fondy98 (02-27-2013)

  • #3
    New Coder
    Join Date
    Feb 2013
    Posts
    15
    Thanks
    9
    Thanked 0 Times in 0 Posts
    The output shows:

    Resource id #45

    Which is not correct.

    I used:

    "SELECT parent, MAX(`timestamp`) FROM " . PREFIX . "replies GROUP BY parent"

  • #4
    New Coder
    Join Date
    Feb 2013
    Posts
    15
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I also tried this:

    "SELECT parent, MAX(`timestamp`) FROM " . PREFIX . "replies WHERE type = 'ticket' GROUP BY parent"

  • #5
    New Coder
    Join Date
    Feb 2013
    Posts
    15
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Sorry, I am still having no luck at all.

  • #6
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,994
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Quote Originally Posted by fondy98 View Post

    I need to get the timestamp for the last reply to a ticket from the parent id number. In this example would be the parent id number 16.

    This gives you what you want, right ?
    Code:
    SELECT MAX(`timestamp`) FROM replies WHERE type = 'ticket' and parent = "16"
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • Users who have thanked BubikolRamios for this post:

    fondy98 (02-27-2013)

  • #7
    New Coder
    Join Date
    Feb 2013
    Posts
    15
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I tried SELECT MAX(`timestamp`) FROM replies WHERE type = 'ticket' and parent = "16"

    and

    "SELECT MAX(`timestamp`) FROM " . PREFIX . "replies WHERE type = 'ticket' and parent = '16'"

    and now it shows Resource id #22

  • #8
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,994
    Thanks
    120
    Thanked 76 Times in 76 Posts
    I doubt anyone but you have any idea is that now correct or not ?

    (-:
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • Users who have thanked BubikolRamios for this post:

    fondy98 (02-27-2013)

  • #9
    New Coder
    Join Date
    Feb 2013
    Posts
    15
    Thanks
    9
    Thanked 0 Times in 0 Posts
    No. It should show the timestamp number. But for some reason it is showing the resource id22

  • #10
    New Coder
    Join Date
    Feb 2013
    Posts
    15
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I should clarify. the original ticket is in another table called tickets. The replies are in a table called replies such as shown above. I need to show the last reply date to a response to the ticket which is in the timestamp column under the replies table also shown in the screenshot shown in first post.

    Here is a screenshot of what it looks like
    http://www.flickr.com/photos/93602215@N06/8513212140/
    Last edited by fondy98; 02-27-2013 at 02:31 PM. Reason: error

  • #11
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,994
    Thanks
    120
    Thanked 76 Times in 76 Posts
    But for some reason it is showing the resource id22
    Who, where ?

    You are 'mixing' here PHP and MYSQL, while you are not sure that mysql part is OK jet.

    First clarify what is going on at mysql part. Separately !

    My filling is you dont have a mysql client.
    Get this: http://www.heidisql.com/download.php

    Check what that sql client returns on:
    Code:
    SELECT MAX(`timestamp`) FROM replies WHERE type = 'ticket' and parent = "16"
    this can no way return 'resource id22'

    Then go to PHP part.

    BTW - one of links to images in upper posts is broken.
    Last edited by BubikolRamios; 02-27-2013 at 04:09 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • Users who have thanked BubikolRamios for this post:

    fondy98 (02-27-2013)

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    The resource id means you are doing something wrong in PHP. It has nothing to do with MySQL. In fact, the fact that you are getting a resource id other than zero means that the query *IS* working (at least in the sense it is doing *something*).

    If you won't show your PHP code, nobody can help you.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    fondy98 (02-27-2013)

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Bubikol: He must have a mysql client else where would he be getting the resource id number from?

    I think he just doesn't know how to USE the mysql client.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    fondy98 (02-27-2013)

  • #14
    New Coder
    Join Date
    Feb 2013
    Posts
    15
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I do have mysql client as all other info is populated perfectly as normal. I just decided to add the last reply field and for some reason just cannot for the life of me get it to work

  • #15
    New Coder
    Join Date
    Feb 2013
    Posts
    15
    Thanks
    9
    Thanked 0 Times in 0 Posts
    When I run the query right in phpmyadmin/mysql, it now returns the timestamp. But now I cannot get it to work in the script.

    $replied = $site->db->query("SELECT MAX(`timestamp`) FROM support_replies WHERE type = 'ticket' and parent = '16'");

    In the line above, we use ( $site->db-> ) for all queries and such.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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