I ran into this same type of question the other evening at a local
I think that once you reach the end of the results set the only way to
start back at the beginning of that results set is to do the query
Once option - do your query and grab all the rows
load them into a data structure - ie - a list of associative arrays
Then all you need to do is incrementally go through the array.
when you hit the end, just reset the index back to 0.
From: Steffan A. Cline [mailto:steffan@stripped]
Sent: Friday, May 12, 2006 2:52 PM
Subject: Re: Returning records in a circle
Well, basically it can be done to an extent in some kind of code.
I am drawing from a table 2 records at a time. I want to make sure that
records are pulled at least once. If at all possible not to have 2 from
So, in this case.
I have columns id, html, vendor
So I can add columns as needed. I tried adding a flag and after
the client the 2 records I'd mark it as flag = 1 then search like this
Select id, html from urls order by flag, id desc limit 3
Then after I have those I would then set the last of the 3 to flag = 1
that on the next search I get the 2 after. In theory it worked fine but
multiple people hit the page simultaneously I had flags in different
and not in order. Maybe just mark them as flag = 1 after returned and
on search if found is 0 then set all to flag = 0 so they can be seen
This doesn't seem so bad but them I guess I'd use distinct? If I cant
distinct with other parameters...
ex: select id,html distinct(vendor) from urls where flag=0 limit 2;
Would it be like :
Select id,html from urls where flag = 0 group by distinct(vendor) limit
T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
Steffan@stripped Phoenix, Az
AIM : SteffanC ICQ : 57234309
The Executive's Choice in Lasso driven Internet Applications
Lasso Partner Alliance Member
> From: Rhino <rhino1@stripped>
> Date: Fri, 12 May 2006 14:20:10 -0400
> To: "Steffan A. Cline" <steffan@stripped>, <mysql@stripped>
> Subject: Re: Returning records in a circle
> ----- Original Message -----
> From: "Steffan A. Cline" <steffan@stripped>
> To: <mysql@stripped>
> Sent: Friday, May 12, 2006 1:38 PM
> Subject: Returning records in a circle
>> Is there a way I can get a set of records incrementally such as to
>> then the next query get the next 2 then at the end of all records to
>> 2 from the beginning? I need to keep going incrementally by 2 in a
> Are you trying to get these rows purely via SQL at the command line or
> SQL script? Or would an application be an option for you?
> If you are not willing to consider application code to grab the rows
> want, the answer to your question is "maybe". SQL has always been
> to return ALL of the rows that satisfy a query with a single
> the query, no matter how many rows that is. So if your query says:
> select * from mytab;
> you will normally get all of the rows that satisfy that query in one
> whether there are 0 rows, 100 rows, or a 100 million rows in the
> You _might_ be able to get the results you want by using the LIMIT
> I'm not sure what version of MySQL you are using but the LIMIT clause
> described in the MySQL 3.23/4.0/4.1 manual on this page:
> http://dev.mysql.com/doc/refman/4.1/en/select.html. The problem is
> you'll still pretty much need some sort of script in order to keep
> the query to get the next two records and you may need to change the
> parameters of the LIMIT clause at the same time.
> If you are willing to write application code, things get a lot easier.
> instance, a Java program could easily grab rows from a result set for
> two at a time, let you process them, then grab two more, etc. I expect
> it would similarly easy to do the same thing in Perl and PHP and C.
> In short, a program gives you a lot more ability to do what you want
> with your database data. But some shops have very little programming
> expertise and prefer to do everything via SQL. If you work for one of
> shops, you might not be able to get your records two at a time with
> alone, unless you can write a script that takes advantage of the LIMIT
> I don't pretend to know MySQL exhaustively so someone else may have
> suggestion for you but the only two approaches I can think of that
> meet your needs are to use the LIMIT clause or to write an
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.392 / Virus Database: 268.5.6/337 - Release Date:
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql