Shree Siddheshwar Women’s College of Engineering, Solapur

Unit 5
Open Source Database and Application
Content of Unit 5 :
✓ MySQL: Configuring MySQL Server
✓ working with MySQL Databases
✓ MySQL Tables
✓ SQL Commands INSERT, SELECT, UPDATE, REPLACE, DELETE. Date and Time
functions in MySQL
✓ PHP MySQL Application Development: Connecting to MySQL with PHP
✓ Inserting data with PHP, Retrieving data with PHP.

MySQL: Configuring MySQL Server :

➢ MySQL is a popular open-source Relational Database Management System (RDBMS) that

uses SQL (Structured Query Language) for database operations.

➢ While MySQL is a specific database system accessible for free and supports various
programming languages.

What is MySQL?
➢ MySQL is an open-source relational database management system (RDBMS) developed by
Oracle Corporation.
➢ It uses Structured Query Language (SQL) for database management and is known for its
reliability, speed and ease of use.
➢ MySQL supports large databases, up to 50 million rows or more in a table.
➢ It is known for its reliability, ease of use, and performance.
➢ MySQL is widely used for various applications, from small websites to large-scale enterprise
systems.
Key Features of MySQL

Open Source Technology (OST) TY (E&TC) Page 1

 Shree Siddheshwar Women’s College of Engineering, Solapur

1) Open Source: Free to use, with a strong community and enterprise versions
available.
2) Platform Independent: Runs on various platforms such as Windows, Linux, and
macOS.
3) High Performance: Capable of handling large databases efficiently.
4) Scalability: Can be scaled from small projects to enterprise-level applications.
5) Security: Provides strong data protection with user-level security features.
6) Replication and Clustering: Supports master-slave replication for redundancy and
scalability.
Basic MySQL Terminologies
✓ Database: A structured collection of data.
✓ Table: A collection of related data entries consisting of rows and columns.
✓ Row/Record: A single, structured data item in a table.
✓ Column/Field: A vertical entity in a table that contains specific information about all
records.
✓ Primary Key: A unique identifier for records in a table.
✓ Foreign Key: A column used to link two tables.
Introduction to MySQL Server Configuration
➢ Configuring MySQL Server properly ensures optimal performance, security, and reliability.
➢ This process involves installing the server, modifying its configuration files, setting up user
authentication, and fine-tuning settings based on specific use cases.
Installation of MySQL Server
1. For Windows:
• Download MySQL Installer: Go to the MySQL official website
(https://dev.mysql.com/downloads/installer/ ) and download the MySQL Installer.
• Run the Installer: Double-click the installer file and choose the setup type (e.g.,
Developer Default or Custom).
• Install MySQL Server: Follow the prompts to install MySQL Server, along with
optional components like Workbench.
2. For Linux (Ubuntu/Debian)
a) Update the Package List :

Open Source Technology (OST) TY (E&TC) Page 2

 Shree Siddheshwar Women’s College of Engineering, Solapur

sudo apt update

b) Install MySQL Server
sudo apt install mysql-server
c) Start MySQL Service
sudo systemctl start mysql
Securing the Installation
• Run mysql_secure_installation to enhance security
Key Steps in mysql_secure_installation:
✓ Set a strong root password
✓ Remove anonymous users
✓ Disallow root login remotely
✓ Remove test databases
✓ Reload privilege tables for changes to take effect.
Key Steps in mysql_secure_installation:
✓ Set up the VALIDATE PASSWORD plugin: Choose the level of password
validation (low, medium, high) as per your requirements.
✓ Set a root password: Choose and confirm a secure password for the MySQL root
user.
✓ Remove anonymous users: It is recommended to remove them to secure access.
✓ Disallow root login remotely: This increases security by allowing root access
only from localhost.
✓ Remove test database and access to it: This prevents access to a database used
for testing.
✓ Reload privilege tables: Select Y to apply the changes
Accessing the MySQL Command-Line Client
• Log in to the MySQL server as the root user using below command
sudo mysql -u root –p
Enter the root password when prompted.
Configuring the MySQL Server
• Locate the MySQL configuration file:
• Linux: /etc/mysql/my.cnf or /etc/my.cnf

Open Source Technology (OST) TY (E&TC) Page 3

 Shree Siddheshwar Women’s College of Engineering, Solapur

• Open the configuration file with a text editor

sudo nano /etc/mysql/my.cnf

Common Configuration Options

✓ Server Port : port = 3306
✓ Bind Address: Controls which IPs the server listens on. For local access only
bind-address = 127.0.0.1
✓ Data Directory : datadir = /var/lib/mysql
✓ Maximum Connections : max_connections = 150
✓ Buffer Pool Size (for InnoDB storage engine) : innodb_buffer_pool_size = 1G
Restarting the MySQL Service
✓ Apply changes made in the configuration file by restarting the MySQL service
sudo systemctl restart mysql
Creating and Managing MySQL User Accounts
• Create a New User:
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password’;
• Grant Privileges to a User
GRANT ALL PRIVILEGES ON database_name.* TO 'new_user'@'localhost’;
• Apply Changes:
FLUSH PRIVILEGES;
Testing the Configuration
Log in using the new user account to ensure that it has the correct permissions
mysql -u new_user -p

MySQL Tables
Introduction to Tables in MySQL
➢ A table in MySQL is a collection of related data, organized in rows and columns.
➢ Tables are used to store data in a structured format, facilitating easy data manipulation and
retrieval.
Understanding Table Structure
✓ Columns: Represent the attributes or fields of the data (e.g., id, name, email).

Open Source Technology (OST) TY (E&TC) Page 4

 Shree Siddheshwar Women’s College of Engineering, Solapur

✓ Rows: Represent individual records or data entries in the table.

✓ Data Types: Columns must be defined with appropriate data types (e.g., INT,
VARCHAR, DATE).
Syntax for Creating a Table
CREATE TABLE table_name (
column1 datatype [constraints],
column2 datatype [constraints],
column3 datatype [constraints], ...
);
• table_name: The name you choose for your table.
• datatype: The type of data the column will hold (e.g., INT, VARCHAR,
DATE).
• Constraints: Optional conditions such as NOT NULL, PRIMARY KEY,
UNIQUE, etc.
Example of Creating a Table
CREATE TABLE students (
student_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50),
birth_date DATE,
grade_level INT DEFAULT 1
);
Different Parameter in table
• student_id: Integer, serves as the primary key and auto-increments with each new
record.
• first_name: Variable character field up to 50 characters, cannot be null.
• last_name: Optional variable character field up to 50 characters.
• birth_date: Stores date values.
• grade_level: Integer with a default value of 1.
Column Constraints
• NOT NULL: Ensures the column cannot have NULL values.

Open Source Technology (OST) TY (E&TC) Page 5

 Shree Siddheshwar Women’s College of Engineering, Solapur

• UNIQUE: Ensures all values in the column are unique.

• PRIMARY KEY: Uniquely identifies each row; cannot be null.
• FOREIGN KEY: Links one table to another, establishing relationships.
• AUTO_INCREMENT: Automatically increments the column value with each new
entry.
• DEFAULT: Sets a default value for the column if none is provided.
Creating Tables with Constraints
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
amount DECIMAL(10, 2) CHECK (amount > 0),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
• CHECK: Ensures that the column value satisfies the condition (e.g., amount > 0).
• FOREIGN KEY: Establishes a relationship with the customers table.
Modifying Tables
• Add a Column
ALTER TABLE table_name ADD column_name datatype [constraints];
• Drop a Column
ALTER TABLE table_name DROP COLUMN column_name
• Modify a Column
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
• Deleting a Table
DROP TABLE table_name;

SQL Commands: INSERT, SELECT, UPDATE, REPLACE, DELETE :

Introduction to SQL Commands
➢ SQL (Structured Query Language) is used for managing and manipulating data in
relational databases.

Open Source Technology (OST) TY (E&TC) Page 6

 Shree Siddheshwar Women’s College of Engineering, Solapur

➢ Common SQL commands include INSERT, SELECT, UPDATE, REPLACE, and

DELETE

1) INSERT Command
✓ INSERT statement in the SQL is used to add the new records or rows of data into the
table.
✓ It allows you to specify the table name, columns where the data will be inserted and the
corresponding values to be inserted into the columns.
✓ Syntax for INSERT Statement

Explanation:
✓ table_name is the name of the table, which we want to insert the data.
✓ (column1, column2, ....) are optional lists of the columns in table. If specified, you
need to be provide the values for those columns in the same order.
✓ VALUES (value1, value2, ...) are values to be inserted into specified columns. These
values must match the data types of the provided columns.
Example: Adding Data to the students Table
✓ Let' insert some data into the students table.

