NYCPHP Meetup

[nycphp-talk] every other record

max goldberg max.goldberg at gmail.com
Wed Feb 1 22:19:11 EST 2006


If post processing is a large concern and you'd rather figure it out on the
database side, you could try making a stored procedure. Using a cursor would
allow you to loop through every row and use a counter to get every other
row, effectively solving both the problem of holes in your ID numbers and
wanting to do this server side. I guess the most puzzling piece for me is
why you actually need to do this.

Now I haven't actually tested this so it comes with no warranty and probably
doesn't work but here's a quick pseudo stored procedure that might clear it
up for you:

 CREATE PROCEDURE getEveryOther(IN starting_id INT)
     BEGIN
       DECLARE complete      INT DEFAULT 0;
       DECLARE rank       INT DEFAULT 0;
       DECLARE current_id INT;
       DECLARE cur1 CURSOR FOR SELECT foo_id FROM foos WHERE foo_id >=
starting_id LIMIT 10;
       DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET complete = 1;

       OPEN cur1;
       WHILE complete = 0 DO
         FETCH cur1 INTO current_id;
           SET rank = rank+1;
           IF MOD(rank, 2) = 1 THEN
          SELECT current_id;
           END IF;
       END WHILE;

       CLOSE cur1;
     END;

Cheers and good luck!

On 2/1/06, Rahmin Pavlovic <rahmin at insite-out.com> wrote:
>
>
> On 2/1/06 3:19 PM, "Chris Merlo" <chris at theyellowbox.com> wrote:
>
> > If you have a table with an auto-generated ID, where you have ever
> deleted a
> > record, in my experience with MySQL, the deleted ID won't get reused,
> and so
> > your ID values will be 1, 2, 3, 5, 6, 7, etc...  Therefore, that first
> line
> > will grab records with IDs 1, 3, 6, 8, etc...
>
> Is that not still every other record?  (I don't particularly need every
> even
> or odd record id, just every other entry.)
>
> I don't particularly mind performing logic on the recordset after the
> fact,
> but I would rather put the processing weight on the SQL side (more out of
> principle than anything else).
>
>
> _______________________________________________
> New York PHP Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
> New York PHP Conference and Expo 2006
> http://www.nyphpcon.com
> Show Your Participation in New York PHP
> http://www.nyphp.org/show_participation.php
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20060201/7dfd3450/attachment.html>


More information about the talk mailing list