Mysql capabilities

Anon

Hi all,

I've been playing around with mysql for a while now (my first database) and I'm beginning to get the hang of it.

I would like to know if the following sort of thing is possible...

Say I have a table with 2 fields, "name" and "time". Is it possible to construct a query which gives me a list of all the entries with a given name and the difference in time between the next one and this one.

So basically...

name | time

russ | 4
russ | 7

would return:

russ - 3 and russ - 0

I know I haven't explained this very well. But basically I want to know if doing calculations etc. is even possible with a mysql query.

If not, what are my options? I would like to be able to display the list of results in a table and allow the user to sort by a given column. If mysql cannot do the above, I guess I will have to sort manually with a quick sort algo and the like... is this how it's done?

Sorry for the long winded question, any help much appreciated.

Regards,
Russ

Anon

It is not possible in Mysql to do calculation with values in other records. This is a limitation of Mysql, not SQL.

If you wish to retrieve data sorted with respect to a certain column, you can add an order by clause so there is no need for a sort algorithm.

It is a bit unclear what you mean by

the next one >>

Do you mean next with respect to the order in which records were entered or just the one with the next lowest value? If the latter you can do that sorting in the select statement

select * from t order by name,time desc

if you retrieve the data in this order (names sorted ascending and time values sorted descendingly for each name) you can do the calculation in PHP, keeping track of when the name column changes value.

if the former, you need a timestamp column so you can order by this column instead. A relational database will not keep track of the order in which records are entered.

Anon

Hi,

Thanks for your reply. By <<the next one>> I bascially mean the next highest value (in terms of a timestamp). I realise this is impossible now, tho.

I basically have an object (a class) which represents a given row. My problem is that currently I have to calculate some of the fields of this manually (without relying on mysql). Thus, when I want to let the user 'sort' the columns displayed in the html table, I can't just simply call another QUERY with a changed ORDER BY clause because of the fields that require manual calculation.

Thus, is my only option to sort the objects manually (with some kind of quick sort algorithm etc.) before outputting to the screen? Or can (should) temporary databases be used in this instance.

Thanks again for your help,
Russ