Unit 5: Database Operations

5.1 Introduction to MySQL - Create a database.

5.2 Connecting to a MySQL database: MySQL database server from PHP

5.3 Database operations: Insert data, Retrieving the Query result

5.4 Update and delete operations on table data

5.1 Introduction to MySQL - Create a database:

MySQL is a popular relational database management system. To create a database in MySQL
using PHP, follow these steps:

1. Establish a connection to the MySQL server using mysqli_connect().

2. Execute an SQL query to create a new database using mysqli_query().

3. Check if the query execution was successful using mysqli_query().

4. Close the connection using mysqli_close().

Example:

<?php
$servername = "localhost";
$username = "root";
$password = "";

// Create a connection to MySQL server

$conn = mysqli_connect($servername, $username, $password);

// Check if the connection was successful

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

1|Page
// Create a new database
$sql = "CREATE DATABASE mydatabase";

if (mysqli_query($conn, $sql)) {
echo "Database created successfully";
} else {
echo "Error creating database: " . mysqli_error($conn);
}

// Close the connection

mysqli_close($conn);
?>

5.2 Connecting to a MySQL database: MySQL database server from PHP:

To connect to a MySQL database server using PHP, follow these steps:

1. Establish a connection to the MySQL server using mysqli_connect(), providing the

server name, username, password, and the name of the database.

2. Check if the connection was successful using mysqli_connect_error().

3. Perform database operations using the established connection.

4. Close the connection using mysqli_close().

Example:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydatabase";

// Create a connection to MySQL server

2|Page
$conn = mysqli_connect($servername, $username, $password,
$dbname);

// Check if the connection was successful

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
} else {
echo "Connected successfully";
}

// Perform database operations

// Close the connection

mysqli_close($conn);
?>

5.3 Database operations: Insert data, Retrieving the Query result:

To insert data into a MySQL database table and retrieve the query result, follow these steps:

1. Establish a connection to the MySQL server.

2. Execute an SQL query to insert data into the table using mysqli_query().

3. Check if the query execution was successful.

4. To retrieve data, execute a SELECT query using mysqli_query().

5. Use mysqli_num_rows() to check if any rows were returned.

6. Fetch each row using mysqli_fetch_assoc() or similar functions, and access the values
using column names.

7. Close the connection.

Example:

<?php

3|Page
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydatabase";

// Create a connection to MySQL server

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

// Check if the connection was successful

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// Insert data into a table

$sql = "INSERT INTO users (name, email) VALUES ('John Doe',
'johndoe@example.com')";

if (mysqli_query($conn, $sql)) {
echo "Data inserted successfully";
} else {
echo "Error inserting data: " . mysqli_error($conn);
}

// Retrieve data from a table

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
// Output data of each row
while ($row = mysqli_fetch_assoc($result)) {

4|Page
 echo "Name: " . $row["name"] . ", Email: " . $row["email"] . "<br>";
}
} else {
echo "No records found";
}

// Close the connection

mysqli_close($conn);
?>

In the above example, after establishing the database connection, we execute a SELECT
query to retrieve all the records from the "users" table using mysqli_query(). We then check
if there are any rows returned using mysqli_num_rows(). If there are rows, we loop through
the result using mysqli_fetch_assoc() to fetch each row as an associative array. We can then
access the values of each column using the column names as keys.

Finally, we close the database connection using mysqli_close().

5.4 Update and delete operations on table data:

To perform update and delete operations on table data in MySQL using PHP, follow these
steps:

1. Establish a connection to the MySQL server.

2. Execute an SQL query to update or delete data in the table using mysqli_query().

3. Check if the query execution was successful.

4. Close the connection.

Example:

<?php
$servername = "localhost";
$username = "root";
$password = "";

5|Page
$dbname = "mydatabase";

// Create a connection to MySQL server

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

// Check if the connection was successful

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// Update data in a table

$sql = "UPDATE users SET email='newemail@example.com' WHERE name='John Doe'";

if (mysqli_query($conn, $sql)) {
echo "Data updated successfully";
} else {
echo "Error updating data: " . mysqli_error($conn);
}

// Delete data from a table

$sql = "DELETE FROM users WHERE name='John Doe'";

if (mysqli_query($conn, $sql)) {
echo "Data deleted successfully";
} else {
echo "Error deleting data: " . mysqli_error($conn);
}

// Close the connection

6|Page
mysqli_close($conn);
?>

In the above example, we establish a connection to the MySQL server and select the
database. We then execute an UPDATE query to update the email field for the user with the
name 'John Doe' in the 'users' table. Next, we execute a DELETE query to delete the row where
the name is 'John Doe'. If the update or delete operation is successful, it displays a success
message; otherwise, it shows an error message. Finally, we close the database connection using
mysqli_close().

7|Page