Output

Open Source Technology (OST) TY (E&TC) Page 7

 Shree Siddheshwar Women’s College of Engineering, Solapur

2) UPDATE Statement
✓ UPDATE statement in the SQL is used to the modify the existing records in table.
✓ It is allowed us to specify the table name, columns to updated new values for these
columns and an optional conditional to the filter which rows are to be updated.
Syntax for UPDATE Statement

Explanation:
✓ table_name is the name of the table, which we want to update.
✓ SET column1 = value1, column2 = value2, ... are specified the columns to the updated
with their new values.
✓ WHERE condition is specify which rows are to be update. if we not use where
condition all rows in the table will be update.
Examples on UPDATE Statement
• Example: Let's update the last name of the students as Anderson whose student_id is 3.

Output

Open Source Technology (OST) TY (E&TC) Page 8

 Shree Siddheshwar Women’s College of Engineering, Solapur

3) DELETE Statement
✓ DELETE statement in the SQL is used to remove the one or more records from the
table.
✓ It is allowed you to the specify the table name and optional condition to the filter
which rows are to be deleted.
Syntax for DELETE Statement

• table_name is the name of the table, which we want to delete the rows of the table.
• WHERE condition is specify which rows are to be delete. if we not use where
condition all rows in the table will be delete.
✓ Examples on DELETE Statement

✓ Let's delete the student from the table whose student_id is 4.

Output

Open Source Technology (OST) TY (E&TC) Page 9

 Shree Siddheshwar Women’s College of Engineering, Solapur

4) SELECT Statement
✓ SELECT statement in the SQL is used to the retrieve the data from one or more
tables.
✓ It is allowed you to the specify the columns you want to be retrieve, table from the
which you want to be retrieve the data and optional condition to filter rows retrieved.
Syntax for SELECT Statement

Explanation:
✓ column1, column2, ... are the columns you want to retrieve the data.
✓ table_name is the name of the table, which we want to retrieve the data of the
table.
✓ WHERE condition is specify which rows are to be retrieve. if we not use
where condition all rows in the table will be retrieve.
Example on SELECT statements

Open Source Technology (OST) TY (E&TC) Page 10

 Shree Siddheshwar Women’s College of Engineering, Solapur

Explanation:

✓ SELECT student_id and first_name specified that the columns you want to be
retrieve the data from the students table.
✓ FROM students specified that the name of the table from the which want to be
retrieve the data.
Output

5) REPLACE
✓ Replaces an existing record or inserts a new one if the record does not exist. Primarily
used when you need to insert data but update it if it already exists.
Syntax:

Example

REPLACE deletes the existing row before inserting the new one if a duplicate key is
found, which can trigger DELETE and INSERT operations internally.

Date and Time Functions in MySQL

Open Source Technology (OST) TY (E&TC) Page 11

 Shree Siddheshwar Women’s College of Engineering, Solapur

Introduction to Functions
✓ In MySQL, functions play a crucial role in performing various operations on data, such
as calculations, string manipulations, and date handling.
✓ These built-in functions simplify complex queries and data transformations, making it
easier to manage and analyze data within a database.
Functions in MySQL
✓ In MySQL, functions are a fundamental part of the SQL language, enabling us to
perform calculations, manipulate data and retrieve information.
✓ The functions in MySQL can edit rows and tables, alter strings, and help us to manage
organized and easy-to-navigate databases.
✓ A function is a special type of predefined command set that performs some operation
and returns a value. Functions operate on zero, one, two, or more values that are
provided to them.
✓ The values that are provided to functions are called parameters or arguments.
✓ The MySQL functions have been categorized into various categories, such as String
functions, Mathematical functions, Date and Time functions, etc.
• String Functions
• Numeric Functions
• Date and Time Functions
Introduction to Date and Time Functions

✓ MySQL provides a rich set of built-in functions to work with date and time data types.
✓ These functions help in performing operations such as extracting parts of dates, formatting
dates, calculating time differences, and modifying date values.
Commonly Used MySQL Date and Time Data Types
• DATE: Stores dates in the format YYYY-MM-DD.
• TIME: Stores time in the format HH:MM:SS.
• DATETIME: Stores both date and time in the format YYYY-MM-DD HH:MM:SS.
• TIMESTAMP: Stores date and time values, often used for recording the time of data
modifications.
• YEAR: Stores year values in YYYY format.

