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.
Advice on how to approach this? (calculate availability?)
Moderator: General Moderators
-
TheBrandon
- Forum Commoner
- Posts: 87
- Joined: Tue May 20, 2008 8:55 am
Re: Advice on how to approach this? (calculate availability?
Here is how I would do it.
Make two tables: one for clients, the other for cards.
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.
To find the cards that are not assigned or have expired (are more than one year old), use this query:
Edit: This post was recovered from search engine cache.
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 | | | |
# +---------------+------------------+------+-----+---------+-------+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 |
# +---------+-----------+---------------------+Code: Select all
SELECT * FROM `card` WHERE `client_id` IS NULL OR DATE(`register_date`) < DATE_ADD(CURDATE(), INTERVAL -1 YEAR)