CODEWITHSUNDEEP
phpmysqlpdo
Necro.
Necro.

Reputation: 987

PHP/MySQL/PDO Library - Distributing Information Out Of A Database? What's the most efficient way?

I have keys for a project I made where I am trying to test a licensing system (Just for fun, and learning) a part that I thought I'd run into, is how to distribute the keys. I have about 100 keys in a database, and I'm trying to figure out the best way to distribute them. The database is layed out as follows,

ID (Auto Increment) | key

Using the PDO library, what is the most effective way to either to go in chronological order by ID? But even if I did chronological order, when I deleted the key that was given out, how would I go in chronological order? Or maybe random ID number? I have no clue how to go about the most effective way to distribute these keys?

Upvotes: 1

Views: 108

Answers (2)

Mark Grey
Mark Grey

Reputation: 10257

Chronology isn't exactly a feature of PDO, or for that matter whatever database driver you are using... it's more a matter of your schema.

Typically, a commonly employed field in any database structure is a "timestamp" or "created" field that holds the time the record was created in the database. These fields can be MySQL datatype TIMESTAMP (in which case the driver will return seconds since the Unix Epoch), or DATETIME (in which case most drivers will attempt to return the language's native DateTime object if one exists.) Even though monotonically-increasing primary keys imply a certain amount of chronological order when sorted, a timestamp field can record the exact time a record was created at the server, as well as update on change using ON UPDATE CURRENT_TIMESTAMP. So I would suggest adding this to your schema.

With such a field in your database, you can always sort your queries using:

SORT BY timestamp_field_name ASC

Also, if by "distribute" you mean some data will be publicly accessible by using this key as query param of some sort, I wouldn't use the monotonic primary key for the exact reason you described, especially if this is a "licensing" proof of concept, which if you mean a DRM-type thing should probably produce a complex string. Hashed timestamps in a UNIQUE field, or the php uniqid function can produce values that can be stored in a VARCHAR database field with the UNIQUE key restraint. This is if I have understood your described goal.

Upvotes: 1

Leng
Leng

Reputation: 2998

If I understand your question correctly...

You might try this query through PDO:

SELECT * FROM `table-name`
ORDER BY `ID` ASC

Then when you step through the rows in a while() loop from the execution's return, it will be in chronological order like you asked.

As far as losing ID's, like if you delete the key with ID # 10, your table will jump from 9 to 11 in the returned rows IDs. When you add a new key, # 10 will not be used unless you specifically specify that ID when inserting.

EDIT: From the phrasing of your question, it sounds like you may be concerned about how you set up the ID's for the keys. Maybe you understand this already, but since you have Auto Increment, your IDs will be automatically generated when you insert new keys, so a new key would be assigned an ID of (ID of last inserted key) + 1.

Upvotes: 2

Related Questions