Open Source Technology (OST) TY (E&TC) Page 12

 Shree Siddheshwar Women’s College of Engineering, Solapur

Current Date and Time Functions

a) NOW( ): Returns the current date and time
SELECT NOW();
Output : Display current date & Time
b) CURDATE(): Returns the current date
SELECT CURDATE();
c) CURTIME(): Returns the current time
SELECT CURTIME();

Date & Time function in SQL

Functions Description

ADDDATE() MySQL ADDDATE() adds a time value with a date.

ADDTIME() In MySQL the ADDTIME() returns a time or datetime after

adding a time value with a time or datetime.

CONVERT_TZ() In MySQL the CONVERT_TZ() returns a resulting value after

converting a datetime value from a time zone specified as the

Open Source Technology (OST) TY (E&TC) Page 13

 Shree Siddheshwar Women’s College of Engineering, Solapur

second argument to the time zone specified as the third

argument.

CURDATE() In MySQL the CURDATE() returns the current date in 'YYYY-

MM-DD' format or 'YYYYMMDD' format depending on
whether numeric or string is used in the function.

CURRENT_DATE() In MySQL the CURRENT_DATE returns the current date in

‘YYYY-MM-DD’ format or YYYYMMDD format depending
on whether numeric or string is used in the function.

CURRENT_TIME() In MySQL the CURRENT_TIME() returns the current time in

‘HH:MM:SS’ format or HHMMSS format depending on
whether numeric or string is used in the function.

CURRENT_TIMESTAMP() In MySQL the CURRENT_TIEMSTAMP returns the current

date and time in ‘YYYY-MM-DD HH:MM:SS’ format or
YYYYMMDDHHMMSS.format depending on whether
numeric or string is used in the function.

CURTIME() In MySQL the CURTIME() returns the value of current time in

‘HH:MM:SS’ format or HHMMSS format depending on
whether numeric or string is used in the function.

DATE_ADD() MySQL DATE_ADD() adds time values (as intervals) to a date

value. The ADDDATE() is the synonym of DATE_ADD().

Open Source Technology (OST) TY (E&TC) Page 14

 Shree Siddheshwar Women’s College of Engineering, Solapur

DATE_FORMAT() MySQL DATE_FORMAT() formats a date as specified in the

argument. A list of format specifiers given bellow may be used
to format a date.

DATE_SUB() MySql DATE_SUB() function subtract a time value (as

interval) from a date.

DATE() MySQL DATE() takes the date part out from a datetime
expression.

DATEDIFF() MySQL DATEDIFF() returns the number of days between two

dates or datetimes.

DAY() MySQL DAY() returns the day of the month for a specified date.

DAYNAME() MySQL DAYNAME() returns the name of the week day of a

date specified in the argument.

DAY OF MONTH() MySQL DAYOFMONTH() returns the day of the month for a
given date.

DAY OF WEEK() MySQL DAYOFWEEK() returns the week day number (1 for
Sunday,2 for Monday …… 7 for Saturday ) for a date specified
as an argument.

Open Source Technology (OST) TY (E&TC) Page 15

 Shree Siddheshwar Women’s College of Engineering, Solapur

DAY OF YEAR() MySQL DAYOFYEAR() returns day of the year for a date. The
return value is within the range of 1 to 366.

EXTRACT() MySQL EXTRACT() extracts a part of a given date.

FROM_DAYS() MySQL FROM_DAYS() returns a date against a datevalue.

FROM_UNIXTIME() MySQL FROM_UNIXTIME() returns a date /datetime from a

version of unix_timestamp.

GET_FORMAT() MySQL GET_FORMAT() converts a date or time or datetime in

a formatted manner as specified in the argument.

HOUR() MySQL HOUR() returns the hour of a time.

LAST_DAY() MySQL LAST_DAY() returns the last day of the corresponding

month for a date or datetime value.

LOCALTIME() MySQL LOCALTIME returns the value of current date and time
in ‘YYYY-MM-DD HH:MM:SS’ format or
YYYYMMDDHHMMSS.uuuuuu format depending on the
context (numeric or string) of the function.

LOCALTIMESTAMP() MySQL LOCALTIMESTAMP returns the value of current date

and time in ‘YYYY-MM-DD HH:MM:SS’ format or

