Provides various official and user-released code examples. For code reference, you are welcome to exchange and learn. After pgsql inserts data into the database in tp, the auto-incrementing primary key cannot be returned normally, consider using the following method for simple processing.
For pgsql, after the add () method is used to insert data, the returned result is always 1 instead of the auto-increment ID of the newly inserted data.
Bytes -----------------------------------------------------------------------------------------------
Cause
In ThinkPHP 3.2.3, all databases use the PDO method to connect to the database. The following method returns the auto-incremental ID of the inserted data in PDO:
string PDO::lastInsertId ([ string $name = NULL ] )The parameter name indicates the name of the sequence object whose ID should be returned, which is optional.
However, the specific behavior of this function is implemented based on the specific underlying driver. This method may not return a meaningful or consistent result between different PDO drivers, because the underlying database may not support the concept of auto-incrementing fields or sequences.
Bytes -----------------------------------------------------------------------------------------------
For pgsql: PDO_PGSQL () requires that the name of the sequence object be specified for the name parameter.
This is the cause:
In the DB Driver. class. php of ThinkPHP, the method for getting auto-increment IDS is to use the lastInsertId () method without parameters. When no parameter is added, pgsql returns 1.
Bytes -----------------------------------------------------------------------------------------------
Bytes -----------------------------------------------------------------------------------------------
Solution
For pgsql, if you want to return the correct auto-increment ID, you must add the name of the corresponding sequence object (sequence name) when calling the lastInsertId () method ).
Therefore, first, we need to re-write the 'getlastinsid () 'method of the DB driver in the corresponding driver of pgsql and input the parameter to it:
# Location: ThinkPHP/Library/Think/Db/Driver/Pgsql. class. php
/**
* Used to obtain the last inserted ID
* @ Access public
* @ Return integer
*/
Public function getLastInsID ($ sequenceName ){
Return $ this-> _ linkID-> lastInsertId ($ sequenceName );
}So what should this parameter be?
Generally, if the auto-increment primary key is set in the pgsql data table, the auto-increment primary key is named as follows:
tableNmae_columnName_seqWhere:
* TableName indicates the name of the current table (if it is not a public namespace, you need to add a namespace );
* ColumnName indicates the name of the column with the primary key set;
* Seq is set by default and does not need to be modified.
Therefore, the parameter is not a definite string: The table name is uncertain, and the primary key column name is uncertain:
* When we use the M () or D () method for table names, we usually pass in the table name. You can consider concatenating parameters in the Model class;
* For the primary key column name, if we make a Convention: in the data table, the primary key column name is 'id', then we can solve this problem. If you cannot comply with this convention, you can only consider adding a method to the Model class to input the corresponding parameters.
To implement the above method, find and modify the 'getlastinsid () 'method in the Model class:
# Location: ThinkPHP/Library/Think/Model. class. php
/**
* Returns the last inserted ID.
* @ Access public
* @ Return string
*/
Public function getLastInsID (){
$ Col = null;
If (strtolower (C ('db _ type') = 'pgsql ')
$ Col = strtolower ($ this-> trueTableName). '_ id_seq ';
Return $ this-> db-> getLastInsID ($ col );
}Here, you can use '$ this-> truetablename' to obtain the table name and obtain the real table name containing the prefix or even database name to avoid errors.
After this modification, it will basically be normal. However, you will soon find that sometimes an error occurs, prompting 'relation xx does not exist '. This error occurs when accessing a table without a primary key. When the pgsql-driven 'lastinsertid () 'method is used to obtain a table without a primary key, the sequence object does not exist and an error is returned.
After knowing the cause, you can use the 'try... catch 'statement to overwrite the 'lastinsertid ()' method of pgsql:
# Location: ThinkPHP/Library/Think/Db/Driver/Pgsql. class. php
/**
* Used to obtain the last inserted ID
* @ Access public
* @ Return integer
*/
Public function getLastInsID ($ sequenceName ){
Try {
Return $ this-> _ linkID-> lastInsertId ($ sequenceName );
} Catch (\ PDOException $ e ){
Return $ this-> lastInsID;
}
}MD documentation: Workshop
AD: truly free, domain name + VM + enterprise mailbox = 0 RMB
