Inserting Records
The SQL INSERT statement is used to insert user data into MySQL database tables. The most common source of user data is values stored in variables, and user input through HTML form controls. The general format of the SQL INSERT statement is shown below:
INSERT INTO tablename (column1, column2) VALUES ($value1,$value2);
The steps for inserting records into a table include: 1. Collecting the user input, 2. Constructing the SQL INSERT statement, 3. Executing or running the INSERT statement against the MySQL database, and 4. Confirming that the data was successfully added to the table. The example below includes an HTML page containing form controls for user input. After the user inputs the data and clicks the form submit button, the form data is posted to a PHP page that contains a script to insert the record.
VisitorSurvey.html
<!DOCTYPE HTML>
<html>
<head>
<title>Visitor Survey Page</title>
</head>
<body>
<form method="post" action="VisitorSurvey.php">
First Name: <input type="text" name="FirstName">
Last Name: <input type="text" name="LastName">
Email Address: <input type="text" name="Email">
Age: <input type="text" name="Age">
Gender: <input type="radio" name="Gender" value="M"> Male
<input type="radio" name="Gender" value="F">Female
Comments:<textarea name="comments"></textarea>
<input type="submit" name="SubmitB" value="Submit Data">
</form>
</body>
</html>
The form fields in the VisitorSurvey.html are named accordingly:
FirstName
LastName
Email
Gender
Comments
The following is the PHP page that contains a script to parse and insert the user form input data. The PHP MySQL functions used include: mysqli_connect(), mysqli_query(), mysqli_affected_rows(), and mysqli_close():
VisitorSurvey.php
<?php
if (isset($_POST['SubmitB']))
{
//Collect the posted form data and assign to scalar variables
$FirstName = $_POST['FirstName'];
$LastName = $_POST['LastName'];
$Email = $_POST['Email'];
$Gender = $_POST['Gender'];
$Comments = $_POST['Comments'];
//Establish a connection to the Database
//Reference the mysqli_connection.php file created above
include('mysqli_connection.php');
// Create the SQL INSERT Statement
$sql = "INSERT INTO Survey (FirstName,LastName,Email,Gender,Comments) VALUES ('$FirstName',’$LastName’, '$Email', '$Gender', '$Comments')";
//Execute SQL Statement
$rs = mysqli_query($conn, $sql);
//Confirm that the data was inserted
if (mysqli_affected_rows($rs) == 1)
{
//data was successfully inserted
}
else
{
//A problem occurred. Display appropriate error message
}
// Close connection to database server
mysqli_close($conn);
}
?>
After the submit button is clicked on VisitorSurvey.html, the form data is posted to VisitorSurvey.php, the page referenced in the <form> tag’s method attribute.
On VisitorSurvey.php, a $_POST superglobal array is created containing the form values. We first check that the VisitorSurvey form has been submitted by confirming that the submit button is set - if (isset($_POST['SubmitB'])). This prevents errors and ensures that the remaining PHP scripts are run only if VisitorSurvey.php is accessed via a valid form submission.
After the form submission is confirmed, the user input stored in the $_POST array are assigned to scalar variables. While this is not a required step, it does greatly simplify coding of the SQL statement later in the script. Next, the include() function is used to call the contents of the mysqli_connect.php which establishes a connection to the MySQL database. After connecting to the MySQL database server, an SQL INSERT statement is issued and assigned to a user defined variable, in this case $sql.
At this point, it is important to note that the SQL INSERT statement should be surrounded by double quotes. Additionally, the values can be literals, or variables. In this example the values are the variables that contain the user input data. If the value is a literal or variable that contains string data, it must be surrounded by single quotes. Variables containing numeric data or data being inserted into a numeric database table field are not surrounded by quotes.
Next, the mysqli_query() function executes the SQL statement creating a recordset (set of returned database records). The recordset is assigned to the user defined variable $rs, another PHP reference variable. Note that the mysqli_query() function requires two parameters - $conn (a reference to the current database connection) and $sql (a reference to the current SQL statement). Finally, the mysqli_close() function is called to close the current database connection.
With this type of application, it is generally a good idea peform data validation prior to inserting the data into the database table. This should be done prior to establishing the database connection using the techniques discussed in the Basic Form Processing section.
The last step is to confirm whether the data was successfully inserted into the database. This can be checked using the mysqli_affected_rows() function. The mysqli_affected_rows() function requires one parameter , the reference to the current recordset - $rs. The function returns the number of rows affected by the last INSERT statement. In this script one record is being inserted. If the value of mysqli_affected_rows() is 1, we display a user friendly confirmation message. Otherwise, an appropriate error message is displayed.
In the event of a coding error, which is common for even the most experienced programmers, the data is not inserted into the database table and PHP will by default display a cryptic warning or error message.These messages can be helpful as you debug in development mode. However, prior to making the page “live”, it is a good idea to supress these cryptic messages, add code to manually check for errors, and generate more friendly output for the end user. This can be done by using the PHP error suppression operator - @.
Suppose in the previous code that the mysqli_query() function contained the parameter $sqlString instead of $sql. With the error suppressed, we can add code to generate a more user-friendly response. This technique is shown below:
VisitorSurvey.php
<?php
if (isset($_POST['SubmitB']))
{
//Collect the posted form data and assign to scalar variables
$FirstName = $_POST['FirstName'];
$LastName = $_POST['LastName'];
$Email = $_POST['Email'];
$Gender = $_POST['Gender'];
$Comments = $_POST['Comments'];
//Establish a connection to the Database
//Reference the mysqli_connection.php file created above
include('mysqli_connection.php');
// Create the SQL INSERT Statement
$sql = "INSERT INTO Survey (FirstName,LastName,Email,Gender,Comments) VALUES ('$FirstName',’$LastName’, '$Email', '$Gender', '$Comments')";
//Execute SQL Statement
$rs = @mysqli_query($conn, $sqlString);
//Confirm that the data was inserted
if (mysqli_affected_rows($rs) == 1)
{
//data was successfully inserted
}
else
{
//A problem occurred. Display appropriate error message
}
// Close connection to database server
mysqli_close($conn);
}
?>
Following the mysqli_query() function, the mysqli_affected_rows() function is used to verify that the record was inserted successfully. The mysqli_affected_rows() function is used to return the number of affected rows by the last SQL INSERT statement. It takes the connection id, in this case $conn, as a parameter. The function returns a value of -1 if an error occurs. If the function returns a value of -1, an error message is displayed.