Increment Question?

[barkster]

I'm trying to figure out the best way to increment a field and return the new number back to the users. I'm trying to keep track of who contacted a user first and need to number them.  I want the first user to contact a user to be number one and so on but want to make sure the fastest and most reliable way to do it.  Any suggestions?  Thanks

[jworisek]

can you just save the date the contact was made and then sort based on dates to get the number?

[fenway]

Agreed... no need to store this number anywhere with the raw data; at worst, you can generate a temporary table if you need to consult it often.

[barkster]

I guess I could do a timestamp or something but I really need to keep track of who contacted first and have them numbered

[fenway]

You can report on this information afterwards.

[SQL_F1]

I get the sense that the problem is not being fully described - execute
[code]describe [i]tablename[/i][/code]
queries for the relevant tables and describe the problem refrring to the queries' output.

[barkster]

Ok I'll try and explain a little better

I have a Table called Topics in which users respond to the topic.  When they respond I need to be able to track who responded first, second, third, etc...  When a users responds I need to increment some field and use that number to send out an email so the topic starter will know who contacted them first.  I know they can just sort their emails by received but I need to have this number as the first character in the subject for future reference.  I have now idea on how to implement this without doing extensive amount of queries.  I'm looking for one - two queries max.  If I could do a UPDATE Topics SET Count=(Count+1) and get the original count all in the same query that would work for me I think but don't know how

[fenway]

Again, why isn't a DATETIME column sufficient?

[barkster]

Datetime might work for somebody who isn't trying do what I'm trying to do.  If you know a way to convert a datetime into a number then I'm all ears.  I would love to know how to do what I'm trying to do with a datetime field, doesn't accomplish a numbering scheme. I'm sure I could do it if I inserted a record using a datetime, then do a lookup and somehow figure out what position they are in the recordset?

[fenway]

You can always unix_timestamp() your datetime field to get an integer value.  And yes, the idea is that you record the date/time for every reply to a topic, and then run a simple query to find the earliest date for a particular thread.  I don't see what the problem is.