Current Location: Home> Latest Articles> How to Achieve Efficient Database Management with PHP?

How to Achieve Efficient Database Management with PHP?

M66 2025-06-13

1. Establishing a Database Connection

Before performing database operations, the first step is to establish a connection with the database. PHP provides several built-in functions for this task, with mysqli_connect() being the most commonly used. Below is an example code for connecting to a MySQL database:

<?php
$servername = "localhost"; // Database server address
$username = "root"; // Database username
$password = "123456"; // Database password
$dbname = "test"; // Database name

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

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

2. Querying Data

Once the connection is established, you can begin performing database operations. One common operation is querying data. PHP provides several functions to execute queries, such as mysqli_query() and mysqli_fetch_array(). Here is an example code for querying data:

<?php
$sql = "SELECT id, name, age FROM users"; // Query statement

// Execute the query
$result = mysqli_query($conn, $sql);

// Handle the result
if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Age: " . $row["age"] . "<br>";
    }
} else {
    echo "No data found";
}

// Free result memory
mysqli_free_result($result);
?>

3. Inserting Data

To insert new data into the database, you can use the mysqli_query() function. Below is an example code for inserting data into the database:

<?php
$sql = "INSERT INTO users (name, age, email) VALUES ('John Doe', 25, 'john@example.com')"; // Insert statement

// Execute the insertion
if (mysqli_query($conn, $sql)) {
    echo "Data inserted successfully";
} else {
    echo "Data insertion failed: " . mysqli_error($conn);
}
?>

4. Updating Data

To update existing data in the database, you can use the mysqli_query() function. Below is an example code for updating data in the database:

<?php
$sql = "UPDATE users SET age = 30 WHERE name = 'John Doe'"; // Update statement

// Execute the update
if (mysqli_query($conn, $sql)) {
    echo "Data updated successfully";
} else {
    echo "Data update failed: " . mysqli_error($conn);
}
?>

5. Deleting Data

To delete data from the database, you can use the mysqli_query() function. Below is an example code for deleting data:

<?php
$sql = "DELETE FROM users WHERE age < 18"; // Delete statement

// Execute the deletion
if (mysqli_query($conn, $sql)) {
    echo "Data deleted successfully";
} else {
    echo "Data deletion failed: " . mysqli_error($conn);
}
?>

6. Closing the Database Connection

After completing all database operations, it's important to close the database connection to free up resources. You can use the mysqli_close() function to close the connection. Here is an example code for closing the connection:

<?php
// Close the connection
mysqli_close($conn);
echo "Connection closed";
?>

Conclusion

Through the code examples above, we've demonstrated how to perform common database operations in PHP, such as connecting to the database, querying data, inserting data, updating data, and deleting data. In real-world applications, you may encounter more complex database operations, but these basic operations are essential for every PHP developer. We hope this article helps you understand and master PHP database operations.

Related