MySQL INSERT INTO

Understanding the INSERT INTO statement is essential for anyone working with MySQL databases. In this tutorial, you will learn to use INSERT INTO in various scenarios with examples.

What is MySQL INSERT INTO?

The MySQL "INSERT INTO" SQL statement adds new records to a specified table. Here's its basic syntax:

SQL Syntax:

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

In this syntax, table_name refers to the table to insert records into, while column1, column2, ... specify the columns. The VALUES clause provides the data for these columns.

Insert Single Record

Adding a single row to a table is simple. For example, consider a table named employees with the columns FirstName, LastName, and Age.

SQL Example:

INSERT INTO employees (FirstName, LastName, Age)
VALUES ('Pavitra', 'Prabhakar', 30);

Insert Multiple Records

Batch insertion allows you to add multiple rows in a single SQL query.

SQL Example:

INSERT INTO employees (firstName, lastName, age)
VALUES 
  ('Pavitra', 'Prabhakar', 30),
  ('Aditi', 'Jane', 25),
  ('Esha', 'Smith', 40);

Inserting Data via PHP

Example:

<?php
// Database connection establishment
$con=mysqli_connect("hostname","username","password","db_name");

// Check connection
if (mysqli_connect_errno($con)) {
    echo "MySQL database connection failed: " . mysqli_connect_error();
}

// Insert
mysqli_query($con,"INSERT INTO contact (name, email, message)
    VALUES ('Pavitra', '[email protected]', 'Test message')")
?>

Collecting Data with HTML Forms

Collect user data seamlessly with HTML forms, as demonstrated below:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Contact Form</title>
</head>
<body>

  <form action="submit.php" method="post">
    <fieldset>
      <div>
        <label for="name">First Name:</label>
        <input type="text" name="name" id="name" required>
      </div>
      <div>
        <label for="email">Email:</label>
        <input type="email" name="email" id="email" required>
      </div>
      <div>
        <label for="message">Message:</label>
        <textarea name="message" id="message" cols="40" rows="5" required></textarea>
      </div>
      <div>
        <input type="submit" name="submitBtn" id="submitBtn" value="Submit">
      </div>
    </fieldset>
  </form>

</body>
</html>

In the above example, form data will be sent to "submit.php" when a user clicks the submit button. The "Submit.php" file connects to a database, and the PHP $ _POST variable receives the value from the form. Then, the mysqli_query() function executes the INSERT INTO statement, and a new record will be added to the "contact" table.

Here's how it's done:

PHP Code for the HTML Form

<?php
// Establish database connection
$con = mysqli_connect("hostname", "username", "password", "db_name");

// Validate connection
if (mysqli_connect_errno($con)) {
    echo "MySQL database connection failed: " . mysqli_connect_error();
}

// Check if submit button pressed
if (isset($_POST["submitBtn"])) {
    $name = $_POST["name"];
    $email = $_POST["email"];
    $message = $_POST["message"];

    // Execute INSERT query
    if (mysqli_query($con, "INSERT INTO contact (name, email, message) VALUES ('$name', '$email', '$message')")) {
        echo "Record inserted successfully";
    }
}
?>

Conclusion

In this tutorial, you learned the MySQL INSERT INTO statement basics. You learned how to use the statement to add single and multiple records to a MySQL table. You also learned how to use the statement with HTML forms and PHP scripts. With this knowledge, you can use INSERT INTO like a pro.

Found This Page Useful? Share It!
Get the Latest Tutorials and Updates
Join us on Telegram
Keep W3schools Growing with Your Support!
❤️ Support W3schools