• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

Mysql - select continuous ID's

Hi,

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
0
generationgav
Asked:
generationgav
1 Solution
 
AlexSoftware EngineerCommented:
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.
0
 
generationgavAuthor Commented:
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.
0
 
AlexSoftware EngineerCommented:
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.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
generationgavAuthor Commented:
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"

0
 
Steve BinkCommented:
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.
0
 
Beverley PortlockCommented:
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;
                     break;
               }
     }

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

if ( $startId == -1 )
    echo "None found";
else
......
0
 
AlexSoftware EngineerCommented:
did you have any "ORDER BY ID ASC" inside your sql statement?
0
 
generationgavAuthor Commented:
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now