Database Management with PHP

Database management is an essential aspect of any web application, as it allows for the storage and retrieval of data. PHP provides several built-in functions and extensions that make it easy to interact with databases.

In this article, we will explore how to perform various database management tasks using PHP.

Connecting to a Database

To interact with a database, we first need to establish a connection. PHP provides the `mysqli` and `PDO` extensions for this purpose.

Using mysqli, we can connect to a MySQL database:

“`php
$servername = “localhost”;
$username = “username”;
$password = “password”;
$dbname = “database_name”;

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

// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
} else {
echo “Connected successfully”;
}
“`

Using PDO, we can connect to a variety of database systems, including MySQL, PostgreSQL, and SQLite:

“`php
$dsn = “mysql:host=localhost;dbname=database_name”;
$username = “username”;
$password = “password”;

try {
$conn = new PDO($dsn, $username, $password);
echo “Connected successfully”;
} catch (PDOException $e) {
die(“Connection failed: ” . $e->getMessage());
}
“`

Executing SQL Queries

Once the connection is established, we can execute SQL queries to interact with the database.

With mysqli, we can use the `query()` method to execute a SQL query:

“`php
$sql = “SELECT * FROM users”;
$result = $conn->query($sql);

if ($result) {
while ($row = $result->fetch_assoc()) {
echo “Name: ” . $row[“name”] . “, Email: ” . $row[“email”];
}
} else {
echo “Error: ” . $conn->error;
}
“`

With PDO, we can use the `query()` method or prepare a statement using `prepare()` and execute it:

“`php
$sql = “SELECT * FROM users”;
$result = $conn->query($sql);

if ($result) {
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
echo “Name: ” . $row[“name”] . “, Email: ” . $row[“email”];
}
} else {
echo “Error: ” . $conn->errorInfo()[2];
}
“`

Inserting Data

To insert data into a database, we can use the `INSERT INTO` SQL statement.

With mysqli, we can use the `query()` method to execute the SQL statement:

“`php
$name = “John Doe”;
$email = “johndoe@example.com”;

$sql = “INSERT INTO users (name, email) VALUES (‘$name’, ‘$email’)”;
$result = $conn->query($sql);

if ($result) {
echo “Data inserted successfully”;
} else {
echo “Error: ” . $conn->error;
}
“`

With PDO, we can use prepared statements to insert data safely:

“`php
$name = “John Doe”;
$email = “johndoe@example.com”;

$sql = “INSERT INTO users (name, email) VALUES (:name, :email)”;
$stmt = $conn->prepare($sql);
$stmt->bindParam(“:name”, $name);
$stmt->bindParam(“:email”, $email);
$result = $stmt->execute();

if ($result) {
echo “Data inserted successfully”;
} else {
echo “Error: ” . $stmt->errorInfo()[2];
}
“`

Updating Data

To update data in a database, we can use the `UPDATE` SQL statement.

With mysqli, we can use the `query()` method to execute the SQL statement:

“`php
$name = “Jane Doe”;
$email = “janedoe@example.com”;
$id = 1;

$sql = “UPDATE users SET name=’$name’, email=’$email’ WHERE id=$id”;
$result = $conn->query($sql);

if ($result) {
echo “Data updated successfully”;
} else {
echo “Error: ” . $conn->error;
}
“`

With PDO, we can use prepared statements to update data safely:

“`php
$name = “Jane Doe”;
$email = “janedoe@example.com”;
$id = 1;

$sql = “UPDATE users SET name=:name, email=:email WHERE id=:id”;
$stmt = $conn->prepare($sql);
$stmt->bindParam(“:name”, $name);
$stmt->bindParam(“:email”, $email);
$stmt->bindParam(“:id”, $id);
$result = $stmt->execute();

if ($result) {
echo “Data updated successfully”;
} else {
echo “Error: ” . $stmt->errorInfo()[2];
}
“`

Deleting Data

To delete data from a database, we can use the `DELETE FROM` SQL statement.

With mysqli, we can use the `query()` method to execute the SQL statement:

“`php
$id = 1;

$sql = “DELETE FROM users WHERE id=$id”;
$result = $conn->query($sql);

if ($result) {
echo “Data deleted successfully”;
} else {
echo “Error: ” . $conn->error;
}
“`

With PDO, we can use prepared statements to delete data safely:

“`php
$id = 1;

$sql = “DELETE FROM users WHERE id=:id”;
$stmt = $conn->prepare($sql);
$stmt->bindParam(“:id”, $id);
$result = $stmt->execute();

if ($result) {
echo “Data deleted successfully”;
} else {
echo “Error: ” . $stmt->errorInfo()[2];
}
“`

Closing the Connection

After we are finished using the database connection, it is important to close the connection to free up resources.

With mysqli, we can use the `close()` method to close the connection:

“`php
$conn->close();
“`

With PDO, the connection is automatically closed when the `$conn` object is destroyed. However, we can close it explicitly if needed:

“`php
unset($conn);
“`

Conclusion

In this article, we have explored how to perform various database management tasks using PHP. These tasks include connecting to a database, executing SQL queries, inserting data, updating data, deleting data, and closing the connection. By understanding these concepts, you should be able to easily manage a database using PHP in your web applications.