Open Source Technology (OST) TY (E&TC) Page 16

 Shree Siddheshwar Women’s College of Engineering, Solapur

YYYMMDDHHMMSS.uuuuuu format depending on the

context (numeric or string) of the function.

MAKEDATE() MySQL MAKEDATE() returns a date by taking a value of a

year and a number of days. The number of days must be greater
than 0 otherwise a NULL will be returned.

MAKETIME() MySQL MAKETIME() makes and returns a time value from a

given hour, minute and seconds.

MICROSECOND() MySQL MICROSECOND() returns microseconds from the time

or datetime expression.

MINUTE() MySQL MINUTE() returns a minute from a time or datetime

value.

MONTH() MySQL MONTH() returns the month for the date within a range
of 1 to 12 ( January to December).

MONTHNAME() MySQL MONTHNAME() returns the full name of the month

for a given date.

NOW() MySQL NOW() returns the value of current date and time in
‘YYYY-MM-DD HH:MM:SS’ format or
YYYYMMDDHHMMSS.uuuuuu format depending on the
context (numeric or string) of the function.

Open Source Technology (OST) TY (E&TC) Page 17

 Shree Siddheshwar Women’s College of Engineering, Solapur

PERIOD_ADD() MySQL PERIOD_ADD() adds a number of months with a

period and returns the value in the format YYYYMM OR
YYMM. Remember that the format YYYYMM and YYMM are
not date values.

PERIOD_DIFF() MySQL PERIOD_DIFF() returns the difference between two

periods.

QUARTER() MySQL QUARTER() returns the quarter of the year for a date.

SEC_TO_TIME() MySQL SEC_TO_TIME() returns a time value by converting

the seconds specified in the argument.

SECOND() MySQL SECOND() returns the second for a time.

STR_TO_DATE() MySQL STR_TO_DATE() returns a datetime value by taking a

string and a specific format string as arguments.

SUBDATE() MySQL SUBDATAE() subtracts a time value (as interval) from

a given date.

SUBTIME() MySQL SUBTIME() subtracts one datetime value from another.

SYSDATE() MySQL SYSDATE() returns the current date and time in

YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS
format depending on the context of the function.

Open Source Technology (OST) TY (E&TC) Page 18

 Shree Siddheshwar Women’s College of Engineering, Solapur

TIME_FORMAT() MySQL TIME_FORMAT() converts a time in a formatted string

using the format specifiers.

TIME_TO_SEC() MySQL TIME_TO_SEC() converts a time value in to seconds.

TIME() MySQL TIME() extracts the time part of a time or datetime

expression as string format.

TIMEDIFF() MySQL TIMEDIFF() returns the differences between two time

or datetime expressions.

TIMESTAMP() MySQL TIMESTAMP() returns a datetime value against a date

or datetime expression.

TIMESTAMPADD() MySQL TIMESTAMPADD() adds time value with a date or

datetime value.

TIMESTAMPDIFF() MySQL the TIMESTAMPDIFF() returns a value after

subtracting a datetime expression from another.

TO_DAYS() MySQL TO_DAYS() returns number of days between a given

date and year 0.

UNIX_TIMESTAMP() MySQL UNIX_TIMESTAMP() returns a Unix timestamp in

seconds since '1970-01-01 00:00:00' UTC as an unsigned integer
if no arguments are passed with UNIT_TIMESTAMP().

Open Source Technology (OST) TY (E&TC) Page 19

 Shree Siddheshwar Women’s College of Engineering, Solapur

UTC_DATE() MySQL UTC_DATE returns the current UTC (Coordinated

Universal Time) date as a value in 'YYYY-MM-DD' or
YYYYMMDD format depending on the context of the function
i.e. in a string or numeric context.

UTC_TIME() MySQL UTC_TIME returns the current UTC time as a value in

'HH:MM:SS' or HHMMSS format depending on the context of
the function i.e. in a string or numeric context.

UTC_TIMESTAMP() In MySQL the UTC_TIMESTAMP returns the current UTC

date and time as a value in 'YYYY-MM-DD HH:MM:SS' or
YYYYMMDDHHMMSS.uuuuuu format depending on the
usage of the function i.e. in a string or numeric context.

WEEK() MySQL WEEK() returns the week number for a given date.

WEEKDAY() MySQL WEEKDAY() returns the index of the day in a week for
a given date (0 for Monday, 1 for Tuesday and ......6 for Sunday).

