503073
WEB PROGRAMMING & APPLICATIONS
PHP - MySQL
1 Instructor: Mai Van Manh
2
OUTLINE
1. Introduction
2. SQL
3. MySQL
4. PHP vs MySQL
November 17, 2021
3
INTRODUCTION
´ Why Use a Database?
´ Structured Query Language
November 17, 2021
4
SQL: STRUCTURED QUERY LANGUAGE
´ SQL is the most popular language for adding, accessing and
managing content in a database.
´ Pronounciation: either S - Q - L (one character at a time) or
‘sequel’.
´ SQL Statements: Most of the actions you need to perform on a
database are done with SQL statements:
SELECT * FROM Customers
´ SQL keywords are NOT case sensitive.
´ Some database systems require a semicolon at the end of each
SQL statement.
November 17, 2021
5
Important SQL Commands
´ SELECT - extracts data from a database
´ UPDATE - updates data in a database
´ DELETE - deletes data from a database
´ INSERT INTO - inserts new data into a database
´ CREATE DATABASE - creates a new database
´ ALTER DATABASE - modifies a database
´ CREATE TABLE - creates a new table
´ ALTER TABLE - modifies a table
´ DROP TABLE - deletes a table
´ CREATE INDEX - creates an index (search key)
´ DROP INDEX - deletes an index
November 17, 2021
6
MYSQL
´ MySQL is a freely available open source RDBMS that uses
Structured Query Language.
´ MySQL is an essential part of almost every open source PHP
application.
´ MySQL is developed, and distributed by Oracle Corporation.
November 17, 2021
7
MYSQL ADVANTAGES
´ MySQL is easy to use, yet extremely powerful, fast, secure,
and scalable.
´ MySQL runs on a wide range of operating systems, including
UNIX or Linux, Microsoft Windows, Apple Mac OS X, and
others.
´ MySQL supports standard SQL (Structured Query Language).
´ MySQL is ideal database solution for both small and large
applications.
´ MySQL includes data security layers that protect sensitive
data from intruders.
November 17, 2021
8
WORKING WITH MYSQL
1. Download & install MySQL
2. Connect to MySQL Database.
3. Interact with a database.
´ Create a database and tables
´ Insert data into tables
´ Load data from tables
´ Update and delete data from tables
4. Close database connection.
November 17, 2021
9
MySQL Server Default Information
1. Hostname: e.g localhost, 127.0.0.1
2. Username: root
3. Password: (empty)
4. Port: 3306
5. Database name: optional
November 17, 2021
10
PHP Connect to Database Server
´PHP offers two different ways to connect to MySQL
server:
1. MySQLi: Improved version of MySQL.
2. PDO: PHP Data Objects extensions.
November 17, 2021
11
PHP Data Object
´ More portable and supports more than 12 different
databases.
´ Only Object-oriented API is supported.
November 17, 2021
12
MySQLi
´ Only supports MySQL database.
´ Provides an easier way to connect to, and execute queries
on, a MySQL database server.
´ MySQLi also offers a procedural API which is relatively easy for
beginners to understand.
´ Faster than PDO.
November 17, 2021
13
Open Connection: MySQLi
´ Connect to server without selecting a database.
November 17, 2021
14
Open Connection: MySQLi
´ Connect to server then select a database.
November 17, 2021
15
Open Connection: MySQLi
´ Connect to server and database at the same time.
November 17, 2021
16
Close Connection
´ The connection will be closed automatically when the script ends.
´ To close the connection earlier, use the following:
November 17, 2021
17
Create Database
´ Before saving or accessing the data, we need to create a
database first.
´ The CREATE DATABASE statement is used to create a new
database in MySQL.
´ The MySQLi query() method is used to execute the create
database statement.
November 17, 2021
18
Create Database
November 17, 2021
19
MySQLi query() method
´ Returns FALSE on failure.
´ Returns a mysqli_result object for successful SELECT, SHOW,
DESCRIBE or EXPLAIN queries.
´ Return TRUE for other successful queries.
November 17, 2021
20
Create Table
´ Before creating a table, we first need to select a valid
database.
´ Selecting a database can be done in 2 ways:
1. Specifying database name when open a connection to server.
2. Use select_db() method.
November 17, 2021
21
Create Table
´ Use $conn->query('sql command') to create a table.
November 17, 2021
22
Insert Data
´ The INSERT INTO statement is used to insert new rows in a database
table.
$sql = "insert into product values(1, 'iPhone 12', 1200)";
´ Multiple records can be inserted at the same time:
$sql = "insert into product values
(1, 'iPhone 12', 1200),
(2, 'iPhone 13', 2300),
(3, 'iPhone 14', 3500)";
November 17, 2021
23
Insert Data
November 17, 2021
24
Insert Data
´ When receiving data from the user, we put them in the insert
statement.
November 17, 2021
25
Insert Data
´ What if the user input is not safe?
November 17, 2021
26
Insert Data using Prepared Statement
´ A prepared statement is simply a SQL query template containing
placeholder instead of the actual parameter values.
´ These placeholders will be replaced by the actual values at the
time of execution of the statement.
$sql = "insert into product values (?,?,?)";
November 17, 2021
27
Insert Data using Prepared Statement
´ The prepared statement execution consists of two stages: prepare
and execute.
1. Prepare — At the prepare stage a SQL statement template is
created and sent to the database server. The server parses the
statement template, performs a syntax check and query
optimization, and stores it for later use.
2. Execute — During execute the parameter values are sent to the
server. The server creates a statement from the statement
template and these values to execute it.
November 17, 2021
28
Insert Data using Prepared Statement
November 17, 2021
29
Load data from a Table
´MySQLi returns a mysqli_result object for successful
SELECT command.
November 17, 2021
30
Load data from a Table
´If there is data, we use fetch_assoc() to read it line
by line.
November 17, 2021
31
Load data from a Table
´call fetch_assoc() repeatedly to read data row by
row:
November 17, 2021
32
Load data from a Table
´Use while loop when we do not know how many row
may be returned.
November 17, 2021
33
Load data from a Table
´Use while loop when we do not know how many row
may be returned.
November 17, 2021
34
Load data with condition
´ The WHERE clause is used to filter records.
November 17, 2021
35
Load data with condition
´ Prepared statement can also be used.
November 17, 2021
36
PHP MySQL Demo
´ Watch a demo video
November 17, 2021
