DIWE - Working with MySQL Databases

Diploma in Web Engineering
Module X: Working with MySQL
Databases
Rasan Samarasinghe
ESOFT Computer Studies (pvt) Ltd.
No 68/1, Main Street, Pallegama, Embilipitiya.

Contents
1. What is a Database?
2. The Relational Data Model
3. Relationships
4. Normalization
5. Functional Dependency
6. Normal Forms
7. DBMS
8. What is MySQL?
9. PHP Connect to MySQL
10. Create a MySQL Database
11. Connect to Database
12. Close Database
13. Create a MySQL Table
14. Insert Data Into MySQL
15. Get Last ID
16. Insert Multiple Records
17. Prepared Statements
18. Select Data From MySQL
19. Update Data in MySQL
20. Delete Data From MySQL

What is a Database?
Database is a collection of interrelated data items
that can be processed by one or more application
systems.

The Relational Data Model
Data elements are stored in different tables made
up of rows and columns. Relates data in different
tables through the use of common data element(s).

What is a Relation?
Data is presented to the user as tables:
• Tables are comprised of rows and a fixed number of
named columns.
• Columns are attributes describing an entity. Each column
must have an unique name and a data type.

Relationships
• One to One Relationship
• One to Many Relationship
• Many to Many Relationship

Normalization
• In relational database design, the process of
organizing data to minimize redundancy.
• Normalization usually involves dividing a database
into two or more tables and defining relationships
between the tables.

Advantages of Normalization
Reduction of data redundancy within tables:
 Reduce data storage space.
 Reduce inconsistency of data.
 Remove insert, update and delete anomalies.
 Improve flexibility of the system.

Functional Dependency
Normalization is a process based on Functional Dependencies.
Functional Dependency is a constraint between two attributes
or two sets of attributes
The functional dependency of B on A is represented by an
arrow: A → B
e.g.
NID → Name, Address, Birth date
VID → Model, Color
ISBN → Title, Author, Publisher

Normal Forms
 1NF any multi-valued columns have been
removed
 2NF any partial functional dependencies have
been removed
 3NF any transitive dependencies have been
removed

Purchase Order Relation in 0NF

Purchase Order Relation in 1NF

Problems - 1NF
INSERT PROBLEM
Cannot know available parts until an order is placed
(e.g. P4 is bush)
DELETE PROBLEM
Loose information of part P7 if we cancel purchase order
115 (e.g. Delete PO-PART for Part No P7)
UPDATE PROBLEM:
To change description of Part P3 we need to change every
record in PO-PART containing Part No P3

Purchase Order Relations in 2NF

Problems - 2NF
INSERT PROBLEM
Cannot know available suppliers until an order is placed
(e.g. 200 is hardware stores)
DELETE PROBLEM
Loose information of supplier 100 if we cancel purchase
order 116 (e.g. Delete PO for Supplier No 100)
UPDATE PROBLEM
To change name of Supplier 222 we need to change every
record in PO containing Supplier No 222

Purchase Order Relations in 3NF

Database Management Systems (DBMS)
DBMS is a software that enables users to define,
create, maintain and control the access to a
database.

DBMS Functions
A. Data Definition
B. Data Entry
C. Data Manipulation
D. Data Display
E. Data Security
F. Data Integrity
G. Backup and Recovery

What is MySQL?
• MySQL is a database system used on the web
• MySQL is a database system that runs on a server
• MySQL is ideal for both small and large applications
• MySQL is very fast, reliable, and easy to use
• MySQL uses standard SQL
• MySQL compiles on a number of platforms
• MySQL is free to download and use
• MySQL is developed, distributed, and supported by
Oracle Corporation
• MySQL is named after co-founder Monty Widenius's
daughter: My

PHP Connect to MySQL (object oriented)
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = new mysqli($servername, $username,
$password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

PHP Connect to MySQL (procedural)
$conn = mysqli_connect($servername, $username,
$password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

Create a MySQL Database (object oriented)
// Create database
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
echo "Database created successfully";
} else {
echo "Error creating database: " . $conn->error;
}

Create a MySQL Database (procedural)
// Create database
$sql = "CREATE DATABASE myDB";
if (mysqli_query($conn, $sql)) {
echo "Database created successfully";
} else {
echo "Error creating database: " .
mysqli_error($conn);
}

Connect to Database (object oriented)
$dbname = "myDB";
$conn = new mysqli($servername, $username,
$password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

Connect to Database (procedural)
$dbname = "myDB";
$conn = mysqli_connect($servername, $username,
$password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

Close Database
The connection will be closed automatically when
the script ends. To close the connection before, use
the following:
Object oriented:
$conn->close();
Procedural:
mysqli_close($conn);

Create a MySQL Table (object oriented)
// sql to create table
$sql = "CREATE TABLE tblStudent (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
echo "Table tblStudent created successfully";
} else {
echo "Error creating table: " . $conn->error;
}

Create a MySQL Table (procedural)
// sql to create table
$sql = "CREATE TABLE tblStudent (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
echo "Table tblStudent created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}

Insert Data Into MySQL (object oriented)
$sql = "INSERT INTO tblStudent (firstname,
lastname, email)
VALUES ('Hirosh', 'Nuwan', 'hiro@example.com')";
echo "New record inserted successfully";
} else {
echo "Error: " . $sql . " " . $conn->error;
}

