PHP MySQL INSERT Query

Inserting Data into a MySQL Database Table

The INSERT INTO statement is used to insert new rows into a table. After the VALUES keyword, a comma-separated list of column names follows.

Its basic syntax is as follows:

INSERT INTO table_name VALUES (value1, value2,....)

You can also specify the columns for which you want to insert data:

INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

Example

$link = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Attempt insert query execution
$sql = "INSERT INTO book (userName, phone, email) 
VALUES ('Krishna', '944944882', 'krishna@mail.com')";

if(mysqli_query($link, $sql)){
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

// Close connection
mysqli_close($link);

Inserting Multiple Rows into a Table

$sql = "INSERT INTO book (id,userName, phone, email) 
VALUES ('','Raghavendra', '9449423245', 'raghavendra@mail.com'),
('','Gopal', '944944958', 'gopal@mail.com'),
('','Vijay', '944944944', 'vijay@mail.com')";

Insert Data into a Database from an HTML Form

In the previous section, we have learned how to insert data into database from a PHP script. Now, we'll see how we can insert data into database obtained from an HTML form.

Step 1: Creating the HTML Form Download

Here's a simple HTML form that has three text <input> fields and a submit button.

Example

<h2>Register User</h2>
<form action="insertQueryHtml.php" method="post">
<p><label>User Name:</label>
<input id="userName" type="text" name="userName" size="30" maxlength="30" 
value="<?php if (isset($_POST['userName'])) echo $_POST['userName']; ?>"></p>

<p><label class="label" >Phone Number:</label>
<input id="phone" type="text" name="phone" size="30" maxlength="60" 
value="<?php if (isset($_POST['phone'])) echo $_POST['phone']; ?>" ></p>

<p><label class="label" >Email Address:</label>
<input id="email" type="text" name="email" size="30" maxlength="60" 
value="<?php if (isset($_POST['email'])) echo $_POST['email']; ?>" ></p>
<p><input id="submit" type="submit" name="submit" value="Add User"></p>
</form>

Step 2: Retrieving and Inserting the Form Data Download

When a user clicks the submit button of the add record HTML form, in the example above, the form data is sent to 'insert.php' file. The 'insertQuery.php' file connects to the MySQL database server, retrieves forms fields using the PHP $_POST variables and finally execute the insert query to add the records. Here is the complete code of our 'insertQuery.php' file:

 
require ('mysqli_connect.php'); // Connect to the db.
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$userName = mysqli_real_escape_string($dbcon, trim($_POST['userName']));
$phone = mysqli_real_escape_string($dbcon, trim($_POST['phone']));
$email = mysqli_real_escape_string($dbcon, trim($_POST['email']));

$q = "INSERT INTO book (id, userName, phone,  email)
VALUES (' ', '$userName', '$phone', '$email')";
$result = @mysqli_query ($dbcon, $q); // Run the query.
if ($result) {
//echo "Records added successfully.";
echo("<script>location.href = 'register-thanks.php';</script>");
exit();
} else { 
echo "ERROR: Could not able to execute $q. " .mysqli_error($dbcon);
} 
}

Finding last insert ID Download View

$q = "INSERT INTO book (id, userName, phone,  email)
VALUES (' ', '$userName', '$phone', '$email')";
$result = @mysqli_query ($dbcon, $q); // Run the query.
if ($result) {
    $last_id = mysqli_insert_id($dbcon);
    echo "Records inserted successfully. Last inserted ID is: " . $last_id;
exit();
} else { 
echo "ERROR: Could not able to execute $q. " .mysqli_error($dbcon);
}