WEEK OF YEAR() MySQL WEEKOFYEAR() returns the calender week (as a

number) of a given date.

YEAR() MySQL YEAR() returns the year for a given date.

YEARWEEK() MySQL YEARWEEK() returns year and week number for a

given date.

Open Source Technology (OST) TY (E&TC) Page 20

 Shree Siddheshwar Women’s College of Engineering, Solapur

PHP MySQL Application Development:

1) Connecting to MySQL with PHP :-

➢ PHP is a powerful server-side scripting language used for web development.
➢ One of its common uses is to connect to a MySQL database to perform operations like
inserting, retrieving, updating, and deleting data.
➢ PHP provides various ways to connect to MySQL, mainly using:
a) MySQLi (MySQL Improved)
b) PDO (PHP Data Objects)
➢ Before connecting PHP to MySQL, ensure that:
• You have XAMPP, WAMP, or LAMP installed (for local development).
• MySQL server is running.
• A database is already created (or will be created in PHP).
• The required PHP extensions (mysqli or pdo_mysql) are enabled.
1. Connecting to MySQL Using MySQLi
➢ MySQLi (MySQL Improved) is a PHP extension specifically designed for MySQL
databases. It supports both procedural and object-oriented approaches.
a) Procedural Method :
<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "test_db";

// Create connection
$conn = mysqli_connect($server, $username, $password, $database);

// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
Open Source Technology (OST) TY (E&TC) Page 21
 Shree Siddheshwar Women’s College of Engineering, Solapur

}
echo "Connected successfully";
?>
Here point to be noted is
▪ mysqli_connect() establishes a connection to MySQL.
• die() terminates the script if the connection fails.
• mysqli_connect_error() provides error details.

b) Object-Oriented Method
<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "test_db";

// Create connection
$conn = new mysqli($server, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
Point to be remember is

• $conn = new mysqli(...) --------creates a new MySQLi object.

• $conn->connect_error -----------checks if the connection failed.

Open Source Technology (OST) TY (E&TC) Page 22

 Shree Siddheshwar Women’s College of Engineering, Solapur

2. Connecting to MySQL Using PDO

➢ PDO (PHP Data Objects) is a database abstraction layer that supports multiple databases
(MySQL, PostgreSQL, SQLite, etc.).
a) PDO Connection Example :
<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "test_db";

try {
$conn = new PDO("mysql:host=$server;dbname=$database",
$username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
Point to be remember is
▪ new PDO(...)------- creates a new database connection.
• setAttribute() ---------is used to enable error handling.
• The try-catch block handles exceptions -------(PDOException).

Closing MySQLi Connection

a) Procedural Approach:
mysqli_close($conn);
b) Object-Oriented Approach:
$conn->close();

Open Source Technology (OST) TY (E&TC) Page 23

 Shree Siddheshwar Women’s College of Engineering, Solapur

Closing PDO Connection

$conn = null;

2) Inserting Data with PHP into MySQL

➢ Inserting data into a MySQL database is an essential operation in PHP web applications.
PHP provides multiple methods to insert data into a MySQL table, primarily using:
a) MySQLi (MySQL Improved)
b) PDO (PHP Data Objects)
Before inserting data, ensure:
• PHP and MySQL are installed (e.g., via XAMPP, WAMP, or LAMP).
• A database and table are created in MySQL.
• A PHP script is set up to handle the database connection.
Creating a Sample Database and Table
Use the following SQL queries to create a database and table:
CREATE DATABASE test_db;
USE test_db;

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
age INT NOT NULL
);
Now, insert this data using PHP.

a) Inserting Data Using MySQLi

1) Procedural Method

<?php
$server = "localhost";
$username = "root";

Open Source Technology (OST) TY (E&TC) Page 24

 Shree Siddheshwar Women’s College of Engineering, Solapur

$password = "";
$database = "test_db";

// Create connection
$conn = mysqli_connect($server, $username, $password, $database);

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

// SQL Insert Query

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

if (mysqli_query($conn, $sql)) {
echo "New record inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>

Here the meaning of different terms are ,

• mysqli_connect() ----------establishes a connection to MySQL.
• mysqli_query($conn, $sql) -------executes the INSERT INTO statement.
• mysqli_error($conn)-------- prints an error if the query fails.
• mysqli_close($conn)--------, different terms used closes the connection.

Open Source Technology (OST) TY (E&TC) Page 25

 Shree Siddheshwar Women’s College of Engineering, Solapur

2) Object-Oriented Method

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "test_db";

