Database Management with PHP

Database management with PHP involves connecting to a database, executing SQL queries, and retrieving and modifying data. This can be done using the native MySQL functions provided by PHP, or by using an ORM (Object-Relational Mapping) library such as Doctrine.

Here is a basic example of database management with PHP using the native MySQL functions:

1. Connecting to the database:

“`php
$hostname = ‘localhost’;
$username = ‘root’;
$password = ”;
$database = ‘mydatabase’;

$connection = mysqli_connect($hostname, $username, $password, $database);

if (!$connection) {
die(‘Connection failed: ‘ . mysqli_connect_error());
}
“`

2. Executing SQL queries:

“`php
$query = “SELECT * FROM users”;
$result = mysqli_query($connection, $query);

if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
// Do something with each row of data
echo “Username: ” . $row[‘username’] . “
“;
}
} else {
echo “No rows found.”;
}

mysqli_free_result($result);
“`

3. Inserting data:

“`php
$username = ‘john_doe’;
$password = ‘password123’;

$query = “INSERT INTO users (username, password) VALUES (‘$username’, ‘$password’)”;
$result = mysqli_query($connection, $query);

if ($result) {
echo “Data inserted successfully.”;
} else {
echo “Data insert failed: ” . mysqli_error($connection);
}
“`

4. Updating data:

“`php
$username = ‘john_doe’;
$newPassword = ‘newpassword123′;

$query = “UPDATE users SET password=’$newPassword’ WHERE username=’$username'”;
$result = mysqli_query($connection, $query);

if ($result) {
echo “Data updated successfully.”;
} else {
echo “Data update failed: ” . mysqli_error($connection);
}
“`

5. Deleting data:

“`php
$username = ‘john_doe’;

$query = “DELETE FROM users WHERE username=’$username'”;
$result = mysqli_query($connection, $query);

if ($result) {
echo “Data deleted successfully.”;
} else {
echo “Data delete failed: ” . mysqli_error($connection);
}
“`

6. Closing the database connection:

“`php
mysqli_close($connection);
“`

Note: It is important to sanitize user input before using it in SQL queries to prevent SQL injection attacks. You can use prepared statements or escape user input using `mysqli_real_escape_string()` function.