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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kalp1200
Forum Newbie
Posts: 19
Joined: Tue Aug 26, 2008 8:57 pm

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

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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.
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

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

Post 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;
 
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
kalp1200
Forum Newbie
Posts: 19
Joined: Tue Aug 26, 2008 8:57 pm

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

Post 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
Post Reply