PHP and MySQL

CMPS 246: Web Programming

Web Programming 1
How did you
register for the
course?

Web Programming 2
You accessed a database!
• When you registered for the course, you accessed the university
database.

• A database is an organized collection of structured information, or

data, typically stored electronically in a computer system.

• What kind of data is stored in a university database?

Web Programming 3
A database is a coherent collection of data
Meaningful data (random assortment of data can’t be referred to as a
database)

• Courses
• Students
• Grades
• Employees

• Patients
• Doctors
• Medical records
• Salaries

Web Programming 4
What is a Database Management System
• Computerized systems that enables users to create and maintain a
database
• General purpose software system DBMS
• DBMS operations on a database
1. Define
2. Construct
3. Manipulate
4. Share DATABASE
5. Protect
6. Maintain

Web Programming 5
Facebook DBMS
• Facebook uses MySQL for the majority of the data you
would consider as “Facebook data” like posts
comments etc..
• It also uses other DBMSs like
• Hbase
• Cassandra

Web Programming 6
Facebook
Datacenters
• Each data center houses
tens of thousands of
computer servers
• massive storage
infrastructure to house
its enormous piles of
data

Web Programming 7
Database ER Model

Web Programming 8
Database Tables

Web Programming 9
Structure Query Language
• Originally was called SEQUEL (Structured English QUEry Language)
• Was designed and implemented at IBM research
• Now the standard language for commercial relational DBMSs
• Has statements for applying the DBMS operations
Data Definition Language (DDL)

Data Manipulation Language (DML)

Data Control Language (DCL)

Transaction Control Language (TCL)
DDL + DML + DCL + TCL

Web Programming 10
SQL Commands Chart

SQL

DDL DML DCL TCL

Define Manipulate Control

• Schema • Query data • User access
• relation • Update data • Permissions
• datatype
• Domain
• views
Web Programming 11
 Creating Table in
• Use wizard to create the Employee relation
• Execute an SQL query to create the department relation

CREATE TABLE DEPARTMENT

(Dname VARCHAR(15) NOT NULL,
Dnumber INT NOT NULL,
Mgr_ssn CHAR(9) NOT NULL,
Mgr_start_date DATE,
PRIMARY KEY (Dnumber),
UNIQUE (Dname));

Web Programming 12
 SQL

DDL DML DCL TCL

Define Schemas,
create roles,
Relations, Domains, Query Data Update Data
permissions, access
Transaction specific
Views

CREATE SELECT

DROP FROM

ALTER WHERE

TRUNCATE ORDER BY

Web Programming 13
Manipulating the Database by Querying Data

Main command is used for data retrieval

SELECT

Formed as a SELECT FROM WHERE Block

Web Programming 14
Querying Data
SELECT projection_attribute(s)
FROM table_name(s)
WHERE selection_join_condition
ORDER BY attribute_name(s)

• SELECT and FROM are mandatory

• SELECT attributes to be retrieved FROM corresponding tables WHERE
a condition is met and ORDER them BY a specific order

Web Programming 15
Query with Selection Condition

SELECT Bdate, Address

FROM EMPLOYEE
WHERE Fname = ‘John’ AND Minit = ‘B’ AND Lname = ‘Smith’;

Web Programming 16
 SQL

DDL DML DCL TCL

Define Schemas,
create roles,
Relations, Domains, Query Data Update Data
permissions, access
Transaction specific
Views

CREATE SELECT INSERT

DROP FROM UPDATE

ALTER WHERE DELETE

TRUNCATE ORDER BY

Web Programming 17
Manipulating the Database by Updating Data

Three commands are used to modify the

database content

INSERT DELETE UPDATE

Web Programming 18
Inserting Data
• Use INSERT to add a single tuple o a relation
• Values should be listed in the order of the attributes
• If no attributes are specified, add values for all relation attributes
• Must insert values for NOT NULL attributes

INSERT INTO RELATION(attributes)

VALUES (‘attributeval’, ‘attributeval’,…);

Web Programming 19
Inserting Data

Web Programming 20
Application Programs

SOFTWARE WEBSITES/WEB MOBILE APPLICATIONS

