UNIT 5 DATABASE OPERATIONS NOTES

Sr.no Syllabus
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 Updated and Delete operations on table data

Position in Question Paper Total Marks-14

Q.1. e) 2-Marks.
Q.1. g) 2-Marks.
Q.3. a) 4-Marks.
Q.5. a) 6-Marks.
Q.5. c) 6-Marks.
Q.6. a) 6-Marks.

PYQs Repeated.

 List any four data types in MYSQL 2m

 Explain delete operation of PHP on table data.4m
 Inserting and retrieving the query result operations4m
 How do you connect MYSQL database with PHP. 6m
 What is inheritance?
Write update operation on table data.6m
 List two database operations.2m
 Write steps to create database using PHP 4m
 Explain queries to update and delete data in the database. 4m
 Write a program to connect PHP with MYSQL. 6m
 State the use of serialization.
 State the query to insert data in the database. 6m
 Write syntax of constructing PHP webpage with MySQ 2m
 Write update and delete operations on table data 4m
 Explain inserting & Retrieving the query result operations. 4m
 Write a program to connect PHP with MySQL 6m
 Elaborate the following:
_call()
Mysqli_connect()
 List out the database operations in php
 Explain inserting and retrieving the query result operations.4m
 Write PHP program to demonstrate delete operation on table data.6m
 Write PHP program to demonstrate database operation using PHP and
MySQL. 6m
 State advantages of PHP-MySQL. 2m
 Write PHP program To find largest of two number for connecting toMySQL server.4m
 Write a PHP program to count total number of rows in the
database table. 4m
 Explain Following Methods
MySQL_select_db()
ImageCopyReszied()4m
 Develop PHP program to create database and insert records in
database.4m
 Create a html form "result.html" to accept Rollno of student using submit button. Write "result.php"
code to check the result of student "pass" or "fail". Create a table result_table
in MySQL database "My_db" with two columns Rollno and Status. Also write PHP code to delete a
record from result_table.6m

5.1 Introduction to MySQL, Create a Database.

 MySQL is a popular database used in PHP to store and manage data.

 PHP uses functions like `mysqli_connect()` to connect and interact with MySQL databases.
 It allows performing operations like insert, update, delete, and fetch data using SQL queries.
 PHP and MySQL together are widely used to build dynamic and data-driven websites.

Syntax in php
<?php
$conn = mysqli_connect("localhost", "username", "password", "database_name");
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
?>
Features of MySQL in PHP.

 MySQL is an open-source relational database used to store and manage data efficiently.
 It supports SQL (Structured Query Language) for creating, reading, updating, and deleting
records.
 MySQL can be easily connected with PHP using built-in functions like mysqli or PDO.
 It offers high speed, reliability, and scalability for both small and large web applications.
 MySQL supports multiple users and provides secure data access with user roles and privileges.
 It works well with PHP to build dynamic websites that interact with databases in real-.

Datatypes of mysql in php.

Creating a DataBase:
Creating a database in PHP is done using MySQLi or PDO functions.
The mysqli_connect() function is used to connect to the MySQL server.
The CREATE DATABASE SQL statement is used to create a new database.
You must check if the connection is successful before executing the query.
Database creation is mostly done during setup or installation of applications.
Steps for creating database .
Step 1: Set Server Details
First, define variables to store your database connection details like servername,
username, and password.

Step 2: Create Connection

Use the mysqli_connect() function to establish a connection with the MySQL server
using the above details.

Step 3: Prepare the Query

Write the SQL query to create a database using CREATE DATABASE database_name;.

Step 4: Execute the Query

Use mysqli_query() to run the SQL command on the connection.

Step 5: Check for Success or Failure

Use a simple if statement to display whether the database was created successfully or not.

Step 6: Close the Connection

Use mysqli_close() to close the database connection (optional but good practice).

Syntax:
<?php
$conn = new mysqli("localhost", "root", "");
$sql = "CREATE DATABASE dbname";
$conn->query($sql); Ouput
$conn->close();
Database created successfully
?>
Example:
<?php
$servername = "localhost";
$username = "root";
$password = "";

// Step 2: Create connection

$conn = new mysqli($servername, $username, $password);
// Step 3: Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Step 4: Create database

$sql = "CREATE DATABASE ifdept";
if ($conn->query($sql) === TRUE) {
echo "Database created successfully";
} else {
echo "Error creating database: " . $conn->error;
}

// Step 5: Close connection

$conn->close();
?>

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

 PHP uses the mysqli_connect() function to connect to a MySQL database.

 It requires parameters like hostname, username, password, and database name.
 If the connection is successful, PHP can interact with the database to perform operations.
 If the connection fails, we can use mysqli_connect_error() to display the error.
 It is important to close the connection using mysqli_close() after operations are done.
 This connection allows PHP to fetch, insert, update, or delete data from the MySQL
database.
 Always check the connection status to ensure smooth communication between PHP and the
MySQL database.

Steps:

1. **Use `mysqli_connect()`** function to establish a connection between PHP and MySQL.

2. **Pass database details** like host, username, password, and database name to the function.
3. **Store the connection in a variable**, usually named `$conn`.
4. **Check if the connection is successful** using an `if` condition.
5. **Use `mysqli_query()`** to run SQL queries on the connected database.
6. **Fetch data using functions** like `mysqli_fetch_assoc()` or `mysqli_fetch_array()`.
7. **Close the connection** at the end using `mysqli_close($conn);` to free resources.

Syntax:
mysqli_connect("hostname", "username", "password", "database_name");

