PHP SQL Server: Inserting Data

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 AuthorDBCode 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.