APPLICATIONS APPLICATIONS

Web Programming 21
 Websites/Web Applications
• Webpages accessed through the internet/intranet
• Client-served based applications
• Developed in scripting language
• HTML
• PHP
• CSS
• Python
• Javascript
• Others

Web Programming 22
Application Environment
Backend

Frontend Internet

A Web server App server

Database
Application

Web Programming 23
Application Environment
Backend as a Service (BaaS)

Frontend Internet

Application

https://youtu.be/iosNuIdQoy8?list=PLl-K7zZEsYLmOF_07IayrTntevxtbUxDL
Web Programming 24
Database Programming Routes

• SQL or NoSQL Database?

• Software, website, web app, or mobile app?
• If mobile app, is it native or cross-platform or …?
• Which programming language to use?
• Using a custom backend or BaaS?

Web Programming 25
Choosing the TechStack
• a set of tools that are used to construct and power an application
• Combination of software applications, and programming languages

Web Programming 26
Find Techstacks

https://techstacks.io/stacks/

Web Programming 27
Database Programming Languages

• Host language: general

purpose programming
language (PHP, python,
Java,…)

• Data sublanguage:
database language (SQL)

Web Programming 28
Approaches to Database Programming
• Embedded SQL: Embedding database commands in a host language

• API: Using a library of database functions or classes (JDBC/ODBC)

• Used by host programming language for database calls
• Functions to
• Connect to database
• Prepare a query
• Execute a query
• Commands are included as function calls

Web Programming 29
Impedance Mismatch
• Problems that occur because of differences between the
database model and the programming language model
• Different datatypes (binding datatypes)
• Accessing individual valued from multiset results of queries
(biniding data structures)

Web Programming 30
Sequence of Interaction in DB Programming
1. Establish or open a connection to DB server
• URL of DB server machine
• Login account name and password
2. Interact with DB by submitting queries
• Use SQL statements
• Apply appropriate binding to avoid impedance mismatch
3. Terminate or close DB connection

Web Programming 31
PHP and MySQL
• MySQL is the most popular database system used with PHP.
• PHP 5 and later can work with a MySQL database using:
• MySQLi extension (the "i" stands for improved)
• PDO (PHP Data Objects)

Web Programming 32
Open a Connection to MySQL

Web Programming 33
Create the Database

Web Programming 34
Insert Values

Web Programming 35
Facebook
Datacenters
• Each data center houses
tens of thousands of
computer servers
• massive storage
infrastructure to house
its enormous piles of
data

Web Programming 36
Similar Organizations

• Google
• Amazon
• Twitter
• Instagram
• LinkedIn

Database Systems 37
Big Data Applications
• Social media
• Web links
• User profiles
• Marketing and sales
• Posts and tweets
• Roadmaps
• Spatial data
• Email

Database Systems 38
No Relational model

Database Systems 39
DynamoDB by Amazon in 2004
• Amazon was growing rapidly and was starting to hit the upper scaling
limits of its Oracle database
• Engineers created the Amazon Dynamo database which backed major
internal infrastructure

Database Systems 40
NoSQL Databases
• Not only SQL
• Needed to augment data management needs
• Distributed databases and storage systems

Database Systems 41
SQL NoSQL

• Data consistency • Data replication

• Powerful query language • High performance
• Structured data storage • Semi-structure data storage
• Availability
• scalability

Database Systems 42
Popular NoSQL Systems
• Google
• BigTable
• Firebase Realtime Database
• Firestore
• Amazon
• DynamoDB

• Facebook
• Cassandra

• DoubleClick (Now owned by Google)

• MongoDB
Database Systems 43
NoSQL Systems Data Characteristics

Database Systems 44
NoSQL Systems Model Characteristics
• No schema required
• Semi-structured self describing data
• Constraints are programmed within the applications
• Less powerful query language
• Provide functions as API
• CRUD operations (Create, Read, Update, Delete)
• Join need to be implemented in the application program
• Versioning
• Storage of multiple versions of all data items
• Time-stamped data

Database Systems 45
 NoSQL Database (JSON Object)
