Master the fine art of inserting data using PHP in MySQL with our comprehensive, step-by-step guide loaded with expert insights.
In the world of web development, the ability to store and retrieve data efficiently is paramount. MySQL, a popular open-source relational database management system, is often used to accomplish this. In this tutorial, we’ll walk you through the process of inserting data into a MySQL database using PHP, a widely-used server-side scripting language.
Prerequisites: Before you begin, ensure that you have the following set up:
- A web server running PHP.
- MySQL database server.
- A basic understanding of HTML, PHP, and SQL.
Step 1: Create a Database
CREATE DATABASE users;
Replace users with the desired name for your database. Here’s a step-by-step guide to creating a MySQL database:
Step 2: Create a Table
CREATE TABLE student ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, roll_no VARCHAR(255) NOT NULL, entry_date INT(1) NOT NULL );
id: An integer column that serves as the primary key and auto-increments for each new entry.name: A string (VARCHAR) column with a maximum length of 255 characters, which cannot be NULL (meaning it’s required).roll_no: An integer column that cannot be NULL.entry_date: A date column that cannot be NULL.
Step 3: Database Connection
The first step is to establish a connection to your MySQL database. You’ll need to provide the database hostname, username, password, and the name of the database you want to work with. This connection allows PHP to communicate with the database.
Create a file with connection.php where we are connecting our MySQL Database to PHP Code
<?php
$server = “localhost”;
$user = “root”;
$password = “”;
$database = “users”;
$conn = mysqli_connect($server, $user, $password,$database);
if (mysqli_connect_errno()) {
echo “Error in connection”. mysqli_connect_error();
}else{
echo”success”;
}
?>
Step 4: Create an HTML Form:
After connecting our MySQL Database to code. Create another file with index.php where we run our insert query and html form
index.php
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<link href=”https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css” rel=”stylesheet” integrity=”sha384-T3c6CoIi6uLrA9TneNEoa7RxnatzjcDSCmG1MXxSR1GAsXEV/Dwwykc2MPK8M2HN” crossorigin=”anonymous”>
<title>Document</title>
</head>
<body>
<div class=”container”>
<div class=”row”>
<div class=”col-4 col-md-4 col-lg-4″ ></div>
<div class=”col-4 col-md-4 col-lg-4 mt-4″ >
<form method=”post” action=”index.php”>
<div class=” mb-3″>
<label for=”exampleInputEmail1″ class=”form-label”>User Name</label>
<input type=”text” name=”name” class=”form-control” id=”exampleInputEmail1″ aria-describedby=”emailHelp”>
</div>
<div class=” mb-3″>
<label for=”exampleInputPassword1″ class=”form-label”>Roll No</label>
<input type=”text” name=”rollNo” class=”form-control” id=”exampleInputPassword1″>
</div>
<button type=”submit” name=”submit” class=”btn btn-primary”>Submit</button>
</form>
</div>
<div class=”col-4 col-md-4 col-lg-4″ ></div>
</div>
</div>
<script src=”https://cdn.jsdelivr.net/npm/@popperjs/core@2.11.8/dist/umd/popper.min.js” integrity=”sha384-I7E8VVD/ismYTF4hNIPjVp/Zjvgyol6VFvRkX/vR+Vc4jQkC+hVqc2pM8ODewa9r” crossorigin=”anonymous”></script>
<script src=”https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.min.js” integrity=”sha384-BBtl+eGJRgqQAUMxJ7pMwbEyER4l1g+O15P+16Ep7Q9Q+zqX6gSbd85u4mG4QzX+” crossorigin=”anonymous”></script>
</body>
</html>
This is the html form where we will fill our form and then insert into database.
Step 5: PHP Script for Data Insertion:
Create another file with index.php where we run our insert query
<?php
include(“connection.php”);
if (isset($_POST[“submit”])) {
$name = $_POST[“name”];
$rollNo = $_POST[“rollNo”];
$time = time();
$insert = “INSERT INTO student (name,roll_no,entry_date) VALUES (‘$name’,’$rollNo’,’$time’)”;
$run = mysqli_query($conn, $insert);
if ($run) {
echo “<script>alert(‘insertion success’)</script>”;
}else{
echo “<script>alert(‘insertion failed’)</script>”;
}
}
?>
This is insertion code. Here is the insertion query to insert data into database.
More Articles: