{"id":4389,"date":"2024-07-30T20:55:37","date_gmt":"2024-07-30T13:55:37","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4389"},"modified":"2024-08-18T10:09:01","modified_gmt":"2024-08-18T03:09:01","slug":"php-sql-server-insert","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/php-sql-server\/php-sql-server-insert\/","title":{"rendered":"PHP SQL Server: Inserting Data"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to insert a new row into a table from PHP using PDO.<\/p>\n\n\n\n

This tutorial begins where\u00a0the\u00a0connecting to the SQL Server from the PHP program tutorial<\/a>\u00a0left off.<\/p>\n\n\n\n

Inserting data into a table using PDO #<\/a><\/h2>\n\n\n\n

We’ll insert a new row into the Authors<\/code> table in the BookStore<\/code> database.<\/p>\n\n\n\n

Step 1. Define Author class #<\/a><\/h3>\n\n\n\n

Create a new file author.php<\/code> within the project directory and define the Author<\/code> class that represents a row in the Authors<\/code> table:<\/p>\n\n\n

<?php<\/span>\n\nclass<\/span> Author<\/span>\n<\/span>{\n    public<\/span> function<\/span> __construct<\/span>(private $id = 0<\/span>, private $firstName, private $lastName, private $birthDate)<\/span>\n    <\/span>{\n        $this<\/span>->id = $id;\n        $this<\/span>->firstName = $firstName;\n        $this<\/span>->lastName = $lastName;\n        $this<\/span>->birthDate = $birthDate;\n    }\n\n    public<\/span> function<\/span> getId<\/span>()<\/span>\n    <\/span>{\n        return<\/span> $this<\/span>->id;\n    }\n\n    public<\/span> function<\/span> setId<\/span>($id)<\/span>\n    <\/span>{\n        $this<\/span>->id = $id;\n    }\n\n    public<\/span> function<\/span> getFirstName<\/span>()<\/span>\n    <\/span>{\n        return<\/span> $this<\/span>->firstName;\n    }\n\n    public<\/span> function<\/span> setFirstName<\/span>($firstName)<\/span>\n    <\/span>{\n        $this<\/span>->firstName = $firstName;\n    }\n\n    public<\/span> function<\/span> getLastName<\/span>()<\/span>\n    <\/span>{\n        return<\/span> $this<\/span>->lastName;\n    }\n\n    public<\/span> function<\/span> setLastName<\/span>($lastName)<\/span>\n    <\/span>{\n        $this<\/span>->lastName = $lastName;\n    }\n\n    public<\/span> function<\/span> getBirthDate<\/span>()<\/span>\n    <\/span>{\n        return<\/span> $this<\/span>->birthDate;\n    }\n\n    public<\/span> function<\/span> setBirthDate<\/span>($birthDate)<\/span>\n    <\/span>{\n        $this<\/span>->birthDate = $birthDate;\n    }\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The Author<\/code> class has the id<\/code>, firstName<\/code>, lastName<\/code>, and birthDate<\/code> properties with the respective getters and setters.<\/p>\n\n\n\n
Step 2. Defining AuthorDB class #<\/a><\/h3>\n\n\n\n
Create a new file authordb.php<\/code> file and define the AuthorDB<\/code> class:<\/p>\n\n\n
<?php<\/span>\n\nrequire_once<\/span> 'author.php'<\/span>;\n\nclass<\/span> AuthorDB<\/span>\n<\/span>{\n    public<\/span> function<\/span> __construct<\/span>(private \\PDO $conn)<\/span>\n    <\/span>{\n        $this<\/span>->conn = $conn;\n    }    \n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, include the author.php<\/code> file:<\/p>\n\n\n
require_once<\/span> 'author.php'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, define the AuthorDB<\/code> class:<\/p>\n\n\n
class<\/span> AuthorDB<\/span><\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, define a constructor that takes a PDO<\/code> object as a parameter:<\/p>\n\n\n
public<\/span> function<\/span> __construct<\/span>(private \\PDO $conn)<\/span>\n<\/span>{\n    $this<\/span>->conn = $conn;\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Defining insert() method #<\/a><\/h3>\n\n\n\n
Define an insert()<\/code> method that inserts a new row into the Authors<\/code> table:<\/p>\n\n\n
public<\/span> function<\/span> insert<\/span>(Author $author)<\/span>\n<\/span>{\n    $sql = 'INSERT INTO Authors(FirstName, LastName, BirthDate)\n            VALUES (:firstName, :lastName, :birthDate)'<\/span>;\n    $stmt = $this<\/span>->conn->prepare($sql);\n    $stmt->execute([\n        'firstName'<\/span> => $author->getFirstName(),\n        'lastName'<\/span> => $author->getLastName(),\n        'birthDate'<\/span> => $author->getBirthDate()\n    ]);\n    return<\/span> $this<\/span>->conn->lastInsertId();\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, define the insert()<\/code> method that accepts an Author<\/code> object:<\/p>\n\n\n
public<\/span> function<\/span> insert<\/span>(Author $author)<\/span><\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, construct an INSERT<\/code> statement that inserts a new row into the Authors<\/code> table:<\/p>\n\n\n
$sql = 'INSERT INTO Authors(FirstName, LastName, BirthDate)\n        VALUES (:firstName, :lastName, :birthDate)'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The :firstName<\/code>, :lastName<\/code>, and :birthDate<\/code> are placeholders for the parameters of the statement. When executing the statement, you need to provide the actual values for these parameters. <\/p>\n\n\n\n
Notice that using the parameterized statement can help prevent SQL injection attacks.<\/p>\n\n\n\n
Third, create a prepared statement:<\/p>\n\n\n
$stmt = $this<\/span>->conn->prepare($sql);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, execute the prepared statement with first name, last name, and birth date provided by the Author<\/code> object:<\/p>\n\n\n
$stmt->execute([\n    'firstName'<\/span> => $author->getFirstName(),\n    'lastName'<\/span> => $author->getLastName(),\n    'birthDate'<\/span> => $author->getBirthDate()\n]);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, return the inserted author ID by calling the lastInsertId()<\/code> method of the Connection<\/code> object:<\/p>\n\n\n
return<\/span> $this<\/span>->conn->lastInsertId();<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Using the AuthorDB class #<\/a><\/h3>\n\n\n\n
Modify the index.php<\/code> file and use the insert()<\/code> method of the AuthorDB<\/code> object to insert a new row into the Authors<\/code> table:<\/p>\n\n\n
<?php<\/span>\n\nrequire_once<\/span> 'authordb.php'<\/span>;\n$conn = require_once<\/span> 'connect.php'<\/span>;\n\n$authorDB = new<\/span> AuthorDB($conn);\n$author = new<\/span> Author('Anthony'<\/span>, 'Pham'<\/span>, '1980-12-31'<\/span>);\n$authorId = $authorDB->insert($author);\n\necho<\/span> \"Author ID: $authorId\"<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, load the authordb.php<\/code> file to use the AuthorDB<\/code> class.<\/p>\n\n\n
require_once<\/span> 'authordb.php'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
$conn = require_once<\/span> 'connect.php'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, create a new AuthorDB<\/code> object:<\/p>\n\n\n
$authorDB = new<\/span> AuthorDB($conn);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, create a new Author<\/code> object:<\/p>\n\n\n
$author = new<\/span> Author('Anthony'<\/span>, 'Pham'<\/span>, '1980-12-31'<\/span>);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, insert a new row into the Authors<\/code> object by calling the insert()<\/code> method of the AuthorDB<\/code> class:<\/p>\n\n\n
$authorId = $authorDB->insert($author);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The insert()<\/code> method returns the inserted author id.<\/p>\n\n\n\n
Finally, display the inserted author id:<\/p>\n\n\n
echo<\/span> \"Author ID: $authorId\"<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 5. Run the script #<\/a><\/h3>\n\n\n\n
If you see the following message on the browser:<\/p>\n\n\n
Author ID: 1<\/code><\/span><\/pre>\n\n\n
…you have successfully inserted a new author with id 1 into the Authors<\/code> table in SQL Server.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Execute an INSERT<\/code> statement to insert a new row into a table.<\/li>\n\n\n\n
  • Always use parameterized queries to prevent SQL injection attacks.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, connect to the SQL Server<\/a> and assign the returned PDO object to the $conn<\/code> variable:<\/p>\n\n\n