Last updated on December 11th, 2023
How to store information in MySQL database in XAMPP?
We will store information in a database using the INSERT INTO statement.
You already know how to create a database with a table and columns in it. Let’s store some information in the database. Let’s have a simple form with one field to fill in information and let’s store the information from that field in the database.
Create using phpMyAdmin a database named ‘msg_db’, in it create a table named ‘messages’ and in the table create a column named ‘user_message’.
Create an HTML page named msg.html and put the following code in the page:
<!DOCTYPE html> <html> <head> <title>Simple Form</title> </head> <body> <form action="store_data.php" method="post"> <label for="message">Enter a Message:</label><br> <input type="text" id="message" name="user_message"><br><br> <input type="submit" value="Submit"> </form> </body> </html>
This HTML form displays a simple input field where the user can type a message.
The form has a submit button that sends the entered message to the server for processing.
Now create a PHP page named store_data.php and put the following code in the page:
<?php
// Connect to the MySQL database (to be filled with your own connection details)
$conn = new mysqli("localhost", "root", "", "msg_db");
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Check if the form has been submitted
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$message = $_POST['user_message']; // Get the message from the form
// Insert the message into the 'messages' table in the database
$sql = "INSERT INTO messages (user_message) VALUES ('$message')";
if ($conn->query($sql) === TRUE) {
echo "Message stored successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
}
// Close the database connection
$conn->close();
?>
Put the two pages in the htdocs folder. Open msg.html via address localhost/msg.html, enter some text in the field, for example “Test message” and click the Submit button. If everything is fine, you should see the message “Message stored successfully”.
In this example:
1. The PHP script establishes a connection to the MySQL database, assuming you fill in your own database connection details. The syntax of the command is as follows:
$conn = new mysqli(“localhost”, “your_username”, “your_password”, “your_database_name”);
This line creates a new instance of the mysqli class, which is used for connecting to the MySQL database. The values inside the double quotes represent the server name (localhost), your MySQL username, your MySQL password, and the name of your database.
By default, the server name is localhost, the XAMPP username is root, the password is blank, and the example database name is msg_db:
$conn = new mysqli(“localhost”, “root”, “”, “msg_db”);
2. If the connection to the database fails, an error message is displayed:
if ($conn->connect_error) { die(“Connection failed: ” . $conn->connect_error); }
This if statement checks if there was an error in establishing the database connection. If an error occurs, it uses the die function to print an error message that includes the specific error from the database connection.
3. The script checks if the form has been submitted. If it has, it retrieves the message entered in the form:
if ($_SERVER[“REQUEST_METHOD”] == “POST”) { … }
This conditional statement checks if the form has been submitted using the POST method. It ensures that the code for handling the form submission runs only when the form has been submitted.
4. The script then inserts the message into the ‘messages’ table in the database:
$message = $_POST[‘user_message’];
This line retrieves the value of the form input field with the name ‘user_message’ using the $_POST superglobal. It assigns this value to the variable $message.
$sql = “INSERT INTO messages (user_message) VALUES (‘$message’)”;
This line creates an SQL query that inserts the value of the $message variable into the ‘messages’ table in the database. It uses the INSERT INTO statement to add a new record to the table, specifying the column name ‘user_message’ and the corresponding value from the $message variable. So, the syntax of the INSERT INTO statement is as follows:
INSERT INTO table_name (table_column1, table_column2…) VALUES (value1, value2…);
5. If the insertion is successful, a success message is displayed; otherwise, an error message with the details of the SQL query error is shown:
if ($conn->query($sql) === TRUE) { echo “Message stored successfully”; } else { echo “Error: ” . $sql . “<br>” . $conn->error; }
This if-else statement executes the SQL query using the query() method of the database connection object. If the query is successful, it prints a success message. If an error occurs, it prints an error message along with the specific SQL query and the database error.
6. Finally, the script closes the database connection:
$conn->close();
This line closes the database connection to free up resources and prevent unnecessary strain on the server.