Page 1 of 1

Check if value exists, if it does.. increment it by 1

Posted: Thu Oct 23, 2008 11:53 pm
by kalp1200
Hi,

I need to implement a serial number system. The serial number's format is AP001A, AP002A, AP003A, I have no problem adding AP and the last A, but I am encountering difficulties in implementing the number format. The system will have to check if the number exists first in the mysql table before adding it, if the number exists in the system, the serial number should be incremented by 1 before adding it again. I think I have to use a for loop, any idea on how to go about it

Re: Check if value exists, if it does.. increment it by 1

Posted: Fri Oct 24, 2008 12:21 am
by requinix
It's ugly, but an easy way to do this is to run a query like

Code: Select all

SELECT COUNT(*) FROM table WHERE identifier LIKE "AP%A"
That'll give you the number of items with identifiers like that. Plus one and you have the next number in the series.
Just don't delete any and don't get any gaps in the sequence and it should work.

Re: Check if value exists, if it does.. increment it by 1

Posted: Fri Oct 24, 2008 4:56 am
by novice4eva
rather than fetching and checking for each value, i think this would be better:

Code: Select all

 
select (MAX(replace(replace(columnName,'A',''),'P',''))+1) as maxValue FROM tableName;
 

Re: Check if value exists, if it does.. increment it by 1

Posted: Fri Oct 24, 2008 6:38 am
by VladSun
kalp1200 wrote:I need to implement a serial number system. The serial number's format is AP001A, AP002A, AP003A
I think, your DB design is wrong. How about this table structure:

Code: Select all

number|prefix|suffix
Where
number: 1, 2, 3 ...
prefix: AP
suffix: A

You may even set number column to be auto_increment and voila - problems disappear :)
Also, this way, you are able to have different formats like BC001T (maybe you should normalize the DB)...
If you don't need them and the AP...A format is constant, then you can omit it from the DB design, and add it on demand in your views.
The same applies to the leading zeros in your number column ;)

Re: Check if value exists, if it does.. increment it by 1

Posted: Thu Oct 30, 2008 3:58 am
by kalp1200
hey, thanks for the suggestions, I will be going with the autonumber thingy and I think I want to implement trigger to combine the 3 columns ( prefix, the autonumber, suffix ), is this the right place to ask about the trigger thingy ( its more of a mysql thing ) or do you all have any other idea, I am not very good at php and mysql, still learning though, the other 2 suggestions looked very complicated to me