...

View Full Version : pulling a timestamp from table



fondy98
02-27-2013, 12:06 AM
http://www.flickr.com/photos/93602215@N06/8510774240/

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/

http://flic.kr/p/dY4WEU

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 (http://flic.kr/p/dY4WEU.)

Fou-Lu
02-27-2013, 12:11 AM
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
02-27-2013, 03:18 AM
The output shows:

Resource id #45

Which is not correct.

I used:

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

fondy98
02-27-2013, 03:21 AM
I also tried this:

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

fondy98
02-27-2013, 06:30 AM
Sorry, I am still having no luck at all.

BubikolRamios
02-27-2013, 08:49 AM
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
02-27-2013, 01:49 PM
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
02-27-2013, 01:57 PM
I doubt anyone but you have any idea is that now correct or not ?

(-:

fondy98
02-27-2013, 01:59 PM
No. It should show the timestamp number. But for some reason it is showing the resource id22

fondy98
02-27-2013, 02:13 PM
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
02-27-2013, 04:05 PM
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
02-27-2013, 04:56 PM
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
02-27-2013, 04:57 PM
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
02-27-2013, 06:04 PM
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
02-27-2013, 06:19 PM
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.

fondy98
02-27-2013, 06:25 PM
We also use this for what is shown above:


var $function = array(
'connect' => 'mysql_connect',
'select_db' => 'mysql_select_db',
'query' => 'mysql_query',
'num_rows' => 'mysql_num_rows',
'close' => 'mysql_close',
'fetch_array' => 'mysql_fetch_array',
'escape' => 'mysql_real_escape_string'
);

Fou-Lu
02-27-2013, 06:34 PM
Resource id #x indicates that you've successfully acquired a resource in PHP. As pointed out, that means that the SQL did return a result. Use a command line client or something like phpMyAdmin to execute queries directly so see the results.
In PHP world, you now have a resource in which you can operate on. The resource by itself is useless; you cannot print out a resource and expect anything meaningful off of it. You need to issue a fetch command to it to actually retrieve the data. Since you are using a custom class, we cannot provide you information on the fetching itself. Using just mysqli library directly, you'd have:


$sQry = "SELECT parent, MAX(`timestamp`) AS maxTimestamp FROM " . PREFIX . "replies WHERE type = 'ticket' GROUP BY parent";
if ($qry = $mysqliObj->query($sQry))
{
while ($aRecord = $qry->fetch_assoc())
{
printf("Max timestamp for parent %s is %s" . PHP_EOL, $aRecord['parent'], $aRecord['maxTimestamp']);
}
$qry->free();
}



Actually, with this list above you may be able to call the method fetch_array on the db object. That's a bad way of injecting functionality into it though.

fondy98
02-27-2013, 08:00 PM
This is some of the code:

$replied = $site->db->query("SELECT parent, MAX(`timestamp`) AS maxTimestamp FROM " . PREFIX . "replies WHERE type = 'ticket' GROUP BY parent");
if ($qry = $mysqliObj->query($sQry))
{
while ($aRecord = $qry->fetch_assoc())
{
printf("Max timestamp for parent %s is %s" . PHP_EOL, $aRecord['parent'], $aRecord['maxTimestamp']);
}
$qry->free();
}

Fou-Lu
02-27-2013, 08:02 PM
You can't just copy and paste the mysqli usage. That was done as an example. You need to figure out how to make it work with your custom $site->db object.

fondy98
02-27-2013, 08:05 PM
Oops, I did try and replace most of it, but still no go. That one above was accidentally the original mysqli usage. By the way, I don't think that mysqli will work as we are only on mySQL

fondy98
02-27-2013, 08:08 PM
$replied = $site->db->query("SELECT parent, MAX(`timestamp`) AS maxTimestamp FROM " . PREFIX . "replies WHERE type = 'ticket' GROUP BY parent");
if ($replied = $site->db->query($sQry))
{
while ($ticket = $replied->fetch_assoc())
{
printf("Max timestamp for parent %s is %s" . PHP_EOL, $ticket['parent'], $ticket['maxTimestamp']);
}
$replied->free();
}

I tried this as well

fondy98
02-27-2013, 08:09 PM
$replied = $site->db->query("SELECT parent, MAX(`timestamp`) AS maxTimestamp FROM " . PREFIX . "replies WHERE type = 'ticket' GROUP BY parent");
if ($replied = $site->db->query($sQry))
{
while ($replies = $replied->fetch_assoc())
{
printf("Max timestamp for parent %s is %s" . PHP_EOL, $replies['parent'], $replies['maxTimestamp']);
}
$replied->free();
}

ANd I tried this too

Fou-Lu
02-27-2013, 08:35 PM
MySQLi is the replacement for MySQL. Since the mysql library will disappear in the future, the mysqli is there to replace it.
Still isn't correct. $sQry doesn't exist anywhere here, so $replied will be set without a resource.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum