CODEWITHSUNDEEP
phpsqlmysqlgroup-by
clicker
clicker

Reputation: 1

groupby msql query

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!

Upvotes: 0

Views: 88

Answers (2)

Draemon
Draemon

Reputation: 34711

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.

Upvotes: 0

Devin Ceartas
Devin Ceartas

Reputation: 4829

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.

Upvotes: 1

Related Questions