pulling a timestamp from table

[fondy98]

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

[Fou-Lu]

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.

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.

[fondy98]

The output shows:

Resource id #45

Which is not correct.

I used:

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

[fondy98]

I also tried this:

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

[fondy98]

Sorry, I am still having no luck at all.

[BubikolRamios]

Originally Posted by fondy98

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 ?

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

[fondy98]

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

[BubikolRamios]

I doubt anyone but you have any idea is that now correct or not ?

(-:

[fondy98]

No. It should show the timestamp number. But for some reason it is showing the resource id22

[fondy98]

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/

[BubikolRamios]

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:

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.

[Old Pedant]

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.

[Old Pedant]

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.

[fondy98]

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

[fondy98]

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.