PHP and SQL Server - Inserting Data

In order to add a record to a database table, the SQL 'insert' statement needs to be used. This takes the following format.

INSERT INTO table_name (column_name)
VALUES ('value')

Again, as with the 'select' statement, multiple columns and values can be separated by commas.

INSERT INTO table_name (column_name1, column_name2, column_name3)
VALUES ('value1', 'value2', 'value3')

The following example uses the same 'person' table that was used when discussing selecting data.

<?php

   // Connect to the database.
   require_once('database-connect.php');
   
   // Query parameters.
   $firstName = "Fiona";
   $lastName = "Jones";
   $title = "Miss";
   $dob = "1985-05-19";
 
   try
   {
      
      // Prepare the query.
      $results = $connect->prepare("INSERT INTO person (firstname, lastname, title, dob)
                                    VALUES (?, ?, ?, ?)");
                                    
      // Bind the parameters.
      $results->bindParam(1, $firstName);
      $results->bindParam(2, $lastName);
      $results->bindParam(3, $title);
      $results->bindParam(4, $dob);
      
      // Execute the query.
      $results->execute();
      
   } catch(Exception $e) {
      
      // If query fails, display an error and exit.
      echo "Error adding person information.";
      exit;
      
   }

   // Display a message saying person added successfully.
   echo "Person added successfully.";

?>

Note that the 'id' column from the 'person' table is not mentioned in the above example. This example assumes that the 'id' column is set up to automatically increment, therefore it will be populated with no intervention from PHP. The contents of the 'person' table now looks as follows.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
2 George Jones Mr 1997-12-15
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20
5 Fiona Jones Miss 1985-05-19