How to Input Data into MySQL using PHP Scripts

In this blog post, we will explore how to input data into a MySQL database using PHP scripts. This process is essential for developers who want to build dynamic websites or applications where user data needs to be stored and accessed.

Prerequisites

Before we start, make sure you have the following:

A local development environment with PHP and MySQL installed.
Basic understanding of HTML, CSS, and JavaScript.
Knowledge on creating a simple PHP script.
Familiarity with MySQL syntax and database structure.

Setting Up Your Environment

First, create a new PHP file (for example, inputData.php). In this file, we will write the code to handle data input into our MySQL database.

Next, let's configure our MySQL connection settings. Create a new file named config.php, which will contain the following lines:

<?php
$servername = "localhost";
$username = "root"; // Your username
$password = ""; // Your password
$dbname = "test_database"; // Database name

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

Make sure to replace the $username, $password, and $dbname values with your actual MySQL username, password, and database name.

Writing the PHP Script

Now that we have our environment set up, let's write the script to input data into our MySQL database using PHP. Create a new file named addData.php. Add the following code:

<?php
require_once 'config.php';

// Get input data from user
$name = $_POST['name'];
$email = $_POST['email'];

// Prepare and bind statement
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);

// Execute query
if ($stmt->execute()) {
    echo "New record created successfully";
} else {
    echo "Error: " . $stmt->error;
}

// Close statement and connection
$stmt->close();
$conn->close();
?>

This code does the following:

Requires our config.php file to access the MySQL database settings.
Gets the input data from the user via the $_POST superglobal array.
Prepares and binds a statement to insert the new record into the users table.
Executes the query, handling any potential errors.
Closes the statement and connection.

Testing the Script

To test our script, create an HTML form in a separate file (for example, form.html) to submit data to the addData.php script:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Input Data</title>
</head>
<body>
    <form action="addData.php" method="post">
        <label for="name">Name:</label>
        <input type="text" id="name" name="name">
        <br>
        <label for="email">Email:</label>
        <input type="email" id="email" name="email">
        <br>
        <button type="submit">Submit</button>
    </form>
</body>
</html>

Save both files (addData.php and form.html) in the same directory, then run your PHP script by visiting form.html in your browser. Fill out the form and submit it to see if your data is being stored in the MySQL database.

Published November, 2015