groupby msql query

Question

Hi

I have a bunch of ID's. I can assign them once a day.

I need to query my database, see if they're any ID's available to hand out (if I didn't hand out all of them in a 24 hour period), and if there's any available, write a new row to the database saying that the ID has been handed out. I also need to know which user held which ID at a certain time.

The only way I can figure this is by creating two tables - one active_ids (contains who owns a particular ID right now) and one id_records (contains who owned IDs currently and in the past). Then I would say something like:

UPDATE active_ids SET user_id=xyz WHERE UNIX_TIMESTAMP(current_timestamp) - UNIX_TIMESTAMP(date) > 84600 LIMIT 1;

and then pusing another record to id_records with the data.

Is there a way I can do this with one table? I'm using PHP and MySQL.

Thanks!

Answer 1

Sure, provided the user id's aren't the actual primary key, you could just timestamp all the user_id creations and then search using time-based queries to see what is available.

Answer 2

You can have a "day" column of type DATE and a "user_id" column, and use a composite primary key of (day,user_id).

Or what I suspect might be a better idea: have a genuinely unique id, then have call the non-unique id an "access code" instead and have a composite unique constraint.