Here it the code from @DTest's Answer
SET @folder = 1;
SET @items = 0;
SELECT
id, num_items,
(SELECT IF(((@items:=@items+num_items)>100), @folder:=@folder+1, @folder)) as folder,
IF(@items>100,@items:=0,@items) as checkItems
FROM foo;
Staring at this query, I would shudder to even think about writing of a Stored Procedure Technique for fitting items in folders. Then, it hit me ...
You could simply take the same query and ORDER BY num_items
. That would actually pack as many items into a folder as possible before switching to another folder.
mysql> use ali
Database changed
mysql> CREATE TABLE foo (
-> id tinyint unsigned primary key AUTO_INCREMENT,
-> num_items tinyint unsigned
-> );
Query OK, 0 rows affected (0.24 sec)
mysql>
mysql> INSERT INTO foo VALUES (1,4),(2,33),(3,74),(4,44),(5,24),(6,34),(7,46),(8,55),(9,11);
Query OK, 9 rows affected (0.17 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> SET @folder = 1;
Query OK, 0 rows affected (0.03 sec)
mysql> SET @items = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT id, num_items, (SELECT IF(((@items:=@items+num_items)>100),
-> @folder:=@folder+1, @folder)) as folder,
-> IF(@items>100,@items:=0,@items) as checkItems
-> FROM foo ORDER BY num_items;
+----+-----------+--------+------------+
| id | num_items | folder | checkItems |
+----+-----------+--------+------------+
| 1 | 4 | 1 | 4 |
| 9 | 11 | 1 | 15 |
| 5 | 24 | 1 | 39 |
| 2 | 33 | 1 | 72 |
| 6 | 34 | 2 | 0 |
| 4 | 44 | 2 | 44 |
| 7 | 46 | 2 | 90 |
| 8 | 55 | 3 | 0 |
| 3 | 74 | 3 | 74 |
+----+-----------+--------+------------+
9 rows in set (0.08 sec)
mysql>
This packing would be somewhat lopsided in iterms of the number of entries per folder but the sum of the items per folder are pressed as close to 100 as possible.
Please note that if you want later folder with more items, simply change ORDER BY num_items
to ORDER BY num_items DESC
.
Give it a Try !!!
UPDATE 2013-01-09 12:52 EDT
What you need are three(3) mechanisms:
- MECHANISM #1 : You need to imbed the query in a subquery
- MECHANISM #2 : Order the subquery output by folder,num_items
- MECHANISM #3 : Use a variable to monitor when you change folders from each row. When such a change occurs, you simply reset the folder counter to 1.
Here is the new code:
SET @order_val = 0;
SET @folder = 1;
SET @curr_folder = 1;
SET @items = 0;
SELECT *,
@order_val:=IF(folder=@curr_folder,@order_val+1,1) ordervalue,
@curr_folder:=folder current_folder
FROM
(
SELECT * FROM
(
SELECT
id, num_items,
(SELECT IF(((@items:=@items+num_items)>100),
@folder:=@folder+1, @folder)) as folder,
IF(@items>100,@items:=0,@items) as checkItems
FROM foo
) AA ORDER BY folder,num_items
) A;
Here is it execution:
mysql> SET @order_val = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @folder = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @curr_folder = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @items = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT *,
-> @order_val:=IF(folder=@curr_folder,@order_val+1,1) ordervalue,
-> @curr_folder:=folder current_folder
-> FROM
-> (
-> SELECT * FROM
-> (
-> SELECT
-> id, num_items,
-> (SELECT IF(((@items:=@items+num_items)>100),
-> @folder:=@folder+1, @folder)) as folder,
-> IF(@items>100,@items:=0,@items) as checkItems
-> FROM foo
-> ) AA ORDER BY folder,num_items
-> ) A;
+----+-----------+--------+------------+------------+----------------+
| id | num_items | folder | checkItems | ordervalue | current_folder |
+----+-----------+--------+------------+------------+----------------+
| 1 | 4 | 1 | 4 | 1 | 1 |
| 2 | 33 | 1 | 37 | 2 | 1 |
| 5 | 24 | 2 | 68 | 1 | 2 |
| 4 | 44 | 2 | 44 | 2 | 2 |
| 3 | 74 | 2 | 0 | 3 | 2 |
| 6 | 34 | 3 | 0 | 1 | 3 |
| 7 | 46 | 3 | 46 | 2 | 3 |
| 9 | 11 | 4 | 11 | 1 | 4 |
| 8 | 55 | 4 | 0 | 2 | 4 |
+----+-----------+--------+------------+------------+----------------+
9 rows in set (0.00 sec)
mysql>
Give it a Try !!!
There are a couple of ways you could do this
a. Geocode your postcodes and calculate the distance between the 2 points (or use an existing API to do this). There are several places you can geocode small quantities of addresses for free, or you can purchase databases. Note that this method is not entirely trivial, but is probably the best for the long term.
You can get Geocoding done via Google at https://developers.google.com/maps/documentation/geocoding/
Then, to calculate the distance, use https://developers.google.com/maps/documentation/distancematrix/
b. Manually set which zip codes are bordering other zip codes in the database. You say you are supplying predefined zip codes so this may be a simpler solution (though for a large number of zip codes it would be costly)
The idea is that you have a new table zip_neighbours as follows
Zip code, neighbour
100, 101, 102, 103
101, 100, 102, 104
102, 100, 101, 104
This wont give an accurate '75 miles' boundary, but can be used as a pretty good indication. When a user wants to include nearby zip codes you lookup the neighbours in this table and include them in the search.
Best Answer
Let's analyze how much effort
COUNT(*)
will take...For those numbers, either way will probably work. Counting 500 rows is likely to take only a fraction of a second. If you don't need to do that a million times a day, you probably won't run out of performance.
Either approach requires performing some form of test every time the user takes a ticket, so this does not seem to be a limiting factor.