// Create connection
$conn = new mysqli($server, $username, $password, $database);

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

// SQL Insert Query

$sql = "INSERT INTO users (name, email, age) VALUES ('Alice Smith',
'alice@example.com', 30)";

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

echo "New record inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}

// Close connection
$conn->close();
?>

Open Source Technology (OST) TY (E&TC) Page 26

 Shree Siddheshwar Women’s College of Engineering, Solapur

Here the meaning of different terms are ,

• $conn = new mysqli(...)--------- creates a connection object.
• $conn->query($sql)---------------- executes the INSERT INTO statement.
• $conn->error ------------------------is used for error handling.
• $conn->close() ----------------------closes the connection.

3) Inserting Data Using MySQLi Prepared Statements

➢ Prepared statements prevent SQL injection attacks and improve security.

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "test_db";

// Create connection
$conn = new mysqli($server, $username, $password, $database);

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

// Prepare statement
$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (?,
?, ?)");
$stmt->bind_param("ssi", $name, $email, $age);

// Set values and execute

$name = "Mark Lee";
$email = "mark@example.com";

Open Source Technology (OST) TY (E&TC) Page 27

 Shree Siddheshwar Women’s College of Engineering, Solapur

$age = 28;
$stmt->execute();

echo "New record inserted successfully";

// Close statement and connection

$stmt->close();
$conn->close();
?>
Here the meaning of different terms are ,
• prepare()-------- creates a prepared statement.
• bind_param("ssi", $name, $email, $age) -------binds parameters (s for string, i for
integer).
• execute()------------ runs the statement.
• close()------------ frees resources.

4) Inserting Data Using PDO

a) Using Regular Query Execution

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "test_db";

try {
$conn = new PDO("mysql:host=$server;dbname=$database", $username,
$password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// SQL Insert Query

Open Source Technology (OST) TY (E&TC) Page 28

 Shree Siddheshwar Women’s College of Engineering, Solapur

$sql = "INSERT INTO users (name, email, age) VALUES ('Jane Doe',
'jane@example.com', 22)";
$conn->exec($sql);

echo "New record inserted successfully";

} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}

// Close connection
$conn = null;
?>

Here the meaning of different terms are ,

• new PDO(...) creates a PDO connection.
• setAttribute() sets error handling mode.
• exec() executes the INSERT query.
• catch(PDOException $e) handles exceptions.

b) Using Prepared Statements in PDO

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "test_db";

try {
$conn = new PDO("mysql:host=$server;dbname=$database", $username,
$password);

Open Source Technology (OST) TY (E&TC) Page 29

 Shree Siddheshwar Women’s College of Engineering, Solapur

$conn->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);

// Prepare and bind

$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES
(:name, :email, :age)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':age', $age);

// Insert values
$name = "Sophia Brown";
$email = "sophia@example.com";
$age = 26;
$stmt->execute();

echo "New record inserted successfully";

} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}

// Close connection
$conn = null;
?>
Here the meaning of different terms are ,
• prepare() --------------prepares the SQL statement.
• bindParam()----------- binds values safely.
• execute() ---------------runs the statement.

Open Source Technology (OST) TY (E&TC) Page 30

 Shree Siddheshwar Women’s College of Engineering, Solapur

Retrieving Data with PHP from MySQL

➢ Retrieving data from a MySQL database is a fundamental task in web development. PHP
provides multiple ways to fetch data from a MySQL database using:
A) MySQLi (MySQL Improved)
B) PDO (PHP Data Objects)
➢ Before retrieving data, ensure:
✓ PHP and MySQL are installed (e.g., via XAMPP, WAMP, or LAMP).
✓ A database and table exist in MySQL.
✓ A PHP script is set up to handle the database connection.
a) Creating a Sample Database and Table
Use the following SQL queries to create a database and insert sample data

CREATE DATABASE test_db;

USE test_db;

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
age INT NOT NULL
);

INSERT INTO users (name, email, age) VALUES

('John Doe', 'john@example.com', 25),
('Alice Smith', 'alice@example.com', 30),
('Mark Lee', 'mark@example.com', 28);

1) Retrieving Data Using MySQLi

a) Procedural Method
<?php
$server = "localhost";

