How to Insert Data into MySQL Database Using PHP (mysqli) – A Step-by-Step Guide

Introduction

In today’s web development landscape, managing and interacting with databases is a crucial skill for creating dynamic applications. One of the most popular relational databases is MySQL, and PHP is a widely-used language for server-side scripting. If you’re looking to learn how to insert data into a MySQL database using PHP, specifically with the mysqli extension, you’ve come to the right place.

In this step-by-step guide, we will walk you through the process of setting up your development environment, connecting to your MySQL database, and executing data insertion queries using PHP’s mysqli functions. Whether you’re a beginner looking to grasp the basics or a seasoned developer seeking to refresh your skills, this tutorial will provide you with the knowledge and tools you need to efficiently handle data insertion in your web applications.

By the end of this guide, you’ll have a solid understanding of how to use mysqli to manage database records, ensuring your applications are both functional and efficient. Let’s dive in and start building your database interaction skills!

Steps for inserting data into mysql using php

To insert data into a MySQL database using PHP, you can follow these steps:

• Connect to MySQL Database: First, establish a connection to your MySQL database server using PHP’s ‘mysqli‘.
• Construct SQL Query: Create an SQL INSERT statement with the data you want to insert into the database.
• Execute the SQL Query: Use PHP to execute the SQL query against the MySQL database.

Connect to MySQL Database: dbconfig.php

<?php
   //Database Details
    $hostName = "localhost";//If your database is on remote machine,replace localhost with IP Address 
    $DBname= "testdb";
    $DBuser= "testuser";
    $userPass= ""; //no password
    
    // Create connection
    $conn = mysqli_connect($hostName , $DBuser, $userPass, $DBname);    
    // Check connection 
    if ($conn->connect_error) {
    die("Connection to mysql database failed: " . $conn->connect_error);
    }
    
    echo "Connect to mysql database established successfully!";    
    mysqli_close($conn);

?>

Create User Table: tbl_users

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Code to insert data into mysql table using php:

<?php
    include 'dbconfig.php';
    
    // Sample data to be inserted
    $name = "John Doe";
    $email = "john@example.com";
    $age = 30;
    
    // Construct SQL query
    $sql = "INSERT INTO tbl_users (name, email, age) VALUES ('$name', '$email', '$age')";
    
    // Execute SQL query
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
    
    // Close connection
    $conn->close();
?>

It’s important to note that this example uses basic SQL string concatenation which can be vulnerable to SQL injection attacks. To prevent this, you should consider using prepared statements with parameterized queries, especially if your data comes from user inputs. If you’re dealing with sensitive data, it’s crucial to handle SQL injection vulnerabilities properly. You can achieve this using PDO or mysqli prepared statements.

In upcoming articles, we will implement insert operation using PDO and mysqli prepared statements.