How to get data from specific row

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
infomamun
Forum Contributor
Posts: 102
Joined: Mon Dec 28, 2009 7:48 pm

How to get data from specific row

Post by infomamun »

Hi there,
I have a database like this:

table: test
===================================
ID |++ date ++| time | company | price
-----------------------------------------------
1 |2012-10-20 | 10:30 | XYBank | 520
2 |2012-10-20 | 10:31 | XYBank | 500
3 |2012-10-20 | 10:32 | XYBank | 540
4 |2012-10-20 | 10:33 | XYBank | 520
5 |2012-10-20 | 10:34 | XYBank | 530
==================================

Now I want to get the data:
$query = "SELECT * FROM test WHERE date='2012-10-20' AND company='XYBank' ORDER BY time ASC";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);

For later purpose of php code, I need to know the minimum time (here 10:30) and maximum time (here 10:34) from $result.
I can get minimum time easily by $mintime = $row['time'] as the query was sorted by time with ascending mode.

But how to get the maximum time?
I have to consider following facts:
1) I don't want to use another query (Like, here ORDER BY time DISC LIMIT 1) to get max time
2) I also don't want to use min(time) or max(time) in query

One possible solution is to go the last row of the $result. Because as it was sorted by time and with ascending mode, the last row will have the maximum time.
But I dont know how to get last row without looping the $row like this:
$maxtime = $row[rownumber][fieldname];

As for example, my above query will return 5 $row, so if I can get something like this:
$maxtime = $row[4]['time'], I can get the max time without looping the $row.

Would anybody help me in this regard, how can I get this?

Regards
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How to get data from specific row

Post by requinix »

Considering how there are three options and you don't want to do two of them (two queries or a GROUP BY with a MIN(time) and MAX(time)) then you're stuck with the third one: query for everything and loop through them all to get to the last row.

The closest you can get to getting a certain row is mysql_result but internally it'll do the same thing you'd have to do: buffer all the results someplace first.
Post Reply