Mysql - select continuous ID's

Posted on 2007-10-01
Last Modified: 2013-12-12

I want to do a query such as the following;
SELECT * FROM Bookings WHERE Status=0 LIMIT 3

But I want the results to be continuous in the database, so the following;

Id     Status    SeatNo
1        0         1
2        1         2
3        1         3
4        0         4
5        0         5
6        0         6

Will bring back 4, 5 and 6 And not 1, 4, 5 as it currently does.

Is there any optimal way of doing this rather than doing the query then checking if it's ok and if not then doing a reselect from the 2nd ID
Question by:generationgav
    LVL 9

    Expert Comment

    if you want all the records that has their status as 0 why you set a LIMIT?Remove the limit=3 and then you will take all the records you want.
    LVL 1

    Author Comment

    I require a limit, as there is going to be millions of records.

    I want people to be able to select between 1 and 15 records; but they need to be continuous. Once they're selected they could be freed again (as people can cancel their bookings) but if there's a slot of 3 freed and then somebody wishes to book 5, I want it to ignore the slot of 3 and carry on looking.
    LVL 9

    Expert Comment

    so you can set a limit to present your records and make a pagination so you will be able to present some pages and 3 records or more in each page.
    LVL 1

    Author Comment

    The issue is, in a normal select you would get "1,4,5" But I want the ID's to be CONTINUOUS so if you did LIMIT 1 it'd bring back "1" but with LIMIT 3 it would bring back "4,5,6"

    LVL 50

    Expert Comment

    by:Steve Bink
    I think this could be done inside SQL, but the query would be slow, cumbersome, and not easily extensible.  Perhaps the solution lies in coding a stored procedure.  The intricate logic certainly points to code vs. SQL, but the amount of potential return makes any outside manipulation a slow process.
    LVL 34

    Accepted Solution

    I do not think that tackling this in SQL makes any sense. The programmatic element means that PHP (or some language) would be better suited.

    I would tackle this as follows;

    1. Make sure that "status" has an index on it as "status, id"
    2. Read just the ID number for a large set of data
    3. Within PHP look to for a continuous set of IDs.

    So, it would look a bit like this (all untested) code

    $groupSize = 3;
    $start = 0;
    $startId = -1;

    do {
         $rs = @mysql_query("select id from Bookings where status='0' limit $start,10000");
         if ( $rs ) {
              $arr = array();
              while ( $rw = @mysql_fetch_array( $rs, MYSQL_NUM ) )
                   $arr [] = $rw[0];

              foreach( $arr as $idx => $id )
                   if ( ($arr[$idx+$group] - $arr[$idx]) == ($group - 1) ) {
                          // Success - found my group
                         $startId = $id;

         $start += 10000;
    } while ( $start < $num_records_in_db );

    if ( $startId == -1 )
        echo "None found";
    LVL 9

    Expert Comment

    did you have any "ORDER BY ID ASC" inside your sql statement?
    LVL 1

    Author Comment

    Sorry, I had forgotten my ORDER BY, but meant to be there.

    Thank you bportloc; unfortunately not the answer I was hoping for, but certainly the one I was expecting, and slightly better than my solution too!                

    I was going to compare one row with the next, and if they were continuous repeat until I found 3 which were continuous.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now