List:General Discussion« Previous MessageNext Message »
From:Steffan A. Cline Date:May 12 2006 6:51pm
Subject:Re: Returning records in a circle
View as plain text  
Well, basically it can be done to an extent in some kind of code. Basically
I am drawing from a table 2 records at a time. I want to make sure that all
records are pulled at least once. If at all possible not to have 2 from the
same vendor.

So, in this case.

I have columns id, html, vendor

So I can add columns as needed. I tried adding a flag and after returning to
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 so
that on the next search I get the 2 after. In theory it worked fine but when
multiple people hit the page simultaneously I had flags in different places
and not in order. Maybe just mark them as flag = 1 after returned and then
on search if found is 0 then set all to flag = 0 so they can be seen again?
This doesn't seem so bad but them I guess I'd use distinct? If I cant use
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 2




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                                  USA
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 get 2
>> then the next query get the next 2 then at the end of all records to get
>> the
>> 2 from the beginning? I need to keep going incrementally by 2 in a circle.
> Are you trying to get these rows purely via SQL at the command line or in an
> SQL script? Or would an application be an option for you?
> If you are not willing to consider application code to grab the rows you
> want, the answer to your question is "maybe". SQL has always been intended
> to return ALL of the rows that satisfy a query with a single invocation of
> 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 go,
> whether there are 0 rows, 100 rows, or a 100 million rows in the result.
> You _might_ be able to get the results you want by using the LIMIT clause.
> I'm not sure what version of MySQL you are using but the LIMIT clause is
> described in the MySQL 3.23/4.0/4.1 manual on this page:
> The problem is that
> you'll still pretty much need some sort of script in order to keep executing
> 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. For
> instance, a Java program could easily grab rows from a result set for you
> two at a time, let you process them, then grab two more, etc. I expect that
> 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 to do
> 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 those
> shops, you might not be able to get your records two at a time with SQL
> alone, unless you can write a script that takes advantage of the LIMIT
> clause.
> I don't pretend to know MySQL exhaustively so someone else may have another
> suggestion for you but the only two approaches I can think of that might
> meet your needs are to use the LIMIT clause or to write an application.
> --
> Rhino
> -- 
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.392 / Virus Database: 268.5.6/337 - Release Date: 11/05/2006

Returning records in a circleSteffan A. Cline12 May
  • Re: Returning records in a circleRhino12 May
    • Re: Returning records in a circleSteffan A. Cline12 May
  • Using a stored procedure inside a viewChris Carrier12 May
  • Re: Returning records in a circleScott Haneda12 May
RE: Returning records in a circleGeorge Law12 May
  • Re: Returning records in a circleRhino12 May