PHP Insert Data Into MySQL

Insert Data Into MySQL Using MYSQLi Object-Oriented

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 Object-Oriented)

    <?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $db="lipsphpstudent";
    // Create connection
    $conn = new mysqli($servername, $username, $password,$db);
    // Check connection
    if ($conn->connect_error) { die("Connection failed: " . $conn->connect_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
    <?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());
    }
    ?>
    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