PostgreSQL PHP: Insert Data Into Tables
Summary: in this tutorial, you will learn how to use PHP PDO API to insert data into a PostgreSQL database table.
Steps for inserting data into a PostgreSQL table using PDO
To insert data into a database table, you use the following steps:
- First, connect to the PostgreSQL database server by creating a new instance of the PDO class.
- Next, construct anINSERT statement. If you want to pass parameters to the
INSERTstatement, you use the named placeholders such as:param - Then, prepare the
INSERTstatement by calling theprepare()method of the PDO object. Theprepare()method returns aPDOStatementobject. - After that, pass the values to the statement by calling the
bindValue()method of thePDOStatementobject. - Finally, call the
execute()method of the PDOStatement object to execute theINSERTstatement.
We will use thestocks table that we created in the previous tutorial for demonstration purposes.
Let’s create a new class namedPostgreSQLPHPInsert in theapp folder and theindex.php file in the project folder.
Inserting a single row into a table example
The followinginsertStock() method inserts a new row into thestocks table.
/** * insert a new row into the stocks table * @param type $symbol * @param type $company * @return the id of the inserted row */ public function insertStock($symbol, $company){ // prepare statement for insert $sql= 'INSERT INTO stocks(symbol,company) VALUES(:symbol,:company)'; $stmt= $this->pdo->prepare($sql); // pass values to the statement $stmt->bindValue(':symbol', $symbol); $stmt->bindValue(':company', $company); // execute the insert statement $stmt->execute(); // return generated id return $this->pdo->lastInsertId('stocks_id_seq'); }First, construct anINSERT statement that uses two named placed holders::symbol and:company for binding values later.
Next, prepare the insert statement for execution by calling theprepare() method of the PDO object.
Then, passing the values to the statement by calling thebindValue() method.
After that, execute theINSERT statement by calling theexecute() method.
Finally, get the ID of the last inserted row by calling thelastInsertId() method of the PDO object
thePDO_PGSQL extension requires us to specify the name of the sequence object as the parameter, we passed thestocks_id_seq string to the function to get the generated ID.
Insert multiple rows into a table example
The followinginsertStockList() method inserts multiple rows into thestocks table.
/** * Insert multiple stocks into the stocks table *@param array $stocks *@return a list of inserted ID */ public function insertStockList($stocks) { $sql= 'INSERT INTO stocks(symbol,company)VALUES(:symbol,:company)'; $stmt= $this->pdo->prepare($sql); $idList= []; foreach ($stocksas $stock) { $stmt->bindValue(':symbol', $stock['symbol']); $stmt->bindValue(':company', $stock['company']); $stmt->execute(); $idList[]= $this->pdo->lastInsertId('stocks_id_seq'); } return $idList; }The method accepts an array of stocks and calls theexecute() method multiple times to insert multiple rows into thestocks table. It returns a list of inserted IDs.
Place the following code in the index.php file to test theinsertStock() andinsertStockList() methods.
<?phprequire 'vendor/autoload.php';use PostgreSQLTutorial\Connection as Connection;use PostgreSQLTutorial\PostgreSQLPHPInsert as PostgreSQLPHPInsert;try { // connect to the PostgreSQL database $pdo= Connection::get()->connect(); // $insertDemo= new PostgreSQLPHPInsert($pdo); // insert a stock into the stocks table $id= $insertDemo->insertStock('MSFT', 'Microsoft Corporation'); echo 'The stock has been inserted with the id ' . $id. '<br>'; // insert a list of stocks into the stocks table $list= $insertDemo->insertStockList([ ['symbol' => 'GOOG', 'company' => 'Google Inc.'], ['symbol' => 'YHOO', 'company' => 'Yahoo! Inc.'], ['symbol' => 'FB', 'company' => 'Facebook, Inc.'], ]); foreach ($listas $id) { echo 'The stock has been inserted with the id ' . $id. '<br>'; }}catch (\PDOException $e) { echo $e->getMessage();}Launch the index.php in the web browser, we got the following output:
The stock has been inserted with the id 1The stock has been inserted with the id 2The stock has been inserted with the id 3The stock has been inserted with the id 4In this tutorial, you have learned how to insert a single row or multiple rows into a table in the PostgreSQL database using PHP PDO.
Last updated on