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 an INSERT 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 the stocks table that we created in the previous tutorial for the demonstration purpose. Let’s create a new class named PostgreSQLPHPInsert in the app folder and the index.php file in the project folder.
Inserting a single row into a table example
The following insertStock() method inserts a new row into the stocks table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /** * 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, we constructed an INSERT statement string. Because we wanted to pass values to the statement, we used two named placed holders: :symbol and :company.
Next, we passed the INSERT statement to the prepare() method of the PDO object to prepare for execution.
Then, we called the bindValue method to pass the values to the statement.
After that, we call the execute() method to execute the INSERT statement.
Finally, we called the lastInsertId() method of the PDO object to get ID of the last inserted row. Because the PDO_PGSQL extension requires us to specify the name of the sequence object as the parameter, we passed the stocks_id_seq string to the function to get the generated ID.
Insert multiple rows into a table example
The following insertStockList() method inserts multiple rows into the stocks table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | /** * 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 ($stocks as $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 the execute() method multiple times to insert multiple rows into the stocks table. It returns a list of inserted ID.
Place the following code in the index.php file to test the insertStock() and insertStockList() methods.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | <?php require '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 ($list as $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:
1 2 3 4 | The stock has been inserted with the id 1 The stock has been inserted with the id 2 The stock has been inserted with the id 3 The stock has been inserted with the id 4 |
In this tutorial, we have shown you how to insert a single row or multiple rows into a PostgreSQL table using PHP PDO.