auto increment per date

temp2escape

hi guys,

this is what I'm trying to achieve. A custom formatted reference number.

K3 - 22/5 - 01

The K3 part is a permanent character. No problem.

While the 22/5 is the form submitted date. This is also not a problem.

The last one 01. This is an auto increment number based on the submitted date.

For example, a user submit a post. Appeared to be the second post for that particular date. So the code will be, K3-22/5-02 and so on.

When is starts with a different date (tomorrow), the auto increment will start back with 01.

dagon

using the auto incremented id in a db would the common way

Piranha

Why do you want it stored in this way and not with an auto incremented id? I just assume it is the key of a database table.

temp2escape

will using the db auto increment work? i dont think so.

If i set it on the db... the number will keep increasing regardless of the date inserted.

for example:

2 post sumitted on 22 may 08

K3-22/5-01
K3-22/5-02

1 post submitted on the next day

K3-23/5-03

This is what will happen if I set the db to auto increment.

I want the value to be reset on a new date. Not continuing the number from the date before.

NogDog

I think what the others are getting at is the question of whether or not all this is necessary? I'm sure if we think about it long enough we can come up with some solution. But if such a solution is not really necessary, why add the extra complication to the code (with the possible maintenance problems it could introduce)?

If you have an auto-increment field in the DB for this table, you already have a unique identifier. If you have a datetime field for when the entry was inserted, you have the info there already for the date. Both those fields will be appropriately typed and easily manipulated by applicable database functions. If you need a report of all items inserted on a given date or listed in the order in which they were inserted, that is then a trivial matter with a WHERE or ORDER BY clause using that date/time field. If there is truly a business need for a an identifier such as you have described, you could create it "on the fly" when you actually need it.

Query to get items for a given date:

SELECT * FROM table_name WHERE DATE(datetime_col) = '2008-05-23' ORDER BY id

Then when outputting the special identifier, it could be generated "on the fly" by putting the parts together, e.g.:

$id = 'K3-' . date('j/n', strtotime($result['datetime_col'])) . '-' . substr(str_pad($result['id'], '0', 2, STR_PAD_LEFT), -2);

dagon

another col, a trigger and a stored procedure may be an idea, seems like an overly elaborate requirement.

HalfaBee

Why not just count the posts for day and use that value+1.
It adds another query, but ...

Piranha

HalfaBee wrote:
Why not just count the posts for day and use that value+1.
It adds another query, but ...

Unfortunatley that is a bad idea. There is a possibility that two scripts run this at the same time and mess things up, as below:

[b]Script 1           Script 2[/b]
posts: 5           --------
--------           posts: 5
save: 6            --------
--------           save: 6

In other words, there is a possibility that you get the same number on many posts.

HalfaBee

table locking?

leatherback

You can store it in a database:
Store timestamp & have an autoincrement value. AFTER storing the record in the database, you select all records for that day, where the ID is less or equal to the current id, group by day, and select the count(id). The cun(id) will give you the 1-based auto-incremented value for that day.

2 queries, but well.. It gives you the value..

Piranha

HalfaBee wrote:
table locking?

Why didn't you suggest that to start with? It is hard for temp2escape to know about it when noone says anything. :p

It could work. But there are a few issues:

The user doesn't seem to be a DB wizard, and suggesting table locking will probably confuse him.
There may be a performanse issue if there are lots and lots of visitors at the page. The risk is small indeed, but if the page will be a big hit in the future it is better to avoid possible performanse problems to start with.

HalfaBee

It also has problems if posts are deleted. <sigh>

temp2escape

i think i got the idea already. by looking at wordpress files on how it counts the number of comments received based on a particular post.