UBB.Developers Forums Community Development Programming MySQL and Server Configuration Looking for some SQL help...

I'm trying to devleop a Trouble Ticket forum. I have Most everything working but I need to be able to append a sequentially incremental number to the beginning of the Subject.
So First post would be

#1 - Subject

Second...

#2 - Subject here...

and so on.

I know how to modify the subject line at the time of posting, but my SQL sillz are um...non-existant. Anyone able to give me an idea of how to go about this?

Thanks...

P-

Would every post or every thread have its unique number?

Yes, each post would have a unique number. I can't use the actual post number though as this is not the only forum, and I need them to be sequential so I can more easily track them...

P-

This actually wouldn't be incredibly difficult, might take some time to do. I've done something similar with my custom calendar events system. Though I don't append a number to the Subject, I do generate a unique ID that ties the two together so the event can be linked via a custom URL in the thread.

Are you planning on using a 2nd table to just track the trouble tickets, or do you want to keep using the Posts table in threads and handle each ticket as a post?

[edit: too early to be a guud spellur ]

The threads will remain as posts. The incremental number is just for in house administrative tracking.

[] Would every post or every thread have its unique number?[/]

Just the initial post for each thread would get this number. I've already modified the addpost.tmpl and .php files sort of like the Modsubmit board here.

P-

I would think that I would just need a new table to hold the counter number, and then pull that number, prepend it to the subject line, increment the number and then write it back to the table. Now, I have no idea how to do most of that. Can you guru's let me know if there's a flaw in my logic...or just crsh the idea as to dificult to worry about

I presume you distinguish between records with a primary key which is possibly again a number, auto incremented by MySQL. That gives you the uniqueness of each record and can be displayed at the beginning of each line for your output i.e.:

#342 Ticket
#343 Ticket
#477 Ticket

etc.

Now if you categorize your tickets and want for each category to show just a simple increment 1,2,3,etc. instead of the primary key which might cause the numbering not to be cunsecutive, you will need to store a number in your table for each record. You can add a new field which will be numeric (integer) and in that you will store the highest number of the ticket per category. All you will have to do is when a ticket is submitted you will query the database to find the max number existing and then increment it by one and store that in the new record.

The additional field approach allows you also to move tickets accordingly because their display order will be based on the new field and not on the primary key. If you want to move a ticket up or down all you will need is a couple of queries and a refresh.

I hope this helps