How to Connect PHP to a MySQL Database and Insert Data Using XAMPP – A Step-by-Step Guide

By /

In this tutorial, you’ll learn how to connect PHP to a MySQL database and insert data into it using XAMPP. This guide is ideal for beginners looking to build dynamic, data-driven websites. We’ll cover everything from setting up your environment to writing PHP code to perform database operations. Let’s dive in!

WhatsApp Group Join Now
Telegram Channel Join Now
Instagram Profile Join Now

frontend.php code

<h1>Customer details form</h1>
<form action="backend.php" method="post">
    Enter Customer Name :
    <input type="text" name="c_name"><br><br>
    Enter Customer Mobile :
    <input type="number" name="c_mobile"><br><br>
    Enter Customer email :
    <input type="email" name="c_email"><br><br>
    Enter Customer Password :
    <input type="password" name="c_password"><br><br>
    <button>Save Customer</button>
</form>

backend.php code 

<?php
echo "<pre>";
print_r($_POST);
echo "</pre>";

$conn = mysqli_connect("localhost", "root", "", "a2z41");
$c_name = $_POST['c_name'];
$c_mobile = $_POST['c_mobile'];
$c_email = $_POST['c_email'];
$c_password = $_POST['c_password'];
$sql = "insert into customer (customer_name,customer_mobile,customer_email,customer_password) values ('$c_name','$c_mobile','$c_email','$c_password')";
echo $sql;
echo "<br><br>";
$result = mysqli_query($conn, $sql);
if ($result) {
    echo "data inserted";
} else {
    echo "data not inserted";
}

1. Introduction to PHP and MySQL <a name=”introduction”></a>

PHP is a popular server-side scripting language that is widely used for web development, while MySQL is a powerful relational database management system. When combined, PHP and MySQL enable developers to create dynamic websites and applications with backend databases.

2. Setting Up XAMPP <a name=”setting-up-xampp”></a>

XAMPP is a free, open-source software that includes Apache (for running a local server), MySQL (for managing databases), and PHP. Here’s how to install and configure XAMPP:

  1. Download XAMPP: Go to the XAMPP website and download the version that matches your operating system.
  2. Install XAMPP: Run the downloaded file and follow the installation instructions.
  3. Start Apache and MySQL: Open the XAMPP Control Panel and start the Apache and MySQL services. These services will allow you to run PHP scripts and access MySQL databases locally.

3. Creating a MySQL Database <a name=”creating-a-database”></a>

Once XAMPP is set up, it’s time to create a MySQL database where we’ll store our data.

  1. Open phpMyAdmin: Go to your browser and type http://localhost/phpmyadmin/ to open phpMyAdmin, a web interface for managing MySQL databases.
  2. Create a Database: Click on “Databases” at the top. In the “Create database” field, enter a name for your database (e.g., test_database) and click “Create.”
  3. Create a Table: Click on your new database, then click on the “SQL” tab or “Create table.” For this tutorial, let’s create a table called users with fields for id, name, and email.

4. Writing PHP Code to Connect to MySQL <a name=”writing-php-code”></a>

Now that we have a database, let’s write PHP code to connect to it.

  1. Open Your Code Editor: Open a code editor like VS Code or Sublime Text.
  2. Create a New PHP File: In your htdocs folder (located in the XAMPP installation directory), create a file called database_connection.php.
  3. Write Connection Code: Use the following code to connect to MySQL.phpCopy code<?php $servername = "localhost"; $username = "root"; // Default username for XAMPP $password = ""; // Leave blank for XAMPP $dbname = "test_database"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } echo "Connected successfully"; ?> This code establishes a connection to the MySQL database. If the connection is successful, it will display “Connected successfully” on the page.

5. Inserting Data into the Database <a name=”inserting-data”></a>

Let’s write a PHP script to insert data into the users table.

  1. Create a New PHP File: In the same folder, create a file called insert_data.php.
  2. Write Insertion Code: Use the following code to insert data.phpCopy code<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "test_database"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // SQL query to insert data $name = "John Doe"; $email = "john.doe@example.com"; $sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')"; if ($conn->query($sql) === TRUE) { echo "New record created successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } $conn->close(); ?> This code inserts a new record into the users table with the name “John Doe” and the email “john.doe@example.com“.

6. Testing the Database Connection and Insertion <a name=”testing”></a>

To test if everything works as expected:

  1. Start Apache and MySQL in XAMPP: Make sure both services are running.
  2. Run the PHP Scripts: Open your browser and go to http://localhost/database_connection.php. If the connection is successful, you’ll see a confirmation message.
  3. Test Data Insertion: Go to http://localhost/insert_data.php. If the data insertion is successful, you’ll see a confirmation message.
  4. Check the Database: Go to phpMyAdmin, open the test_database, and view the users table. You should see the new record with John Doe’s information.

7. Best Practices for Database Security <a name=”security-tips”></a>

When working with databases, it’s important to follow best practices to ensure security and efficiency:

  • Use Prepared Statements: Prevent SQL injection by using prepared statements with parameterized queries instead of directly inserting variables into SQL queries.
  • Validate and Sanitize User Input: Always validate and sanitize input data to avoid security vulnerabilities.
  • Encrypt Sensitive Data: For sensitive information like passwords, consider using encryption or hashing techniques.

8. Conclusion <a name=”conclusion”></a>

Congratulations! You’ve successfully learned how to connect PHP to a MySQL database using XAMPP and insert data into it. This skill is foundational for building database-driven applications. With this knowledge, you’re on your way to developing dynamic websites with robust backend systems.

Stay tuned for more tutorials, and don’t forget to practice to solidify your skills!

Scroll to Top