[liphp] (no subject)

[Sean McCorkle]

> Hi,

   Hi Veena

> I have a table proposal in postgresql databse which has a unique primary key
> proposal_id
> This is a varchar field but is kind of autoincrement field(indirectly).
> every time user submits a proposal, the proposal_id needs to be generated. I
> have problem in this part. My propoalid should be like:
> x6a1,x6a2.....x6a20....etc

    Do I understand you correctly: the proposal ID is syntatically

            x6a<n>

    where <n> are ever-increasing integers 1,2, ...  and the prefix is _always_
    x6a?  Or is x6a going to sometimes be x6b, x6c, etc?

> I tried with "SELECT max(proposalid) from proposal" but it works only till
> x6a9 and after that it considers x6a10 as x6a1 for comparing the max value. 
> I don't know how to take the last record from the table in order to generate
> the next proposalid. I have an option to declare a global variable called
> $lastpid and as and when the proposal record gets inserted into the proposal
> table, store that value into $lastpid but for some reason this is not
> working. the global variable is not retainign the value. 

    I personally think you would be better off by storing only a number in
    the idea field - just assume the x6a prefix and print it out only
    where required.   that way

     select max(proposalid)+1 from proposal

    would work and would be more clear.  If you expect to include OTHER
    prefixes like x6b, I would add another column and make a compound 
    key.  That way, you could easily do

       select max(proposalid)+1 from proposal where beamline='x6a'

    But if you want to stick with what you have
   
         select 1+max( to_number( substring( proposalid, 4), '999999'))
              from proposal

    should isolate and increment the max of digital tail of the id.
    I'll leave it as an exercise for you to prepend the x6a to the 
    beginning -in the same SQL statement (don't cheat and do it in php :)

=sean
-------------------------------------------------------------------------------
Sean R. McCorkle           mccorkle@bnl.gov   | Haven't you heard, that aside
Genome Groupph  | from the details, there's
Biology Departmentfax | nothing further to be found?
Brookhaven National Laboratory                |    --Master Dogen's 300 Koan
Upton, New York  11973                        |      Shobogenzo  (commentary)