[liphp] (no subject)

[Frank Imburgio]

Perhaps a simple workaround would be:

CREATE TABLE keyvalues (
    value int 10 NOT NULL,
    tablename varchar(40) NOT NULL
)
INSERT INTO keyvalues (value,tablename) VALUES (0,proposal) //insert a 
row for every table that needs a generated key

Now, to get the next available key:

BEGIN TRANSACTION // if your SQL flavor has this, it's a good time to 
use it....
UPDATE keyvalues SET value=value+1 WHERE tablename='proposal' //bump the 
key up one, or change it any way you like, really
@newkey=SELECT value FROM keyvalues WHERE tablename='proposal' //this 
will be the next key
END TRANSACTION

There are other answers to this 'chicken before the egg' problem, but 
this one works unless you have very heavy transaction volume.

Hope this helps,

Frank Imburgio

Venkatagiriyappa, Veena wrote:

>Hi,
>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
>
>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. 
>
>The function generate goes like this:
>
>function generate_proposalid()
>{
>    global $lastpid;
>    $query = "select count(proposalid) from proposal";
>    $result = pg_query($query);
>    $row=pg_numrows($result);
>    if($row>0)
>    {
>        $latestpid=$lastpid;
>        /* Use the substring function to extract value after x6a (3 in case
>of x6a3)
>           from the proposalid */
>        $latestpid = substr($latestpid,3);
>        /* Increment the integer part */
>        $latestpid++;
>        /* Generate new proposalid by prefixing x6a to incremented value */
>        $proposalid = "x6a" . $latestpid;
>    }
>    else
>    {
>        /* In case of first proposalid */
>        $proposalid = 1;
>        $proposalid = "x6a" . $proposalid;
>    }
>    return $proposalid;
>}
>
>Please tell me if there is any alternative. Value for $lastpid is being
>stored in the called program after the value is returned from
>generate_proposal function. Am I going wrong somewhere? I have been trying
>with this problem since many days. Some times some logic works but fails in
>specific condition.
>
>Any help will be appreciated.
>
>    
>_______________________________________________
>Liphp mailing list
>Liphp@liphp.org
>https://www.liphp.org/mailman/listinfo/liphp
>  
>