Collection
Table/relation

Document ID
primaryKey

Database Systems 46
Collection of Documents

Database Systems 47
NoSQL Database
• Schemaless
• More flexible
• No need to alter schema to update the data

• Still you can validate data using security rules

• Similar to constraints in a relational schema

Database Systems 48
Adding data

Database Systems 49
Relational Model

Database Systems 50
Get a list of attendees for an event

Database Systems 51
How to model this as NoSQL database?

Database Systems 52
Flat Structure

Database Systems 53
Querying Data in SQL

Database Systems 54
Querying Data in NoSQL

Database Systems 55
Example

Database Systems 56
Recommended YouTube Series

https://www.youtube.com/playlist?list=PLl- https://www.youtube.com/playlist?list=PLl-
K7zZEsYLlP-k-RKFa7RyNPa9_wCH2s K7zZEsYLluG5MCVEzXAQ7ACZBCuZgZ

Database Systems 57
Login System

Signup and login

https://www.youtube.com/watch?v=8K4Wt37Itc4

Verification
https://www.youtube.com/watch?v=C-ZSQMbsm7A

Database Systems 58
Creating the Database

Database Systems 59
phpMyAdmin

Database Systems 60
Define constants (config/constants.php)
<?php

define('DB_HOST','localhost');
define('DB_USER','root');
define('DB_PASS','');
define('DB_NAME','user-verification');

Database Systems 61
Connect to database (config/db.php)
<?php

require 'constans.php';

$con = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME)

or die ('Could not connect to the database server' . my
sqli_connect_error());

//$con->close();

Database Systems 62
authController.php
• Create controllers/authController.php

• Include it into signup.php and login.php

<?php require_once ’controllers/authController.php'; ?>

• Include db.h in authController.php

require 'config/db.php';

Database Systems 63
Check if email already exists
$emailQuery = "SELECT * FROM users WHERE useremail=? LIMIT 1";

$stmt = $con->prepare($emailQuery);
$stmt ->bind_param('s',$email);
$stmt->execute();
$result = $stmt->get_result();
$userCount = $result->num_rows;
$stmt->close();

if($userCount>0){
$errors['email']="Email already exists";
}
Database Systems 64
 If no errors, we need to insert user to DB
• Add a password hash for security
• Add a token to be used for email verification
• Set verified to false

if(count($errors)==0){
$password = password_hash($password,PASSWORD_DEFAULT);
$token = bin2hex(random_bytes(50));
$verified = false;

Database Systems 65
Insert user to database
$sql = "INSERT INTO users (username, useremail, verified, token, password)
VALUES (?,?,?,?,?)";

$stmt = $con->prepare($sql);

$stmt ->bind_param('ssbss',$username,$email,$verified,$token,$password);

if($stmt->execute()){

} else {
$errors[‘db_error’]=“Database error: failed to register”;
}

Database Systems 66
Save User info in a session (use across pages)
if ($stmt->execute()){
//login user
$user_id = $con->insert_id;
$_SESSION['id']=$user_id;
$_SESSION['username']=$username;
$_SESSION['email']=$email;
session_start(); $_SESSION['verified']=$verified;

//set flash message

$_SESSION['message']="You are now logged in!";
$_SESSION['alert-class']="alert-success";
header('location: index.php');
exit();
}
else{
$errors['db_error']="Database error: failed to register";
}
Database Systems 67
Update index.php to include session info
<div class="alert <?php echo $_SESSION['alert-class'];?>">
<?php echo $_SESSION['message'];?>
</div>

<h3>Welcome, <?php echo $_SESSION['username'];?></h3>

<a href="#" class="logout">Logout</a>

<?php if(!$_SESSION['verified']):?>
<div class="alert alert-warning">
You need to verify your account.
Verfication email sent to you at
<strong><?php echo $_SESSION['email'];?></strong>
</div>
<?php endif ?>

Database Systems 68
index.php

Database Systems 69
login.php

Database Systems 70
Email Verfication
• Add email controller to send registered user an email once new user
is inserted to the database
• Install composer
• Use swiftmailer library

Database Systems 71