Page 1 of 1

Advice on how to approach this? (calculate availability?)

Posted: Thu Apr 30, 2009 12:58 pm
by TheBrandon
Hello all,

I have a client who has a membership option. They pre-print cards numbered 1-1600 and give them a card when they join. Members like to keep the same # because they use it when they call in to make a reservation.

Now they don't want to buy 10,000 cards, so they recycle the numbers. If you don't register for the next year, your # goes up for grabs.

I need to know how to figure out the available numbers in PHP/SQL?

I have a row called "member_num" where I have entered some random numbers. 1, 666, 1215, 45, etc. How do I gather all of those up and loop through them until an available number is found?

I imagine it would be something like this:
Select all of the numbers and put them into an array
Starting with 0, loop through SQL queries ("Select * Where member_num=$num AND status=1) and if there is a result, increment +1 until we don't get a result.

Is this the best way to approach this? Is there a more efficient way of discovering "empty" numbers?

Also if this is how I should do it, how do I move the array forward as well as the $num variable?

Let's say we have these numbers already taken: 1, 2, 5

If I do a query on "select where id=1" it will return a result.

How do I move on to the next array and say "select where id=2" until I get an empty result?

Normally I would do the (while X > i){Pull data and increment} kind of approach, but is that the best thing for this particular instance?

Thanks for the help. I'm sorry this isn't a more code-based question but I'm just trying to figure out HOW to do it before I try.

Re: Advice on how to approach this? (calculate availability?

Posted: Fri May 01, 2009 12:42 am
by McInfo
Here is how I would do it.

Make two tables: one for clients, the other for cards.

Code: Select all

EXPLAIN `client`;
# +-------------+------------------+------+-----+---------+----------------+
# | Field       | Type             | Null | Key | Default | Extra          |
# +-------------+------------------+------+-----+---------+----------------+
# | client_id   | int(10) unsigned | NO   | PRI |         | auto_increment |
# | client_name | varchar(32)      | NO   |     |         |                |
# +-------------+------------------+------+-----+---------+----------------+
 
EXPLAIN `card`;
# +---------------+------------------+------+-----+---------+-------+
# | Field         | Type             | Null | Key | Default | Extra |
# +---------------+------------------+------+-----+---------+-------+
# | card_id       | int(10) unsigned | NO   | PRI |         |       |
# | client_id     | int(10) unsigned | YES  |     |         |       |
# | register_date | datetime         | YES  |     |         |       |
# +---------------+------------------+------+-----+---------+-------+
The two tables might hold the following data. The card table holds every card (1-1600). If a card is not assigned to a client, the client_id and register_date for that client are null, but the row remains in the table because the card still exists.

Code: Select all

SELECT * FROM `client`;
# +-----------+-------------+
# | client_id | client_name |
# +-----------+-------------+
# |         1 | Missy       |
# |         2 | Bob         |
# +-----------+-------------+
 
SELECT * FROM `card` LIMIT 0, 4;
# +---------+-----------+---------------------+
# | card_id | client_id | register_date       |
# +---------+-----------+---------------------+
# |       1 |         1 | 2008-02-15 12:16:09 |
# |       2 |      NULL | NULL                |
# |       3 |         2 | 2008-10-08 22:12:58 |
# |       4 |      NULL | NULL                |
# +---------+-----------+---------------------+
To find the cards that are not assigned or have expired (are more than one year old), use this query:

Code: Select all

SELECT * FROM `card` WHERE `client_id` IS NULL OR DATE(`register_date`) < DATE_ADD(CURDATE(), INTERVAL -1 YEAR)
Edit: This post was recovered from search engine cache.