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:
<?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.
Hello, my name is Deependra Singh. I hold B.tech degree in Computer Science & Engineering from Institute of Engineering & Technology, Lucknow. Currently, I am working in Steel Authority of India Limited as a Software Developer.