Solution1:
<?php Output
$servername = "localhost";
Connected successfully
$username = "root";
$password = "";
// Connection
$conn = new mysqli($servername,$username, $password);
// For checking if connection issuccessful or not
if ($conn->connect_error)
{
die("Connection failed: ". $conn->connect_error);
}
echo "Connected successfully";
?>
.
Definition of `mysqli_connect()` in PHP

- `mysqli_connect()` is a built-in PHP function used to open a new connection to a MySQL

database.
- It helps connect PHP scripts to MySQL so you can store, read, or manage data.
- The function needs four main parameters: host, username, password, and database name.
- If the connection fails, it returns `false`, and we can handle it using `mysqli_connect_error()`.
- This function is commonly used in web applications that use databases.
- Always check the connection to avoid errors when working with databases.

### **Syntax:**
```php
mysqli_connect(host, username, password, dbname);
```

### **Example:**
Output

```php Connected successfully

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

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

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

5.3 Database Operations: Insert data, Retrieving the Query Result.

 Database operations in PHP involve connecting to a database, performing queries, and handling
the results.
 Using PHP, you can perform common operations like insert, select, update, and delete data from
a database.
 PHP commonly works with MySQL or MySQLi to manage database interactions.
 These operations allow dynamic websites to store and retrieve user data efficiently.
 Proper use of database operations improves functionality, performance, and security of web
applications.

Database Operations: Insert Data

 Insert operation is used to add new records into a database table.

 It is performed using the INSERT INTO SQL statement.
 The query specifies the table name and the column values to be inserted.
 PHP connects to the database using mysqli or PDO and executes the SQL.
 The SQL query is passed to PHP's query() function to insert the data.
 Proper database connection and error handling are important.
 This operation is useful for storing form inputs or dynamic data into tables.
Syntax:
INSERT INTO table_name (column1, column2) VALUES (value1, value2);

Example:
<?php
require_once 'login.php'; // contains DB connection details
$conn = new mysqli($hn, $un, $pw, $db);
if ($conn->connect_error) die($conn->connect_error);
$query = "INSERT INTO student (rollno, name, percent) VALUES ('CO103', 'Reena Patel',
98.45)";
$result = $conn->query($query); Output
if (!$result) die("Database access failed: " . $conn->error);
Data inserted successfully!
else
echo "Data inserted successfully!";
?>
Retrieving the Query Result.

 Retrieving data in PHP from a database is done using the SQL SELECT statement.
 This statement is used to fetch records from one or more tables in a database.
 PHP uses the query() function to execute the SQL SELECT query on the database
connection.
 The result returned by query() is stored in a result object which contains the records.
 num_rows is used to count how many rows (records) were fetched from the table.
 data_seek() and fetch_array() methods help loop through each record from the result.
 Retrieved data is usually displayed using HTML elements like <table> for a user-friendly
output.

Syntax:
$result = $conn->query("SELECT * FROM table_name");

Example:
<?php
require_once 'login.php'; // Database connection file

// Connect to the database

$conn = new mysqli($hn, $un, $pw, $db);
if ($conn->connect_error) die($conn->connect_error);

// SQL SELECT query to retrieve all records from student table

$query = "SELECT * FROM student";
$result = $conn->query($query);
if (!$result) die("Database access failed: " . $conn->error);

// Get total number of rows Output

$rows = $result->num_rows;
+----------+--------+-------------+
| Roll No. | Name | Percentage |
// Displaying table headers
+----------+--------+-------------+
echo "<table border='1'>
| 1 | Aryan | 85 |
<tr> | 2 | Rahul | 78 |
<th>Roll No.</th>
<th>Name</th> | 3 | Sneha | 90 |
<th>Percentage</th> +----------+--------+-------------+
</tr>";

// Loop to display each row

for ($j = 0 ; $j < $rows ; ++$j) {
$result->data_seek($j);
$row = $result->fetch_array(MYSQLI_NUM);
echo "<tr>";
for ($k = 0 ; $k < 3 ; ++$k)
echo "<td>$row[$k]</td>";
echo "</tr>";
}
echo "</table>";
?>

5.4 Update and Delete Operations on table data:

- The `UPDATE` statement in PHP is used to modify existing records in a MySQL table.
- It is commonly used with MySQLi or PDO to interact with the database.
- You must specify the table name and the column(s) you want to update.
- The `WHERE` clause is important to ensure only the desired rows are updated.
- Omitting `WHERE` will update all records in the table.
- It is used in admin panels, profile edits, or anywhere data changes are needed.
- Always check for a successful connection and query execution.
Always test the update on sample data before running it on real tables.

Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Example:
Output
<?php
$servername = "localhost"; Record updated successfully
$username = "root";
$password = "";
$dbname = "clg";

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

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Update query
$sql = "UPDATE staff SET lastname='technology' WHERE id=1";

if ($conn->query($sql) === TRUE) {

echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}

$conn->close();
?>
Delete operations on table data:
- The `DELETE` statement in SQL is used to remove one or more records from a table.
- It is usually combined with the `WHERE` clause to delete specific records based on a
condition.
- Without a `WHERE` clause, all rows in the table will be deleted, so it should be used carefully.
- The SQL `DELETE` query is created and passed to PHP's `query()` function to execute it.
- PHP connects to the database using `mysqli` or `PDO` before running the `DELETE` query.
- Example: Deleting a record from the `staff` table where `id = 1`.
- After successful deletion, a message like "Record deleted successfully" is shown.

Syntax:
DELETE FROM table_name WHERE condition;

**Example:
```php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "clg";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL DELETE statement
$sql = "DELETE FROM staff WHERE id=1";
// Execute the query
Output
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
Record deleted successfully
echo "Error deleting record: " . $conn->error;
}
// Close the connection
$conn->close();
?>