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;
    }
}

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;
    }    
}

How it works.

First, include the author.php file:

require_once 'author.php';

Second, define the AuthorDB class:

class AuthorDB

Third, define a constructor that takes a PDO object as a parameter:

public function __construct(private \PDO $conn)
{
    $this->conn = $conn;
}

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();
}

How it works.

First, define the insert() method that accepts an Author object:

public function insert(Author $author)

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)';

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);

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()
]);

Fifth, return the inserted author ID by calling the lastInsertId() method of the Connection object:

return $this->conn->lastInsertId();

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";

How it works.

First, load the authordb.php file to use the AuthorDB class.

require_once 'authordb.php';

Second, connect to the SQL Server and assign the returned PDO object to the $conn variable:

$conn = require_once 'connect.php';

Third, create a new AuthorDB object:

$authorDB = new AuthorDB($conn);

Fourth, create a new Author object:

$author = new Author('Anthony', 'Pham', '1980-12-31');

Fifth, insert a new row into the Authors object by calling the insert() method of the AuthorDB class:

$authorId = $authorDB->insert($author);

The insert() method returns the inserted author id.

Finally, display the inserted author id:

echo "Author ID: $authorId";

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.

Was this tutorial helpful?