English
English
In web development, interacting with databases is a common task, and MySQL is one of the most widely used relational databases. This article will guide you step-by-step on how to connect PHP to a MySQL database and perform basic database operations.
To connect to a MySQL database, you need the database host, username, password, and database name. PHP's built-in mysqli extension provides a straightforward way to establish this connection.
$servername = "localhost"; // Database host
$username = "root"; // Username
$password = "password"; // Password
$dbname = "mydatabase"; // Database name
// 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 snippet creates a connection object using the mysqli class and checks if the connection was successful by inspecting the connect_error property.
After connecting, you can execute SQL queries to create tables and insert data. The example below shows how to create a "users" table and insert a record into it.
// SQL to create table
$sql = "CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL,
email VARCHAR(50) NOT NULL
)";
// Execute query to create table
if ($conn->query($sql) === TRUE) {
echo "Table created successfully!";
} else {
echo "Error creating table: " . $conn->error;
}
// SQL to insert record
$sql = "INSERT INTO users (username, email) VALUES ('John', '[email protected]')";
// Execute query to insert data
if ($conn->query($sql) === TRUE) {
echo "Record inserted successfully!";
} else {
echo "Error inserting record: " . $conn->error;
}
The query method executes the SQL, returning true on success. If an error occurs, the error property holds the message.
Fetching data from the database is often required. Below is an example of retrieving all records from the "users" table and outputting each row's details.
// SQL to select records
$sql = "SELECT * FROM users";
// Execute the query and get result set
$result = $conn->query($sql);
// Check if records exist
if ($result->num_rows > 0) {
// Output data of each row
while ($row = $result->fetch_assoc()) {
echo "id: " . $row["id"] . " - username: " . $row["username"] . " - email: " . $row["email"] . "<br>";
}
} else {
echo "No records found";
}
Use num_rows to check the number of rows returned, and fetch_assoc to fetch each row as an associative array for easy access.
After finishing database operations, close the connection with the close method to free resources.
// Close the database connection
$conn->close();
This article covered the complete process of connecting PHP with a MySQL database, including creating the connection, executing SQL queries to create tables and insert data, querying data, and closing the connection. With these examples, you can confidently handle basic PHP-MySQL interactions, laying a strong foundation for further development.