Open Source Technology (OST) TY (E&TC) Page 31

 Shree Siddheshwar Women’s College of Engineering, Solapur

$username = "root";
$password = "";
$database = "test_db";

// Create connection
$conn = mysqli_connect($server, $username, $password, $database);

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

// SQL Query to fetch data

$sql = "SELECT id, name, email, age FROM users";
$result = mysqli_query($conn, $sql);

// Check if records exist

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

// Close connection
mysqli_close($conn);
?>
Here meaning of different terms are as below
• mysqli_query($conn, $sql) ------executes the SELECT query.

Open Source Technology (OST) TY (E&TC) Page 32

 Shree Siddheshwar Women’s College of Engineering, Solapur

• mysqli_num_rows($result) ---------checks if records exist.

• mysqli_fetch_assoc($result)---------- fetches each row as an associative array.
• mysqli_close($conn) -------------------closes the connection.

b) Object-Oriented Method

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "test_db";

// Create connection
$conn = new mysqli($server, $username, $password, $database);

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

// SQL Query to fetch data

$sql = "SELECT id, name, email, age FROM users";
$result = $conn->query($sql);

// Check if records exist

if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . " - Name: " . $row["name"] .
" - Email: " . $row["email"] . " - Age: " . $row["age"] . "<br>";
}
} else {

Open Source Technology (OST) TY (E&TC) Page 33

 Shree Siddheshwar Women’s College of Engineering, Solapur

echo "No records found";

}

// Close connection
$conn->close();
?>
Here meaning of different terms are as below
• $conn->query($sql) executes the SELECT query.
• $result->num_rows checks if records exist.
• $result->fetch_assoc() fetches each row as an associative array.

2) Retrieving Data Using MySQLi Prepared Statements

Prepared statements help prevent SQL injection and improve security.

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "test_db";

// Create connection
$conn = new mysqli($server, $username, $password, $database);

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

// Prepare statement
$stmt = $conn->prepare("SELECT id, name, email, age FROM users WHERE
age > ?");

Open Source Technology (OST) TY (E&TC) Page 34

 Shree Siddheshwar Women’s College of Engineering, Solapur

$stmt->bind_param("i", $age);

// Set value and execute

$age = 25;
$stmt->execute();

// Bind result variables

$stmt->bind_result($id, $name, $email, $age);

// Fetch values
while ($stmt->fetch()) {
echo "ID: $id - Name: $name - Email: $email - Age: $age <br>";
}

// Close statement and connection

$stmt->close();
$conn->close();
?>
Here meaning of different terms are as below
• prepare() ----------creates a prepared statement.
• bind_param("i", $age)-------- binds parameters (i for integer).
• execute()------------ runs the query.
• bind_result() -----------binds columns to variables.
• fetch() -------------retrieves values.

3) Retrieving Data Using PDO

a) Using Regular Query Execution

<?php
$server = "localhost";
$username = "root";

Open Source Technology (OST) TY (E&TC) Page 35

 Shree Siddheshwar Women’s College of Engineering, Solapur

$password = "";
$database = "test_db";

try {
$conn = new PDO("mysql:host=$server;dbname=$database", $username,
$password);
$conn->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);

// SQL Query to fetch data

$sql = "SELECT id, name, email, age FROM users";
$result = $conn->query($sql);

// Fetch and display results

foreach ($result as $row) {
echo "ID: " . $row["id"] . " - Name: " . $row["name"] .
" - Email: " . $row["email"] . " - Age: " . $row["age"] . "<br>";
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}

// Close connection
$conn = null;
?>
b) Using Prepared Statements in PDO

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

Open Source Technology (OST) TY (E&TC) Page 36

 Shree Siddheshwar Women’s College of Engineering, Solapur

$database = "test_db";

try {
$conn = new PDO("mysql:host=$server;dbname=$database", $username,
$password);
$conn->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);

// Prepare statement
$stmt = $conn->prepare("SELECT id, name, email, age FROM users
WHERE age > :age");
$stmt->bindParam(':age', $age);

// Set value and execute

$age = 25;
$stmt->execute();

// Fetch results
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "ID: " . $row["id"] . " - Name: " . $row["name"] .
" - Email: " . $row["email"] . " - Age: " . $row["age"] . "<br>";
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}

// Close connection
$conn = null;
?>

Open Source Technology (OST) TY (E&TC) Page 37