PHP Insert Data Into MySQL

Insert Data Into MySQL Using MYSQLi

After a database and a table have been created, we can start adding data in them.

Here are some syntax rules to follow:

The SQL query must be quoted in PHP

String values inside the SQL query must be quoted

Numeric values must not be quoted

The word NULL must not be quoted

The INSERT INTO statement is used to add new records to a MySQL table:

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

OR

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

In the previous chapter we created an empty table named "MyGuests" with five columns: "id", "firstname", "lastname", "email" and "reg_date". Now, let us fill the table with data.

Note: If a column is AUTO_INCREMENT (like the "id" column) or TIMESTAMP (like the "reg_date" column), it is no need to be specified in the SQL query; MySQL will automatically add the value.

Insert data using MySQL

The following examples add a new record to the "lipsphpstudent" database-"fa_MyGuests" table:

Example (MySQLi Procedural)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$db="lipsphpstudent";
// Create connection
$conn = mysqli_connect($servername, $username, $password,$db);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_error());
}
// Create database
$sql = "CREATE DATABASE IF NOT EXISTS lipsphpstudent";
if (mysqli_query($conn,$sql)) {
echo "<script>alert('Database created successfully');";</script>
} else {
echo "<script>alert('Error creating database:' . mysqli_error());</script>;
}
$sql1 = "CREATE TABLE fa_MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
if (mysqli_query($coon,$sql1)) {
$sql2 = "INSERT INTO fa_MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if (mysqli_query($conn,$sql2)) {
echo "<script> alert('New record created successfully')</script>";
} else {
echo "<script> alert('New record not Created.Please Try again.')</script>";
}
} else {
$sql2 = "INSERT INTO fa_MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if (mysqli_query($conn,$sql2)) {
echo "<script> alert('New record created successfully')</script>";
} else {
echo "&l;script> alert('New record not Created.Please Try again.')</script>";
}
}
?>

Output

ID	First Name	Last Name	Email
1	John	Doe	john@example.com

Show Code

mysql_connection.php

mysqli_pop_insert_data_live_example.php

<?php
include("mysqli_pop_connection.php");
if(isset($_POST['submit']))
{
$fname=$_POST['fname'];
$lname=$_POST['lname'];
$email=$_POST['email'];
//Create table
$sql1 = "CREATE TABLE fa_MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP )";
if (mysqli_query($conn,$sql1)) {
$sql2 = "INSERT INTO fa_MyGuests (firstname, lastname, email)
VALUES ('$fname', '$lname', '$email')";
if (mysqli_query($conn,$sql2)) {
echo "<script> alert('New record created successfully')</script>";
$id = mysql_insert_id($conn);
echo "<script> windows.location.href='mysqli_pop_insert_viewdata.php?last_user=$id';</script>";
} else {
echo "<script> alert('New record not Created.Please Try again.')</script>";
echo "<script> window.location.href='mysqli_pop_insert_viewdata.php';</script>";
}
} else {
$sql2 = "INSERT INTO fa_MyGuests (firstname, lastname, email)
VALUES ('$fname', '$lname', '$email')";
if (mysqli_query($conn,$sql2)) {
echo "<script> alert('New record created successfully')</script>";
$id = mysql_insert_id($conn);
echo "<script> window.location.href='mysqli_pop_insert_viewdata.php?last_user=$id';</script>";
} else {
echo "&l;script> alert('New record not Created.Please Try again.')</script>";
echo "<script> window.location.href='mysqli_pop_insert_viewdata.php';</script>";
}
}
}else{
?>
<?php include('header1.php'); ?>
<?php include("mysql_sidemenu.php"); ?>
<div class="container">
<div class="row">

<div class="col-sm-7">
<center><h3>Insert Recorde in Database </h3></center>
<form method="post" action="mysqli_pop_insert_data_live_example.php">
First Name:<input class="form-control" type="text" name="fname" required="">
Last Name:<input class="form-control" type="text" name="lname" required="">
Email ID:<input class="form-control" type="email" name="email" required="">
$lt;input type="submit" name="submit" class="btn btn-primary" >
</form >
<br><br><br><br><br>
</div>
</div>
</div>
<?php include('footer.php');>
<?php } ?>

mysqli_pop_insert_viewdata.php

<?php
include("mysqli_pop_connection.php");
$id=$_GET['last_user'];
$sql="select * from fa_MyGuests where id=$id";
$query=mysqli_query($conn,$sql);
if($query)
{
?>
<?php include('header1.php'); ?>
<?php include("mysql_sidemenu.php"); ?>
<div class="container">
<div class="row">
<div class="col-sm-12">
<div class="table-responsive">

<center><h3>Recorde inserted Successfully.</h3></center>
<table class="table table-bordered">
<thead>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email ID</th>
<th>Reg_Date</th>
</tr>
</thead>
<tbody>
<?php
while($row=mysqli_fetch_array($query))
{
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['firstname'];?></td>
<td><?php echo $row['lastname']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo $row['reg_date']; ?></td>
</tr>
<?php
}
}?>
</tbody>
</table>
</div>
</div>
</div>
</div>
<?php include('footer.php');?>
</div>
</div>

Previous Next