Insert Data Into MySQL (procedural)
$sql = "INSERT INTO tblStudent (firstname,
lastname, email)
echo "New record inseted successfully";
} else {
echo "Error: " . $sql . " " .
}

Get ID of The Last Inserted Record (object oriented)
$sql = "INSERT INTO tblStudent (firstname, lastname,
email)
$last_id = $conn->insert_id;
echo "New record created successfully. Last inserted
ID is: " . $last_id;
} else {
}

Get ID of The Last Inserted Record (procedural)
$sql = "INSERT INTO tblStudent (firstname, lastname,
email)
$last_id = mysqli_insert_id($conn);
echo "New record created successfully. Last inserted
ID is: " . $last_id;
} else {
echo "Error: " . $sql . " " . mysqli_error($conn);
}

Insert Multiple Records Into MySQL (object oriented)
$sql = "INSERT INTO MyGuests (firstname, lastname,
email)
VALUES ('Roshan', 'Nuwan', 'rosh@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname,
email)
VALUES ('Ruwan', 'Fernando', 'ruwa@example.com');";
email)
VALUES ('Lahi', 'Hasi', 'lahi@example.com')";
if ($conn->multi_query($sql) === TRUE) {
echo "New records created successfully";
} else {
}

Insert Multiple Records Into MySQL (procedural)
$sql = "INSERT INTO MyGuests (firstname, lastname,
email)
VALUES ('Roshan', 'Nuwan', 'rosh@example.com');";
email)
VALUES ('Ruwan', 'Fernando', 'ruwa@example.com');";
email)
VALUES ('Lahi', 'Hasi', 'lahi@example.com')";
if (mysqli_multi_query($conn, $sql)) {
} else {
echo "Error: " . $sql . " " . mysqli_error($conn);
}

Prepared Statements
• Prepared Statements reduces parsing time as the
preparation on the query is done only once.
• Bound parameters minimize bandwidth to the
server as you need send only the parameters each
time, and not the whole query.
• Prepared statements are very useful against SQL
injections.

Prepared Statements
// prepare and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname,
lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
// set parameters and execute
$firstname = "Janaka";
$lastname = "Sameera";
$email = "janaka@example.com";
$stmt->execute();
$firstname = "Mahura";
$lastname = "Gamlath";
$email = "madhu@example.com";
$stmt->execute();
$stmt->close();

Prepared Statements
"INSERT INTO MyGuests (firstname, lastname,
email) VALUES (?, ?, ?)"
question mark (?) is where we want to substitute in
an integer, string, double or blob value.

Prepared Statements
$stmt->bind_param("sss", $firstname, $lastname,
$email);
binds the parameters to the SQL query and tells the
database what the parameters are.
First argument lists the types of data that the
parameters are.
i - integer
d - double
s - string
b - BLOB

Select Data From MySQL (object oriented)
$sql = "SELECT id, firstname, lastname FROM tblStudent";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " .
$row["firstname"]. " " . $row["lastname"]. " ";
}
} else {
echo "0 results";
}

Select Data From MySQL (procedural)
$sql = "SELECT id, firstname, lastname FROM tblStudent";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " .
$row["firstname"]. " " . $row["lastname"]. " ";
}
} else {
echo "0 results";
}

Update Data in MySQL (object oriented)
$sql = "UPDATE tblStudent SET lastname='Perera'
WHERE id=2";
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}

Update Data in MySQL (procedural)
$sql = "UPDATE tblStudent SET lastname='Perera'
WHERE id=2";
echo "Record updated successfully";
} else {
echo "Error updating record: " .
}

Delete Data From MySQL (object oriented)
$sql = "DELETE FROM tblStudent WHERE id=3";
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}

Delete Data From MySQL (procedural)
$sql = "DELETE FROM tblStudent WHERE id=3";
echo "Record deleted successfully";
} else {
echo "Error deleting record: " .
}

The End
http://twitter.com/rasansmn

DIWE - Working with MySQL Databases

More Related Content

What's hot

Viewers also liked

Similar to DIWE - Working with MySQL Databases

More from Rasan Samarasinghe

Recently uploaded

DIWE - Working with MySQL Databases

Editor's Notes