473,238 Members | 1,905 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 software developers and data experts.

Reverse mysql-result

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.

Jul 17 '05 #1
9 4446
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
Jul 17 '05 #2
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!

Jul 17 '05 #3
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
Jul 17 '05 #4
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
Jul 17 '05 #5
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
Jul 17 '05 #6
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!

Jul 17 '05 #7
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
Jul 17 '05 #8
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.

Jul 17 '05 #9
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.

Jul 17 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

35
by: Raymond Hettinger | last post by:
Here is a discussion draft of a potential PEP. The ideas grew out of the discussion on pep-284. Comments are invited. Dart throwing is optional. Raymond Hettinger ...
59
by: Raymond Hettinger | last post by:
Please comment on the new PEP for reverse iteration methods. Basically, the idea looks like this: for i in xrange(10).iter_backwards(): # 9,8,7,6,5,4,3,2,1,0 <do something with i> The...
3
by: engsolnom | last post by:
I need to use the built-in method 'reverse', but notice strange behavior. Foe example: print my_list.reverse() doesn't work. new_list = my_list.reverse() doesn't work. my_list.reverse()...
8
by: Jim Langston | last post by:
I have a class I designed that stores text chat in a std::vector<sd::string>. This class has a few methods to retrieve these strings to be displayed on the screen. void ResetRead( bool Reverse,...
14
by: ford_desperado | last post by:
Why isn't ALLOW REVERSE SCANS the default? Why do we have to - drop PK - create an index - recreate PK What are the advantages of indexes that do not allow reverse scans?
15
by: Fady Anwar | last post by:
Hi while browsing the net i noticed that there is sites publishing some software that claim that it can decompile .net applications i didn't bleave it in fact but after trying it i was surprised...
5
by: VHfc | last post by:
I'm quite experienced in JavaScript, but needed some PHP to access plain text data on my server. No sweat to communicate the file name from JS by the POST/GET method to my PHP snipplet, and to read...
20
by: mike7411 | last post by:
Is there any easy way to reverse the order of the bits in a byte in C++? (i.e. 00000001 becomes 10000000)
41
by: rick | last post by:
Why can't Python have a reverse() function/method like Ruby? Python: x = 'a_string' # Reverse the string print x Ruby: x = 'a_string' # Reverse the string
3
seshu
by: seshu | last post by:
hi guys this might be a simple one for you but here i badly need it i have a table in mysql which has a column with data type date there date format is yyyy-mm-dd but here in vb date formate is...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.