Summary: in this tutorial, you will learn how to insert a new row into a table from PHP using PDO.
This tutorial begins where the connecting to the SQL Server from the PHP program tutorial left off.
Inserting data into a table using PDO
We’ll insert a new row into the Authors
table in the BookStore
database.
Step 1. Define Author class
Create a new file author.php
within the project directory and define the Author
class that represents a row in the Authors
table:
<?php
class Author
{
public function __construct(private $id = 0, private $firstName, private $lastName, private $birthDate)
{
$this->id = $id;
$this->firstName = $firstName;
$this->lastName = $lastName;
$this->birthDate = $birthDate;
}
public function getId()
{
return $this->id;
}
public function setId($id)
{
$this->id = $id;
}
public function getFirstName()
{
return $this->firstName;
}
public function setFirstName($firstName)
{
$this->firstName = $firstName;
}
public function getLastName()
{
return $this->lastName;
}
public function setLastName($lastName)
{
$this->lastName = $lastName;
}
public function getBirthDate()
{
return $this->birthDate;
}
public function setBirthDate($birthDate)
{
$this->birthDate = $birthDate;
}
}
Code language: PHP (php)
The Author
class has the id
, firstName
, lastName
, and birthDate
properties with the respective getters and setters.
Step 2. Defining AuthorDB class
Create a new file authordb.php
file and define the AuthorDB
class:
<?php
require_once 'author.php';
class AuthorDB
{
public function __construct(private \PDO $conn)
{
$this->conn = $conn;
}
}
Code language: PHP (php)
How it works.
First, include the author.php
file:
require_once 'author.php';
Code language: PHP (php)
Second, define the AuthorDB
class:
class AuthorDB
Code language: PHP (php)
Third, define a constructor that takes a PDO
object as a parameter:
public function __construct(private \PDO $conn)
{
$this->conn = $conn;
}
Code language: PHP (php)
Step 3. Defining insert() method
Define an insert()
method that inserts a new row into the Authors
table:
public function insert(Author $author)
{
$sql = 'INSERT INTO Authors(FirstName, LastName, BirthDate)
VALUES (:firstName, :lastName, :birthDate)';
$stmt = $this->conn->prepare($sql);
$stmt->execute([
'firstName' => $author->getFirstName(),
'lastName' => $author->getLastName(),
'birthDate' => $author->getBirthDate()
]);
return $this->conn->lastInsertId();
}
Code language: PHP (php)
How it works.
First, define the insert()
method that accepts an Author
object:
public function insert(Author $author)
Code language: PHP (php)
Second, construct an INSERT
statement that inserts a new row into the Authors
table:
$sql = 'INSERT INTO Authors(FirstName, LastName, BirthDate)
VALUES (:firstName, :lastName, :birthDate)';
Code language: PHP (php)
The :firstName
, :lastName
, and :birthDate
are placeholders for the parameters of the statement. When executing the statement, you need to provide the actual values for these parameters.
Notice that using the parameterized statement can help prevent SQL injection attacks.
Third, create a prepared statement:
$stmt = $this->conn->prepare($sql);
Code language: PHP (php)
Fourth, execute the prepared statement with first name, last name, and birth date provided by the Author
object:
$stmt->execute([
'firstName' => $author->getFirstName(),
'lastName' => $author->getLastName(),
'birthDate' => $author->getBirthDate()
]);
Code language: PHP (php)
Fifth, return the inserted author ID by calling the lastInsertId()
method of the Connection
object:
return $this->conn->lastInsertId();
Code language: PHP (php)
Step 4. Using the AuthorDB class
Modify the index.php
file and use the insert()
method of the AuthorDB
object to insert a new row into the Authors
table:
<?php
require_once 'authordb.php';
$conn = require_once 'connect.php';
$authorDB = new AuthorDB($conn);
$author = new Author('Anthony', 'Pham', '1980-12-31');
$authorId = $authorDB->insert($author);
echo "Author ID: $authorId";
Code language: PHP (php)
How it works.
First, load the authordb.php
file to use the AuthorDB
class.
require_once 'authordb.php';
Code language: PHP (php)
Second, connect to the SQL Server and assign the returned PDO object to the $conn
variable:
$conn = require_once 'connect.php';
Code language: PHP (php)
Third, create a new AuthorDB
object:
$authorDB = new AuthorDB($conn);
Code language: PHP (php)
Fourth, create a new Author
object:
$author = new Author('Anthony', 'Pham', '1980-12-31');
Code language: PHP (php)
Fifth, insert a new row into the Authors
object by calling the insert()
method of the AuthorDB
class:
$authorId = $authorDB->insert($author);
Code language: PHP (php)
The insert()
method returns the inserted author id.
Finally, display the inserted author id:
echo "Author ID: $authorId";
Code language: PHP (php)
Step 5. Run the script
If you see the following message on the browser:
Author ID: 1
…you have successfully inserted a new author with id 1 into the Authors
table in SQL Server.
Summary
- Execute an
INSERT
statement to insert a new row into a table. - Always use parameterized queries to prevent SQL injection attacks.