Reverse mysql-result

[frizzle]

Hi there,

Short question:
Is there any way, to reverse the result of a mysql query?

Explanation:
If i have eg. 20 records, all with their own id of course,
select 5 with limit, and order them DESC by ID, it should give
me: 20, 19, 18, 17, 16. That's all ok.
But now i want 16 to appear first, 17 second, etc.

I hope this is clear!

Thanks.

Frizzle.

[Andy Hassall]

On 28 Jun 2005 12:52:48 -0700, "frizzle" <ph********@gmail.com> wrote:

Short question:
Is there any way, to reverse the result of a mysql query?

Explanation:
If i have eg. 20 records, all with their own id of course,
select 5 with limit, and order them DESC by ID, it should give
me: 20, 19, 18, 17, 16. That's all ok.
But now i want 16 to appear first, 17 second, etc.

Depends on which version of MySQL. If it's recent, have the original query as
a subquery, with the outer query re-ordering by ID.

If not recent, fetch all the records into a PHP array and reverse the array.
http://php.net/array

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[frizzle]

Hmm, i've thought of your second option,
but i wondered if i could change things closer
to the information's root ...
The Mysql version is recent, but i don't know
what you mean with subquery.
I also figured i could get the total possible results,
substract number of uploaded pictures, and then
use that as the offset within Limit.
Or would that make things slow (in quite a small db)

Thanks again!

[Andy Hassall]

On 28 Jun 2005 13:53:45 -0700, "frizzle" <ph********@gmail.com> wrote:

Hmm, i've thought of your second option,
What second option? Please quote some context when replying.
but i wondered if i could change things closer
to the information's root ...
The Mysql version is recent, but i don't know
what you mean with subquery.
select id
from (
select id
from t
order by t desc
limit 5
)
order by id
I also figured i could get the total possible results,
substract number of uploaded pictures, and then
use that as the offset within Limit.
Or would that make things slow (in quite a small db)

Possibly. Try it. I'm more used to Oracle where you'd do this differently, so
can't comment too much on optimising usage LIMIT in MySQL.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[CJ Llewellyn]

On Tue, 28 Jun 2005 12:52:48 -0700, frizzle wrote:

Hi there,

Short question:
Is there any way, to reverse the result of a mysql query?

Explanation:
If i have eg. 20 records, all with their own id of course,
select 5 with limit, and order them DESC by ID, it should give
me: 20, 19, 18, 17, 16. That's all ok.
But now i want 16 to appear first, 17 second, etc.

You can read the result set in reverse order

Use
http://uk.php.net/manual/en/function.mysql-num-rows.php

to find the number of rows, then use a for loop to count backwards reading
each field using:

http://uk.php.net/manual/en/function.mysql-result.php

[Tony]

frizzle wrote:

Hi there,

Short question:
Is there any way, to reverse the result of a mysql query?

Explanation:
If i have eg. 20 records, all with their own id of course,
select 5 with limit, and order them DESC by ID, it should give
me: 20, 19, 18, 17, 16. That's all ok.
But now i want 16 to appear first, 17 second, etc.

You want to change the order it displays in, or you want to display it one
way first, then the other way?

You could read the records into an array, but I'm wondering at the
processing power being used. It may be more efficient to simply reissue the
query with a modification to the ORDER BY clause. Code-wise, it would
certainly be simpler...

--
Tony Garcia
Web Right! Development

[frizzle]

Wow thanks for all the replies!!!!
I used Andy Hassall's way, and it works really good!
There is a maximum of 10 results, so i don't
expect any heavy calculations in this ...

Now there is another thing i cant figure out:
i need to update multiple rows, in 1 query.

It has to have this effect:

UPDATE pictures SET comment = 'this comment is really funny' WHERE id =
'$id1' LIMIT 1
UPDATE pictures SET comment = 'this comment is also funny' WHERE id =
'$id2' LIMIT 1

Somehow it keeps giving me an error.
now i run the query seperately for each pic, but i know
there has to be a smoother way ...

Thanks again!

[Tony]

frizzle wrote:

Wow thanks for all the replies!!!!
I used Andy Hassall's way, and it works really good!
There is a maximum of 10 results, so i don't
expect any heavy calculations in this ...

Now there is another thing i cant figure out:
i need to update multiple rows, in 1 query.

It has to have this effect:

UPDATE pictures SET comment = 'this comment is really funny' WHERE id
= '$id1' LIMIT 1
UPDATE pictures SET comment = 'this comment is also funny' WHERE id =
'$id2' LIMIT 1
Are you updating all of them to read the same?

UPDATE pictures SET comment='this comment is not funny' WHERE 1=1

Are you updating SOME of them to read the same?

UPDATE pictures SET comment='this comment is stupid' WHERE id='$id1' OR
id='$id2' OR id='$id3'...

Or, as it appears from above, you are updating them to read differently?

If the latter, then, sorry, but you'll have to run them one at a time.
Somehow it keeps giving me an error.

( BTW - what gives you an error? )

--
Tony Garcia
Web Right! Development

[frizzle]

It's the latter. The query as i gave as example kept giving me an
error.
But i'll run them 1 by 1.
Shouldn't be a big problem with a max of 10 x 550 chars, but i wondered
if there was a slicker way of handling this.
Guess not, but my original problem is solved, and i'm very statisfied
with that!

Thanks guys!

Greetings Frizzle.

[BearItAll]

On Tue, 28 Jun 2005 13:53:45 -0700, frizzle wrote:

Hmm, i've thought of your second option, but i wondered if i could change
things closer to the information's root ...
The Mysql version is recent, but i don't know what you mean with subquery.
I also figured i could get the total possible results, substract number of
uploaded pictures, and then use that as the offset within Limit.
Or would that make things slow (in quite a small db)

Thanks again!

I would of said that Andy's pass to an array would be right for the amount
of data you are working on. Alternatively to keep the return from your
query so that you can reuse it, which I think is what you mean from your
post, I would say that using a MySQL temporary table would be just as
reasonable (as good as an array, as they say).

Remember that future re-queries on that sub-set table are going to be much
faster than your original query on the main data table, so that reordering
it for display purposes will have little overhead, or sub filtering for
display.

But still, for your quantity of records then an array may be at least as
good.