Building A Web Application With PHP And MariaDB: Reference Guide Maria DB
User Manual: Pdf
Open the PDF directly: View PDF 
.
Page Count: 200
| Download | |
| Open PDF In Browser | View PDF |
Building a Web Application
with PHP and MariaDB:
A Reference Guide
Build fast, secure, and interactive web applications
using this comprehensive guide
Sai Srinivas Sriparasa
BIRMINGHAM - MUMBAI
Building a Web Application with PHP and MariaDB:
A Reference Guide
Copyright © 2014 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval
system, or transmitted in any form or by any means, without the prior written
permission of the publisher, except in the case of brief quotations embedded in
critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy
of the information presented. However, the information contained in this book is
sold without warranty, either express or implied. Neither the author, nor Packt
Publishing, and its dealers and distributors will be held liable for any damages
caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the
companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information.
First published: June 2014
Production Reference: 1090614
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-78398-162-5
www.packtpub.com
Cover Image by Artie Ng (artherng@yahoo.com.au)
Credits
Author
Sai Srinivas Sriparasa
Reviewers
Project Coordinator
Sageer Parkar
Proofreaders
Dario Grd
Simran Bhogal
Nikolai Lifanov
Stephen Copestake
Esteban De La Fuente Rubio
Indexers
Commissioning Editor
Kunal Parikh
Hemangini Bari
Mariammal Chettiyar
Mehreen Deshmukh
Acquisition Editor
Mohammad Rizvi
Content Development Editor
Shaon Basu
Technical Editors
Mrunmayee Patil
Aman Preet Singh
Copy Editors
Janbal Dharmaraj
Sayanee Mukherjee
Tejal Soni
Production Coordinator
Nitesh Thakur
Cover Work
Nitesh Thakur
About the Author
Sai Srinivas Sriparasa is a web developer and an open-source evangelist living
in the Atlanta area. He was the lead developer for building Dr. Oz's website and
currently works on predictive analysis algorithms for News Distribution Network
(NDN). He has previously led teams for companies such as Sprint Nextel, West
Interactive, Apple, and SAC Capital. His repertoire includes PHP, Python, MySQL,
MariaDB, MongoDB, Hadoop, JavaScript, HTML5, Responsive Web Development,
ASP.NET, C#, Silverlight, and so on. He has worked on books such as JavaScript and
JSON Essentials, Packt Publishing.
I want to convey my sincere thanks to the team at Packt Publishing
for making this book possible: Shaon, Sageer, and Sumeet in
particular. This is my second book, so I want to thank all of the
readers in advance for taking time to read my book. Please contact
me on my LinkedIn profile, http://www.linkedin.com/in/
saisriparasa, for networking or any questions that you have.
My acknowledgement section will not be complete unless I thank
my mom, dad, and my sister for all their patience and support
throughout my life. I hope you all enjoy this book as much as I did
and wish me luck for my next book.
About the Reviewers
Dario Grd is a web developer with 7 years of experience in various technologies.
He works with programming languages such as PHP, Java, Groovy, and .NET.
He loves working with frameworks such as Symfony, Grails, jQuery, and Bootstrap.
He finished his master's degree in Informatics at the Faculty of Organization and
Informatics, University of Zagreb. After getting the degree, he started working as a
programmer at a company specialized in developing banking information systems,
where he became a web team leader. Currently, he is working at the Faculty of
Organization and Informatics as an expert assistant in Higher Education and
Science System at Application Development Centre.
He works on various European and freelance projects. He developed a
new Content Management System (CMS) from scratch and is very proud of it.
Other than programming, he is also interested in web server administration and
is currently managing a hosting server. When he is not working, you can find
him on the soccer field or playing table tennis. He plays futsal for a local team
and competes in an amateur table tennis league. You can follow him at
http://dario-grd.iz.hr/en.
Nikolai Lifanov hacks systems. This means doing things that aren't meant to be
done to create a useful effect in a hurry. Over the last decade, he has had experience
in everything from running HA infrastructures on donated prefail hardware to
dealing with emergency spikes in service demand by padding the infrastructure
with cloud services within hours. He had roles ranging from that of a full-stack
engineer to a developer, but feels most in his element focusing on essential system
infrastructure. He builds robust and observable systems that are hard to break
and easy to fix with a strong focus on self-healing, security, and reducing essential
ongoing maintenance. He has built solutions from Linux and BSD systems, from
creating immutable live cd NetBSD hypervisors (a la SmartOS) to founding a hosting
business based on DragonFly. He tries to be active in the open source community
and enjoys old-school roguelike games. His hobbies include researching obscure
ancient arcane Unix lore and retro computing.
Esteban De La Fuente Rubio is a programmer with experience mainly in the
PHP language. He worked in his earlier years developing small websites and by
now, 10 years later, he is the author of the SowerPHP framework on GitHub. In the
last 6 years, he has worked for various Chilean companies developing software
for supporting their process. He also contributed to the free software community
developing small applications and tools to make life easier (more details on this can
be found at his GitHub account, namely, https://github.com/estebandelaf).
www.PacktPub.com
Support files, eBooks, discount offers, and more
You might want to visit www.PacktPub.com for support files and downloads related
to your book.
Did you know that Packt offers eBook versions of every book published, with PDF and
ePub files available? You can upgrade to the eBook version at www.PacktPub.com and
as a print book customer, you are entitled to a discount on the eBook copy. Get in touch
with us at service@packtpub.com for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up
for a range of free newsletters and receive exclusive discounts and offers on Packt books
and eBooks.
TM
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital
book library. Here, you can access, read and search across Packt's entire library of books.
Why subscribe?
•
•
•
Fully searchable across every book published by Packt
Copy and paste, print and bookmark content
On demand and accessible via web browser
Free access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access
PacktLib today and view nine entirely free books. Simply use your login credentials for
immediate access.
Table of Contents
Preface
Chapter 1: CRUD Operations, Sorting, Filtering, and Joins
String datatypes
Number datatypes
Date datatypes
The students table
The courses table
The students_courses table
Inserting data
Retrieving data
Sorting data
Filtering data
Updating data
Deleting data
Joins
Summary
1
7
9
10
11
15
17
18
19
23
24
26
29
29
30
33
Chapter 2: Advanced Programming with MariaDB
35
Chapter 3: Advanced Programming with PHP
61
Enhancing the existing tables
Working with stored procedures
Working with stored routines
Working with triggers
Summary
New features in PHP 5.4 and 5.5
Updated array declaration
The array dereferencing function
The list() function in the foreach statement
35
43
51
53
59
61
62
63
64
Table of Contents
Availability of $this in closures
Class member access on instantiation
Generators
Traits
Addition of the finally block to exception handling
Unit testing
Installing PHPUnit
Working with MariaDB
PHP – mysqli
PHP – PDO
Summary
Chapter 4: Setting Up Student Portal
Setting up the nuts and bolts of our application
Setting up URL rewrite
Setting up MVC
Adding a student
Listing all students
Adding a course
Listing all courses
Registering a student to a course
Viewing all registrations
Summary
66
67
69
71
73
75
76
80
81
83
86
87
88
88
91
96
100
102
105
106
109
112
Chapter 5: Working with Files and Directories
113
Chapter 6: Authentication and Access Control
123
Chapter 7: Caching
139
Data imports
Data exports
Logging
Summary
Authentication
Access controls
User roles
Summary
Introduction to caching
Caching in the database
Caching in the application
Advanced caching techniques
Summary
113
118
119
122
123
130
134
138
139
140
144
146
147
[ ii ]
Table of Contents
Chapter 8: REST API
149
Chapter 9: Security
157
Chapter 10: Performance Optimization
167
Index
179
What is REST?
Generating XML feeds
Generating JSON feeds
Summary
149
153
154
155
Securing the Apache web server
Hiding server information
Server configuration limits
Securing MariaDB
Password-protected access
Building views to restrict access
Creating users and granting access
Securing PHP
Summary
Performance optimization for the Apache web server
Disabling unused modules
Using compression
Caching
Performance optimization for MariaDB
Best practices for data retrieval
Understanding query execution
Query optimization and indexing
Performance optimization for PHP
CDN, reverse proxy, and database replication
Summary
[ iii ]
157
158
159
160
161
161
162
164
165
168
168
170
171
171
172
173
173
175
176
177
Preface
In the age of the Internet, building a web application is no longer a tough task,
but building the web application in the right way is not a trait mastered by many.
Building a Web Application with PHP and MariaDB: A Reference Guide is aimed at taking
readers to the next level and to transform them from beginner-level programmers to
intermediate or advanced-level programmers. Building a Web Application with PHP
and MariaDB: A Reference Guide is a well thought out guide that begins with the basics
of PHP and MariaDB and covers complex topics such as caching, security, building
a REST API, and performance optimization. Building a web application that will be
secure, scale well under pressure, and have an API available to different subscribers
is not a simple task, but this book will make this a simple, easy-to-learn, and a
memorable journey.
What this book covers
Chapter 1, CRUD Operations, Sorting, Filtering, and Joins, deals with introducing
readers to basic SQL operations such as create, read, update, and delete. We then
go to the next step by discussing sorting, filtering, and end by discussing the
concept of joining tables.
Chapter 2, Advanced Programming with MariaDB, deals with various data
manipulation operations such as alter and drop. After a clear understanding
of the DML operations, we will discuss the more advanced concepts such as
stored procedures, stored routines, and triggers.
Chapter 3, Advanced Programming with PHP, introduces readers to more advanced
programming concepts such as unit testing and exception handling. We also discuss
the new features that have been added to PHP 5.4 and 5.5.
Chapter 4, Setting Up Student Portal, deals with using all the concepts encompassed
in the last few chapters to build a student portal.
Preface
Chapter 5, Working with Files and Directories, deals with the introduction and
implementation of file imports, file uploads, and application logging using
files in our student portal application.
Chapter 6, Authentication and Access Control, deals with the introduction and
implementation of authentication and access controls for our student
portal application.
Chapter 7, Caching, introduces the readers to the concept of caching. We later discuss
the different types of caching and how each method of caching is implemented.
Chapter 8, REST API, introduces readers to the concept of REST architecture,
followed by building a REST API for our student portal.
Chapter 9, Security, deals with an introduction to the different security optimizations
that can be performed for Apache, MariaDB, and PHP to secure the web application.
Chapter 10, Performance Optimization, deals with the introduction of different
performance optimization techniques that can be used to scale the application
more effectively.
Bonus chapter 1, Installation of PHP, MariaDB, and Apache, deals with the installation
and configuration of PHP, MariaDB, and Apache. This chapter is not present in
the book but is available for download at https://www.packtpub.com/sites/
default/files/downloads/Bonus_chapter_1.pdf.
Bonus chapter 2, Object-oriented Programming with PHP, deals with introducing the
readers to the concept of Object Oriented Programming (OOP) with PHP and we
continue by discussing various OOP features such as inheritance, encapsulation,
polymorphism, interfaces, and abstract classes. We end this chapter by discussing
a few popular design patterns. This chapter is not present in the book but is available
for download at https://www.packtpub.com/sites/default/files/downloads/
Bonus_chapter_2.pdf.
What you need for this book
This book deals with building web applications; so, to successfully host a web
application, you will need the Apache web server. Once the request is received
by the web server, it will forward that request to the server-side program, and we
will be using PHP for our server-side scripting. We will be using MariaDB as
our database server to store our data. We are using Memcache for memory caching.
The software needed are: PHP, MariaDB, Apache server, cURL, and Memcache.
[2]
Preface
Who this book is for
This book has been designed to cater to the needs of developers at all levels. This book
contains numerous examples, tips, and recommendations that will guide the readers
from the installation and configuration phase to deployment phase. Prior knowledge
of PHP, MariaDB, and/or Apache web server will be very helpful, but not required.
Conventions
In this book, you will find a number of styles of text that distinguish between
different kinds of information. Here are some examples of these styles, and an
explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions,
pathnames, dummy URLs, user input, and Twitter handles are shown as follows:
"Upon executing the show databases; command, the list of existing databases
will be outputted to the screen"
A block of code is set as follows:
Any command-line input or output is written as follows:
phpunit --version
[3]
Preface
New terms and important words are shown in bold. Words that you see on the
screen, in menus or dialog boxes for example, appear in the text like this: "Now that
the database has been successfully changed, note that the database name reflects in
between the brackets next to MariaDB, which denotes the current database."
Warnings or important notes appear in a box like this.
Tips and tricks appear like this.
Reader feedback
Feedback from our readers is always welcome. Let us know what you think about
this book—what you liked or may have disliked. Reader feedback is important for
us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to feedback@packtpub.com,
and mention the book title via the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing
or contributing to a book, see our author guide on www.packtpub.com/authors.
Customer support
Now that you are the proud owner of a Packt book, we have a number of things
to help you to get the most from your purchase.
Downloading the example code
You can download the example code files for all Packt books you have purchased
from your account at http://www.packtpub.com. If you purchased this book
elsewhere, you can visit http://www.packtpub.com/support and register to
have the files e-mailed directly to you.
[4]
Preface
Errata
Although we have taken every care to ensure the accuracy of our content, mistakes do
happen. If you find a mistake in one of our books—maybe a mistake in the text or the
code—we would be grateful if you would report this to us. By doing so, you can save
other readers from frustration and help us improve subsequent versions of this book.
If you find any errata, please report them by visiting http://www.packtpub.com/
submit-errata, selecting your book, clicking on the errata submission form link,
and entering the details of your errata. Once your errata are verified, your submission
will be accepted and the errata will be uploaded on our website, or added to any list of
existing errata, under the Errata section of that title. Any existing errata can be viewed
by selecting your title from http://www.packtpub.com/support.
Piracy
Piracy of copyright material on the Internet is an ongoing problem across all media.
At Packt Publishing, we take the protection of our copyright and licenses very
seriously. If you come across any illegal copies of our works, in any form, on the
Internet, please provide us with the location address or website name immediately
so that we can pursue a remedy.
Please contact us at copyright@packtpub.com with a link to the suspected
pirated material.
We appreciate your help in protecting our authors, and our ability to bring
you valuable content.
Questions
You can contact us at questions@packtpub.com if you are having a problem
with any aspect of the book, and we will do our best to address it.
[5]
CRUD Operations, Sorting,
Filtering, and Joins
Data storage and management have been a very powerful trait for a long time, and
as a server-side web developer, it is of paramount importance to have a thorough
understanding of the available data storage options. The data that we might be
dealing with could be user information, company data, order data, product data, or
personal data, and so on. Data in its raw form needs to be processed, cleared, and
organized to generate information. Text files and spreadsheets can be used by web
applications for storing data but, as the amount of data grows in size, it becomes
very hard to store all the data in a single file, as the burgeoning size takes its toll
on the speed of retrieval, insertion, and constant updates to the file. Numerous
websites store the users' access information in daily or weekly logfiles in the text
format, which ends up with a large number of logfiles. The common problem with
data storage in this fashion is conserving the data integrity, an example being the
process of weeding out duplicate records when data spanned across multiple files
becomes cumbersome. A few other problems with data storage in files is the process
of managing updates to the file, logging the information about what the updates
were or who made them, and applying the necessary file locks when multiple users
access and update files at the same time. These are a few reasons why there has
always been a need to look for other data storage and management solutions.
An alternate data storage solution, the method that we will rely upon for the most
part of this book, is to store the data in a database. A database is an integrated
collection of data, and the rules pertaining to that data. A database relies upon
a database management system to store the data in an organized manner, to
implement the rules that guard the data, and to make the operations such as
data retrieval, data modification, and data management simple.
CRUD Operations, Sorting, Filtering, and Joins
A Database Management System (DBMS) is a software or a collection of
programs that manage a single database or multiple databases, and provide
critical functionality for data administration, data access, and efficient data
security. An example of a database management system is a bookshelf, which
is an enclosed space that can be used for storing books in an organized manner.
There are multiple vendors who provide different database management systems
and we will focus on MariaDB.
Continuing with the bookshelf example, the content of a book is divided into
chapters; similarly, the data in a database is stored in tables. A table can be
described as the fundamental building block of the database. Data can only
be stored inside a table, if there are no tables in the database; the database is
devoid of data. Every table is identified by a unique name, meaning that the same
database cannot have two tables with the same name. The data in a table is stored
and is represented in a two-dimensional format as rows and columns. MariaDB is
a RDBMS and follows the theory of relational-models proposed by Edgar F Codd.
The term relational is applied in two ways, the first is the relation between one or
more tables in the same database and the second is the relationship between the
columns within a table.
Tables carry certain characteristics and are built based on a specific structure
(or a layout) that defines how the data will be stored. These characteristics are
a unique name for the column and the type of data that will be stored in the
column. A row would store the smallest unit of information that can be stored in
a table and each column in the table will store a piece of relevant data for a single
record. We can have a table with all our users' data, a table with all our orders
information, and a table with all our product information. Here, each row in the
users table would represent a user record, each row in the orders table would
represent an order record, and each row in the products table would represent
a product record. In the users table, the columns could be username, address,
city, state, and zip code; all these columns provide certain data about the user.
Each column is associated with a datatype that defines the type of data that can
be stored in the column. Datatypes restrict the type of data that can be stored in
a column, which allows for a more efficient storage of data. Based on the type of
data that is expected to be stored, datatypes can be broadly categorized into
numeric, string, and date-time datatypes.
[8]
Chapter 1
String datatypes
Let us look at the following main datatypes:
Datatype
CHAR(L)
Explanation
Comments
This stores a fixed-length string
between 0 and 255 bytes.
Trailing spaces are removed.
VARCHAR(L)
This stores a variable-length
string between 0 and 65,535
characters.
65,535 is the effective
maximum row size for table.
TEXT
This stores character data and the
maximum length of a text column
is 65,535 characters.
Length need not be specified.
TINYTEXT
This stores the text column
with a maximum length of 255
characters.
MEDIUMTEXT
This stores the text column with
a maximum length of 16,777,215
characters.
LONGTEXT
This stores the text column with a
maximum length of 4,294,967,295
characters.
BLOB
This stores binary data and the
maximum length of a text column
is 65,535 bytes.
TINYBLOB
The BLOB datatype column with a
maximum length of 255 bytes.
MEDIUMBLOB
This stores the text column with
a maximum length of 16,777,215
bytes.
LONGBLOB
This stores the text column with a
maximum length of 4,294,967,295
bytes.
ENUM
This provides a list of strings from
which a value can be chosen.
A list of 65,535 values can
be inserted into the ENUM
datatype.
SET
This is similar to the ENUM
datatype. It provides a list of
strings from which zero or more
values can be chosen.
Can have a maximum of 64
distinct values.
[9]
Binary Large Objects are
used to store binary data
such as images.
CRUD Operations, Sorting, Filtering, and Joins
Number datatypes
Let us now look at the following main number datatypes:
Datatype
tinyint
Explanation
Comments
This stores integer values.
-128 to 127, Signed
0 to 255, Unsigned
Smallint
This stores integer values.
-32768 to 32767, Signed
0 to 65535, Unsigned
Mediumint
This stores integer values.
-8388608 to 8388607, Signed
0 – 16777215, Unsigned
int(l)
Bigint
This stores integer values
and takes the size of the
number.
-2147483648 to 2147483647, Signed
This stores integer values.
-9223372036854775808 to
9223372036854775807, Signed
0 – 4294967295, Unsigned
0 to 18446744073709551615, Unsigned
Float(l,d)
This stores floating point
numbers and allows us to
define the display length
(l) and the number of digits
after the decimal point (d).
The default values for l, d
are 10 and 2, respectively.
This uses 4-byte single precision and
can display from 0 to 23 digits after
the decimal.
Double(l,d)
This is similar to FLOAT,
and uses 8-byte double
precision. The default
values for l, d are 16 and 4,
respectively.
The DOUBLE datatype can display
from 24 to 53 results. Both the FLOAT
and DOUBLE datatypes are commonly
used for storing the results from
scientific calculations.
decimal(l,d)
This stores the exact
numeric data values and
allows us to define the
display length (l) and the
number of digits after
decimal point (d).
This is used for precision mathematics
that deals with extremely accurate
results. The DECIMAL datatype
is commonly used to store
monetary data.
[ 10 ]
Chapter 1
Date datatypes
Let us now look at the following main date datatypes:
Datatype
Date
Explanation
Comments
This stores the date in YYYY-MMDD format.
The supported range is from
1000-01-01 to 9999-12-31.
Time
This stores the time in HHH:MM:SS
format.
The supported range is from
-838:59:59 to 838:59:59.
datetime
This stores both the date and time in
YYYY-MM-DD HH:MM:SS format.
The supported range is
from 1000-01-01 00:00:00 to
9999-12-31 23:59:59.
Timestamp
This stores both the date and time.
The supported range is from
1970-01-01 00:00:01 UTC to
2038-01-19 03:14:07 UTC.
year (L)
This stores the year in either a
2-digit or a 4-digit format. The
length of the year can be specified
during declaration. The default is a
4-digit year.
The supported range for
a 4-digit year is from 1901
to 2155.
Now that we have discussed the available datatypes for building columns,
we will use SQL to build our first table. Structured Query Language (SQL) is
a multipurpose programming language that allows us to communicate with the
database management system to manage and perform operations on the data.
SQL operations can be divided into three groups: Data Definition Language (DDL),
Data Manipulation Language (DML), and Data Control Language (DCL).
These three groups are explained in the following table:
Groups
Explanation
DDL
Data Definition Language can be
used to create a table or alter the
structure of a table once it is built,
drop the table if it is deemed to
be unnecessary, and to perform
operations such as truncating the
data in a table and creating and
dropping indexes on columns.
Operations
[ 11 ]
• CREATE
• ALTER
• DROP
• TRUNCATE
• RENAME
CRUD Operations, Sorting, Filtering, and Joins
Groups
Explanation
DML
Data Manipulation Language is
used to perform insert, update,
delete, and select operations on
the data.
Operations
• SELECT
• INSERT
• UPDATE
• DELETE
• CALL
• REPLACE
• LOAD DATA INFILE
DCL
Other
administration
and utility
statements
Data Control Language is used for
managing the access to the data.
DCL can be used to work with
MariaDB's complex security model.
• GRANT
Other SQL commands that are
often used but do not come under
DDL, DML, or DCL.
• EXPLAIN
• REVOKE
• SHOW
• DESCRIBE
• HELP
• USE
Now that we have discussed the basics of Database Management System and SQL,
let us connect to our MariaDB server. MariaDB is shipped with a few pre-existing
databases that are used by MariaDB itself to store metadata such as information
about databases, tables, columns, users, privileges, logs, and so on (yes, MariaDB
stores its own data in MariaDB tables).
For more information about the installation procedures for PHP,
MariaDB, and Apache, please refer to the Bonus chapter 1, Installation of
PHP, MariaDB, and Apache present online on the Packt Publishing website.
[ 12 ]
Chapter 1
As we have installed MariaDB and have root access to the server, we will be able
to view all this metadata information. To retrieve the metadata information that
is currently on MariaDB, we will use the SHOW utility command and, as we are
interested in retrieving the list of existing databases, we will append DATABASES
to our SHOW command:
SQL commands are case-insensitive, so the case of the SQL command
does not matter.
Upon executing the show databases; command, the list of existing databases
will be outputted to the screen. These databases are reserved to store configurations
and necessary metadata (yes, MariaDB stores its data on MariaDB itself), so it is
advised to avoid using these databases for storing other data. For storing other
data, we will have to create our own database. We will use the SQL commands
that are part of DDL to create new databases. For creating a new database, the
CREATE DDL command is appended with DATABASE and then the name of the
database to be created is added. Let us create a simple course registry database
that keeps a track of student records, the available courses, and the courses for
which the students have registered.
MariaDB is very particular about statement terminators, a
semicolon ; is the default statement terminator and, unless the
statement terminator is given, the SQL command is not executed.
[ 13 ]
CRUD Operations, Sorting, Filtering, and Joins
We have successfully created our first database. To verify that we have created
this database, let us run the show databases; command one more time to see
if our new database is reflected in the list of existing databases:
Now that we have verified that our new database is available in the list of existing
databases, let us access the database and build tables in the course_registry
database. For accessing a database, we will utilize the USE utility command. The USE
command has to be followed with the name of an existing database to avoid an error,
once this command has been executed.
Now that the database has been successfully changed, note that the database
name reflects in between the brackets next to MariaDB, which denotes the
current database.
Another way of finding the current database is to use the select
database(); statement and print it out to the console; if the
output is null, this means that no database has been selected.
Now that we have chosen the course_registry database, let us take a brief look
at the data that has to be housed in this database. The course_registry database
keeps a track of student records, the available courses, and the courses for which the
students have registered. We could do this by putting the students and the courses
that they have registered for in a single table. However, the problems with this
approach, similar to a spreadsheet, are twofold. The first problem is that the student
information would keep repeating when a student registers for multiple courses,
thereby causing unnecessary redundancy.
[ 14 ]
Chapter 1
The second problem will be about data inconsistency, assuming that the student
information was wrong. Either we will be using this erroneous information another
time, or we might be employing another data entry process that allows the user
to enter different data as user information, which causes data inconsistency.
To avoid this, we are splitting our data into three tables; they are students,
courses, and students_courses.
The student records will be stored in the students table, the data about the available
courses will be stored in the courses table, and the data about the courses that the
students have registered for will be stored in the students_courses table. The
students_courses table will be an association table that contains common fields
from the students and the courses tables. This table can also be referred to as a bridge
table, paired table, or cross reference table. By using the students_courses table, we can
accommodate a common case where one student can register for more than one course.
Before we begin building our tables, it is always important to understand the type
data that will be housed in this table and based on the data that will be housed in
that table, we will have to decide on the column names and the datatypes for those
columns. Column names have to be intuitive in order to help others such as system
administrators, auditors, and fellow developers to easily understand the kind of
data that can be or is currently being stored in those columns, and the respective
datatypes of those columns will explain the type that can be housed in a column.
Let us begin with our students table.
The students table
Let us take a look at the following fields in the table and what work they perform:
Column name
student_id
Datatype
Int
Comments
first_name
Varchar(60)
This stores the first name of the student
last_name
Varchar(60)
This stores the last name of the student
address
Varchar(255)
This stores the address of the student
city
Varchar(45)
This stores the name of the city
state
Char(2)
This stores the two letter abbreviation for
states in the United States
zip_code
Char(5)
This stores the five digit zip code for an
address in the United States
This stores the unique identifier for
a student
[ 15 ]
CRUD Operations, Sorting, Filtering, and Joins
It is advised to use a character datatype for fields such as zip codes or
SSNs. Though the data is going to be a number, integer datatypes are
notorious for removing preceding zeroes, so if there is a zip code that
starts with a zero, such as 06909, of an integer datatype, the zip code
would end up in the column as 6909.
Now let us convert this table structure into executable SQL, to create our table,
we will be using the CREATE DDL command, followed by TABLE and then append it
with the table structure. In SQL, the column description is done by mentioning the
column name first and then adding the datatype of the column. The STUDENTS table
has multiple columns, and the column information has to be separated by a comma (,).
Now that the query has been executed, the students table has been created.
To verify if the students table has been successfully built, and to view a list
of existing tables that are in the current database, we can use the SHOW utility
command and append that with TABLES:
We have successfully used the show tables; command SQL statement to retrieve
a list of existing tables, and have verified that our students table exists in our
course_registry database. Now, let us verify if our students table has the
same table structure as we originally intended it to have. We will use the DESCRIBE
utility command followed by the table name to understand the table structure:
[ 16 ]
Chapter 1
The DESCRIBE and DESC commands can be used interchangeably,
both the commands would need the table name to return their structure.
Now let us move on to the courses table, this table will house all the available
courses for which a student can register. The courses table will contain a unique
identifier for the course (course_id), the name of the course (course_name),
and a brief description of the course (course_description).
The courses table
Let us now look at the fields and the type of values they are storing:
Column name
course_id
Datatype
int
Comments
name
varchar(60)
This stores the title of the course.
description
varchar(255)
This stores the description of a course.
This stores the unique identifier for a course.
Now let us convert this table structure into executable SQL to create our
courses table:
[ 17 ]
CRUD Operations, Sorting, Filtering, and Joins
Now that the query has been executed, let us run the SHOW TABLES command to
verify if the courses table has been created:
The output from the SHOW TABLES command returns the list of current tables,
and the courses table is one of them. Now that we have built the students table
and the courses table, let us build the bridge table that would hold the association
between the two tables. This table would contain the data about the students who
were enrolled to a particular course.
The students_courses table
Let us now look at the fields in this table and their respective values:
Column name
course_id
Datatype
int
Comments
student_id
int
This stores the unique identifier for a student
This stores the unique identifier for a course
Now, let us convert this table structure into executable SQL, to create our courses
table using the following command:
[ 18 ]
Chapter 1
Now that the query has been executed, let us run the SHOW TABLES command to
verify if the courses table has been created:
The output from the SHOW TABLES command returns the list of current tables,
and the students_courses table is one of them.
Inserting data
Now that we have built our tables, it is time to insert records into the tables.
Let us look at a few different methods for inserting a single row of data and inserting
multiple rows of data. For insertion of data into a table, we will use the INSERT DML
command, and supply the table name and the values for the available columns in the
table. Let us begin by inserting student records into the students table:
[ 19 ]
CRUD Operations, Sorting, Filtering, and Joins
In this example, we insert a new student record into the students table; we are
supplying the data for that student record in the VALUES clause. This syntax,
though it appears to be very simple, it is not a very safe method of inserting data.
This INSERT statement is depending upon the order in which the columns were
defined in the table structure, so the data in the VALUES clause will be mapped by
position, 1 would go into the first column in the table, though it is intended to go
into the student_id column. If the students table is rebuilt locally or on a different
machine, there is no guarantee that the column order would remain the same as the
order on the current MariaDB database server. The other approach that is considered
safer when compared to this one is the INSERT statement, where the column names
are explicitly mentioned in the SQL:
Though this might be a bit longer, this would guarantee that data that is being
passed in via the VALUES clause is going into the right column. By using this INSERT
syntax, the order in which the columns are mentioned is no longer important.
When this query is executed, MariaDB matches each item in the columns list with
its respective value in the VALUES list by position. This syntax can also be used for
the case where the data is only available for a few columns. Let us come up with an
INSERT statement that has data for a few columns and uses NULL for a column that
does not have any data:
In SQL, the term NULL is used to denote that a value does not exist.
[ 20 ]
Chapter 1
In this example, we are inserting a student record whose address is not known,
so we are using NULL to populate the column.
Columns by default allow NULL values to be populated,
unless it is explicitly mentioned not to allow NULL values.
Now that we have seen the different insertion syntaxes for inserting a single record
row, let us take a step forward and look at how multiple records can be inserted.
There are two ways of inserting multiple records into a table, the first method
is where INSERT statements are created for each row, and are separated by the
statement terminator (;):
[ 21 ]
CRUD Operations, Sorting, Filtering, and Joins
The other way of inserting multiple records is by using a single VALUES clause
while passing in multiple records, separating each record with a comma (,),
and adding a statement terminator at the end of the last record:
We are currently not using any constraints to maintain any
referential integrity among tables, so any integers can be
inserted into the students_courses table. To allow only
existing student IDs and course IDs to be inserted, we will
have to use the primary key and foreign key constraints.
We will be covering constraints in the next chapter.
In this example, we are inserting multiple records into the students_courses table.
On execution of this SQL query, the first statement inserts an associative record into
the students_courses table and the value for the column student_id is 1, which
maps back to the student record of John Doe, and the value for course_id is 1 that
corresponds to the course record CS-101. The inline comments at the end of each
statement are used to describe the data that is being inserted via this statement.
Though these comments are added to the INSERT statements, they are only intended
to explain the purpose of the statements and will not be processed by MariaDB.
MariaDB also supports multi-line comments. Syntax for
creating multi-line comments is by using /* to start the
comment and ending the comment with */.
/* multiple line
comments
go
here*/
The last method of insertion that we are skipping for now is to insert the data that
has been retrieved on the fly from a table. We will be looking at that once we have
covered the methods for retrieving data and filtering data.
[ 22 ]
Chapter 1
Retrieving data
Now that we have inserted data into the students, courses, and students_courses
tables, let us look at the different mechanisms of retrieving data, we will be using the
SELECT command to retrieve the data. The SELECT statement would expect two things
as a minimum, the first would be what to retrieve and the second would be where to
retrieve it. The simplest SELECT command would be to retrieve all the student records
from the students table:
In this query, we are using * to retrieve the data for all the columns from the students
table, this is not a preferred method of retrieving data. The preferred method for data
retrieval is by mentioning the individual columns separated by a comma (,) after the
SELECT clause:
In this query, we are selecting the student_id, first_name, and last_name
columns from the students table. As we are not filtering the data yet, SELECT
statements would return every student record that is in the students table.
We can use the LIMIT clause to retrieve a certain number of records:
[ 23 ]
CRUD Operations, Sorting, Filtering, and Joins
In this query, we are retrieving the data from the students table and we are
retrieving the student_id, first_name, and last_name columns; however, rather
than retrieving all the rows, we are only retrieving a single row. To retrieve the next
row, we could still use the limit, but we would use LIMIT clause accompanied by
the OFFSET clause. The OFFSET clause determines the starting point as to where the
records should start from, while the LIMIT clause determines the number of records
that would be retrieved.
Sorting data
Now that we have looked at different techniques of retrieving the data, let us look
at how the data can be represented in a more ordered way. When we execute a
SELECT statement, the data is retrieved in the order in which it exists in the database.
This would be the order in which the data is stored; therefore, it is not a good idea to
depend upon MariaDB's default sorting. MariaDB provides an explicit mechanism
for sorting data; we can use the ORDER BY clause with the SELECT statement and
sort the data as needed. To understand how sorting can be of help, let us begin by
querying the students table and only retrieving the first_name column:
In the first example, we are going by MariaDB's default sort, and this would give us
the data that is being returned based on the order of the insert:
[ 24 ]
Chapter 1
In this example, we are ordering the data based on the first_name column.
The ORDER BY clause by default sorts in ascending order, so the data would be
sorted in an ascending alphabetical order and if the first character of one or more
strings is the same, then the data is sorted by the second character, which is why
Jane comes before John. To explicitly mention the sort order as ascending, we can
use the keyword asc after the column name:
In this example, we are again ordering the data based on the first_name column and
the ORDER BY clause has been supplied with desc, we are setting the sort direction
to descending, which denotes that the data has been sorted in a descending order.
MariaDB also provides a multi-column sort, which is a sort within a sort. To perform
a multi-column sort, we would specify the column names after the ORDER BY clause
separated by comma (,). The way the multi-column works is, the data would be first
sorted by the first column that is mentioned in the ORDER BY clause, and then the
dataset that has already been sorted by the first column is again sorted by the next
column and the data is returned back. As a muti-column sort performs sorting on
multiple levels, the order of columns will determine the way the data is ordered. To
perform this example, let us insert another row with the student name John Dane and
the student ID being 4, the reason for using John Dane is to make sure that there are
more than one students that share the first name of John (John Doe and John Dane)
and the last name of Dane (Jane Dane and John Dane) exclusively:
[ 25 ]
CRUD Operations, Sorting, Filtering, and Joins
In this example, we are retrieving the last_name and first_name columns from the
students table and are first ordering the data by "last_name" and then reordering
the previously ordered dataset by first_name. We are not restricted by the ORDER
BY clause to use only the columns being used for the sort. This will only help us sort
the data in the correct direction.
Filtering data
Until now, we have dealt with data retrieval where all the data in the students
table is being retrieved, but seldom do we need all that data. We have used the LIMIT
and OFFSET clauses that have allowed us to limit the amount of data were retrieved.
Now let us use MariaDB's filtering mechanism to retrieve the data by supplying
search criteria. To perform a search in a SQL statement, we will use the WHERE clause.
The WHERE clause can be used with the SELECT statement, or it can be even used with
the UPDATE and DELETE statements, which will be discussed in the next section:
In the preceding example, we are selecting the students' records whose last_name
is Dane.
[ 26 ]
Chapter 1
In the preceding example, we are selecting the students' records whose
student_id is 1.
In the preceding example, we are selecting the students' records whose
student_id is greater than 1.
In the preceding example, we are selecting the students' records whose
student_id is less than 4.
[ 27 ]
CRUD Operations, Sorting, Filtering, and Joins
In the preceding example, we are selecting the students' records whose student_id
is between 1 and 4, the between clause is inclusive, so the records with student_id
1 and 4 are also retrieved. The following table lists the common operators that can
be used for data filtering:
Operator
=
Explanation
Comment
!=
Filters and returns data where the
criterion doesn't have an exact
match.
<>
Filters and returns data where
the criterion doesn't have an
exact match.
>
Filters and returns data where the
data is greater than the value in
the criterion.
>=
Filters and returns data where the
data is greater than or equal to the
value in the criterion.
<
Filters and returns data where the
data is lesser than the value in the
criterion.
<=
Filters and returns data where the
data is lesser than or equal to the
criterion.
IS NULL
Filters and returns the rows where
the specified column has no data.
IS NOT NULL
Filters and returns the rows where
the specified column has some
data.
BETWEEN
Filters and returns data where the
data is part of the specified range.
Filters and returns data where the
criterion has an exact match.
This is same as above, based on
preference, either notations can
be used for inequality.
This uses the keywords
BETWEEN, and AND.
Data can also be filtered by utilizing multiple search criteria by using the AND
and OR operators, by employing multiple column search criteria, by using wildcard
filtering, by using the IN operator, and so on. As this chapter will only deal with
basic filtering, we will not be covering these advanced filtering concepts. The
basic filtering in this chapter can be used as a foundation to delve deeper into
understanding the advanced concepts of filtering.
[ 28 ]
Chapter 1
Updating data
Until now, we have worked with the creation of databases, tables, data, and retrieval of
data. Now let us go over the process of updating data, once the data has been added to
the table, there will be different cases where the data has to be updated, such as a typo
while adding the student's name, or if the student's address changes after they have
registered for the course, and so on. We will use the UPDATE DML statement to modify
the data. The UPDATE statement requires a minimum of three details, the first is the
name of the table on which this operation will be performed, the second is the name
of the column, and the third is the value that the column to has to be assigned to. We
can also use the UPDATE statement to modify more than one column at a time. There
are two cases where the UPDATE statement can be used. The first case is where all the
records in the table will be updated, and this has to be done very carefully as this could
cause the loss of existing data. The second scenario when using the UPDATE statement
is in combination with the WHERE clause. By using the WHERE clause, we are targeting a
very specific set of records based on the filter criteria.
It is recommended to execute the filter criteria with a SELECT
statement, so that we can verify the dataset on which our UPDATE
statement would run, in order to make any required changes if the
filter criterion does not reflect the expected results. Another way of
handling such scenarios is to use a transaction, which will allow us
to rollback any changes that we have made.
In the preceding example, we have updated John Dane's current city to Nebraska by
using his student ID. We can also verify this by looking at the output on the query
console, it returns that the filter criterion was matched for one row, and the update
statement was applied for that one row.
Deleting data
We will use the DELETE DML statement for deletion of data. The DELETE statement
at a minimum expects the name of the table. Similar to the UPDATE statement, it is
recommended that the DELETE statement is always used with filter criteria to avoid
loss of data.
[ 29 ]
CRUD Operations, Sorting, Filtering, and Joins
The DELETE statement should be used when a record has to be permanently removed
from the table.
To avoid permanent loss or deletion of data Boolean flags are used to
determine if a record is active or inactive (1 or 0). These are called soft
deletes and help us retain data in the long run.
In the preceding example, we are deleting the records from the students table
that match the criterion of student_id equal to 4. As there is only one record that
matches that criterion, that record has been deleted. The recommendations that were
made above about how to use the filter criterion apply for the DELETE statement too.
Joins
Until now, we have coupled our SELECT statements with various filtering and
sorting techniques to query the student information extensively. As we are operating
in a relational-model of data storage and since our data is stored in different tables,
we are yet to figure out how our SELECT statements can be fired across multiple
tables. In our case, this would help us find out what course or courses a student
has registered for, or to find our which course has the most number of students.
Following the relational-model of data allows us to store data in a more efficient
manner, allows us to independently manipulate the data in different tables, and
allows for greater scalability; however, querying the data across multiple tables
is going to be difficult when compared to retrieving records from a single table.
We will use JOINS to associate multiple tables, to retrieve, update, or delete data.
A SQL JOIN is a virtual entity and is performed at run time, during the execution
of the SQL statement. Similar to any other SQL statement, the data would only be
available during the query execution and is not implicitly persisted to the disk.
A SQL JOIN can be coupled with a SELECT statement to retrieve data from multiple
tables. Let us go through the most common JOIN: the INNER JOIN, a join based on
the equality comparison on the join-predicate.
[ 30 ]
Chapter 1
Let us look at a few examples that perform SQL INNER JOIN between two or
more tables:
In the preceding example, we are joining the students and students_courses
tables to retrieve a list of all the students who have registered for a course. This is
similar to the SELECT statements that we worked with earlier; a big difference is
that we can now add a column that is part of a different table. We use the INNER
JOIN clause to build the association between students and the students_courses
table where the values for student_id in the students table exist in the students_
courses table; this is referred to as the join-predicate. Now let us join all the
tables and retrieve the names of the courses for which each student has registered.
[ 31 ]
CRUD Operations, Sorting, Filtering, and Joins
In this example, we are creating an alias name for the name column
in the courses table. We are using the AS statement to explicitly
create a temporary alias to make the column name more intuitive.
We can build aliases for tables in a similar manner
In the previous example, we have joined the three tables that are available in our
course_registry database and are now able to retrieve the list of courses for
which the students have registered. Similar to our previous SELECT statements,
let us add a filter criterion to narrow down our search:
In the preceding example, we are filtering the data by student_id and are searching
for records with student_id equal to 2. We have discussed the most commonly
used form JOIN statement, which is the INNER JOIN or the equi-join. There are
other types of JOIN in SQL that are supported by MariaDB such as OUTER JOIN,
SELF JOIN, and NATURAL JOIN, we will be skipping these JOIN statements.
[ 32 ]
Chapter 1
Summary
In this chapter, we have covered the basics of relational database management
systems with MariaDB. We began by building our first database, and performed
Create, Read, Update, and Delete (CRUD) operations. We used the SQL SELECT
statement to retrieve data and used the ORDER BY and WHERE statements to sort
and filter the data respectively. Later, we moved on to use the UPDATE and DELETE
statements to modify and remove data respectively. Finally, we used the INNER JOIN
to retrieve data from multiple tables and coupled that with the WHERE statement to
filter that data.
In the next chapter, we will be going over more advanced topics such as creating
calculated fields and building complex views, stored procedures, functions,
and triggers.
[ 33 ]
Advanced Programming
with MariaDB
In the previous chapter, we have discussed basic operations that can be performed
on MariaDB such as creating databases and tables, adding data, modifying
data, deleting data, and retrieving the data. We have also worked with the basic
techniques of sorting and filtering data in order to work with the targeted and
specific datasets. In this chapter, we will work with the following advanced concepts:
•
Indexes
•
Stored procedures
•
Functions
•
Triggers
Enhancing the existing tables
Let's begin by making a few changes to the existing students table. We will be
adding two columns to the students table that would store a student's username
and password. This database will be used to support Student Portal that we will
build at a later point. The information available in the username and password fields
will be used to authenticate and authorize the student to login to the student portal.
There are a couple of ways to facilitate these changes; the first method is to use the
DROP TABLE DDL command to remove the existing students table and use the
CREATE TABLE DDL command to create a new students table that would have the
extra username and password fields. This method however causes loss of existing
data. The second method is to use the ALTER TABLE DDL command to add new
columns to the existing students table.
Advanced Programming with MariaDB
The following screenshot shows the usage of these commands for altering
the students table:
In this example, we have coupled the ALTER TABLE command with the ADD COLUMN
SQL command for adding new columns; we have separated the column definitions
with a comma (,). We are adding NOT NULL to the end of the column definition
to denote that these columns cannot carry null values. Now that we have added
these columns, it's time to populate the usernames and passwords for the existing
students. It is always recommended to store passwords in a hashed state in order to
provide security to the data and the users. We will use the SHA1 hashing algorithm
to hash the passwords, as shown in the following screenshot:
MariaDB provides a built-in function for generating a SHA1 hash value for a string.
As SHA1 is a static algorithm, it will produce the same hash as long as the same
string is provided. As SHA1 is a function by itself, we will have to use subqueries
to retrieve the encrypted string. Let's take a step back and look at an example as to
how to use subqueries as part of the regular SQL statements. Subqueries are SQL
statements that are nested within an existing SQL statement.
[ 36 ]
Chapter 2
Let's use a subquery to retrieve the first name and last name of a student who has
registered for the course CS-101 as shown in the following screenshot:
In the preceding example, we start with looking at all the available courses and get
student_id of the first student registered for the CS-101 course having course_id
as 1. Now we can use the output of this subquery as the value for the filter criterion
on the outer SQL statement. We will use this functionality in our update queries
to add the usernames and passwords for the existing students. We can also use an
INNER JOIN coupled with WHERE to retrieve the output of this query. The purpose of
this example is to introduce the readers to the concept of subqueries that will be used
to execute the SHA1 function.
We will be following the convention of having the "firstname.lastname"
pattern for a username and the "firstnamelastname" pattern for a
password. This pattern for the passwords is being used to keep the
examples simple and is not recommended in real-time scenarios.
[ 37 ]
Advanced Programming with MariaDB
Consider the example as shown in the following screenshot:
We will use the UPDATE DML statement to add the usernames and passwords for
the existing users. As discussed earlier, we are using the SHA1 function to hash the
passwords. Now that we have added the usernames and passwords, let's look at
how the data is stored in the database, as shown in the following screenshot:
In the preceding example, we are using the SELECT statement to retrieve the
first_name, last_name, username, and password fields for the existing users.
As we can see in the preceding screenshot, the username is stored as a string and
follows our "firstname.lastname" pattern, and the password has been successfully
hashed using the SHA1 hashing algorithm.
[ 38 ]
Chapter 2
SHA1 is a one-way hashing algorithm, so the data cannot be converted
back into its original format. For our authentication purposes, we will
take the student's password, hash it using the SHA1 algorithm, and
then compare the password that the student has entered during the
login and the password that is in the database.
From the result set that we have retrieved from the SELECT statement used in the
previous code, we notice that the data in the username field is unique for all the
available columns, but there is no guarantee that another student cannot choose a
username that already exists. MariaDB allows adding a unique key constraint in
order to maintain the uniqueness of the data that is being inserted into the username
column. The unique key constraint builds an index in the background to keep track
of all the values that are being added to the columns with unique constraints.
As the unique constraints use the unique index, the index looks for
the data to be unique; it can also be null. The null value will only hold
true for one record.
Let's use the ALTER DDL command to add a unique constraint to the username
column in the students table, as shown in the following screenshot:
In the preceding example, we are altering the existing students table and combining
the ADD CONSTRAINT command to give a user-defined name to the constraint, and
then we will be passing the username column to the unique function for creating the
unique constraint.
An alternate method to add a unique index to a column (after the table
has been built) is:
alter table students add unique 'username'('username');
[ 39 ]
Advanced Programming with MariaDB
Now that we have created a unique constraint, let's try and see if we can insert a
duplicate record. For this example, let's use the username of the student named
Richard Roe as shown in the following screenshot:
Upon execution of this SQL statement, we will receive an error saying that a duplicate
entry to be inserted into the username column was entered, which is now protected by
the uk_students_username constraint. Assuming that the initial line of SQL code was
correct, we would have a student record with the information available only for the
username column. In the previous chapter, we discussed that student_id is a unique
identifier for students. In order to facilitate this, let's make student_id the primary
key of the students table and let MariaDB know that this field always needs a value.
A primary key is similar to the unique key, except that the primary key does not allow
null values. The reason for having null to be an invalid value is that a primary key
always identifies each row in a table uniquely. Primary keys are commonly defined
in one column, as in our case in student_id; but in other cases, multiple columns can
be used to define a primary key. As in our case, it is common to use the non-changing,
non-repetitive integer values for the primary keys; as new students are added, we add
one to the last student's ID. Currently, we have three students; so, if another student
is added, the ID of that student will be 4. We can either do this manually or MariaDB
provides an auto-increment functionality that keeps on incrementing the value
of the last insert with a default value of 1 or with an incremental value, if the
incremental value is provided. The following screenshot shows the usage of the
auto_increment function:
In the preceding example, we are using the ALTER TABLE DDL command and are
combining that with the CHANGE command that allows us to modify the attributes
of an existing column. The first change is that the student_id column will not take a
null value. Then we are making this column an AUTO INCREMENT column, which that
will automatically assign the next available number to the student_id column.
[ 40 ]
Chapter 2
Finally, we are creating a primary key on the student_id column to make sure
that the IDs of the students will always be unique and not carry a null value.
The next step will be to alter the table one more time and make sure that the
value for next student_id will be 4, as we already have three students as shown
in the following screenshot:
In the preceding example, we are setting the value of auto_increment to 4,
and MariaDB handles value management for the student_id column from
here on. Let's insert another student but this time let's not insert a value for
the student_id column and see how MariaDB handles this case. The code is
as shown in the following screenshot:
In the preceding example, we are adding another student with the name Patrick
Smith. Let's perform a SELECT query to retrieve the student_id, first_name,
last_name, and username fields as shown in the following screenshot:
[ 41 ]
Advanced Programming with MariaDB
In the preceding example, we are retrieving the student records that are in the
students table, and we can notice that the last record has a student_id of 4.
And from here on, any student record that is added would automatically get
the next available number. We have made a lot of changes to the students table
in this chapter. Let's take a look at how the table structure has changed, as shown
in the following screenshot:
From the preceding result, we can notice all the changes that we have made; the first
change is the new columns: username and password. The other changes include
the unique key on the username column and the primary key on the student_id
column. Also, notice that the student_id column will not take null values and will
auto-increment the value upon each insert. Most of the SQL statements that we have
worked on until now are simple statements that access one or multiple tables, most
of which can throw SQL errors unless they are handled. When complex operations
that require multiple SQL statements are performed, we can either run each of these
statements one by one, or create a single unit that runs all these SQL statements in a
specified order. One method of creating a single unit of multiple SQL statements is
to use a stored procedure.
Stored procedures build a cache based on the connection that
is being established, which makes it tricky when used in the
client-server architecture. We will research a little more of this
behavior in Chapter 7, Caching.
[ 42 ]
Chapter 2
Working with stored procedures
Using a stored procedure, we can wrap multiple SQL statements into a single
unit that provides the integrity and consistency in which the SQL statements are
executed. Assume that there are multiple developers performing the same set of
tasks again and again, executing the same set of statements one at a time, in the
same or a different order based on the developer's preferences. The process can be
consolidated by putting these statements into a stored procedure. This single unit
can be thoroughly tested for maintaining data integrity and executing consistency
across different scenarios. Another reason why the stored procedures are preferred
over a set of multiple SQL statements is the performance boost that the stored
procedures provide. To build a stored procedure, we would need two things at a
minimum, the first being the name of the stored procedure and the second being
the body or the content inside the stored procedure. Let's write a simple stored
procedure that would print out Hello World!. MariaDB solely depends on the
statement delimiter (;) on when to execute the statements; since we will be dealing
with multiple SQL statements with a stored procedure, we will temporarily switch
the delimiter to something different, create a stored procedure, and then revert back
to the default delimiter, as shown in the following screenshot:
In the preceding example, we begin by changing the statement delimiter from; to
$$; use the CREATE PROCEDURE DDL statement, and provide the name of the stored
procedure. The name of the stored procedure is followed by (); any parameters
for a stored procedure will be placed in these parentheses. We have to use these
parentheses while declaring or calling a stored procedure irrespective of any
parameters for the stored procedure.
We will be using the "p_procedureName" convention for our stored
procedures.
[ 43 ]
Advanced Programming with MariaDB
The content of a stored procedure is placed in between the BEGIN and END
statements. These statements are used to scope the beginning and the end of the
SQL statements for a stored procedure. Once the stored procedure has been defined,
we will use the temporary delimiter to let MariaDB know that the stored procedure
definition is ready for compilation. Once the stored procedure has been compiled, we
reset the delimiter back to ;. We have successfully created our first stored procedure
and now is the time to execute it and verify the output. To execute a stored
procedure, we will use the CALL keyword, append it with the name of the stored
procedure, and pass parameters to the stored procedure, if any are required.
When we call our p_helloWorld() procedure, we should receive Hello World!
as the output on the screen, as shown in the following screenshot:
As we can see in the preceding example, we are successfully receiving the Hello
World! output upon execution of the p_helloWorld() procedure. Working with
stored procedures using MariaDB on the command line can get tricky sometimes, so
we have to be very careful while creating and executing the stored procedures. Now
that we have successfully created and executed a simple stored procedure, let's move
on to a slightly more complex example where we will create a stored procedure
that will take the first name, last name, address, city, state, zip code, username, and
password as input parameters. The stored procedure would be intelligent enough
to check the students table to see if there are any students with a similar username;
if there are any, it would alert the user. If not, the procedure would use these input
parameters to create a new student record.
[ 44 ]
Chapter 2
The code is as shown in the following screenshot:
We will be looking at four new concepts using this stored procedure: the first is the
concept of defining multiple input parameters that are expected to be passed in by
the user, the second will be declaring a variable inside a stored procedure, the third
will be to store data into that variable, and the fourth concept will be to work with
conditionals based on the value of the variable.
Consider the code in the following screenshot:
[ 45 ]
Advanced Programming with MariaDB
In the preceding code, we are defining our procedure by giving the name of the
stored procedure. This stored procedure accepts eight input parameters that will
be used to create a new student's record.
We will be using the "pa_columnname" convention for our
input parameters.
The next snippet of code will deal with the creation of a variable inside a stored
procedure. We will use the DECLARE keyword to create a procedure variable called
ct_username that will keep a track of the count; that is, the number of times a
username has been stored in the students table. As our variable will keep a track
of the count, we will declare the datatype for this variable as an integer. As we are
under the assumption that a new student is being added to our students table, we
will default the value of ct_username to 0, as shown in the following screenshot:
Once we have declared the variable, the next step will be to retrieve the number of
times the username that is being passed in via pa_username has been used in the
students table, and store that username in the ct_username variable as shown in
the following screenshot:
Now that we have the count of the username that is being passed in as a parameter,
we can use the count in the ct_username variable for making an informed decision;
either to create a new record and give the user a success message or, if the username
already exists, send an alert message back to the user. To perform this conditional
check, we will be using the IF THEN, ELSE, and ENDIF constructs. We will begin by
passing in a condition to the IF statement and use the THEN statement to indicate that
when this condition is true, the block of SQL statements pertaining to the success
case have to be executed.
[ 46 ]
Chapter 2
We can also have an optional ELSE block to handle a situation where the IF condition
is false as shown in the following screenshot:
In the preceding code snippet, we are using the ct_username variable that has been
populated in the previous step, and are making sure that there are no occurrences of
that username. If the count that is returned is less than one (that is, zero), then SQL
syntax in the IF block is executed and a new student record is created. If the count
that is returned is not less than one (equal to or greater than one), the SQL statements
in the ELSE block are executed, and the message is displayed onto the console. Let's
take a look at the output when we call this stored procedure and provide the new
student's information as shown in the following screenshot:
[ 47 ]
Advanced Programming with MariaDB
As seen in the preceding screenshot, we get a success message saying Student has
been successfully added. Now try and run the same call again and this time it
will fail, as there is already a student record with the user william.dice. Whenever
a new record is added to the students table, the AUTO INCREMENT functionality
increments student_id, which is the primary key, by one. Now we know that
student_id for William Dice will be 5, because there were four students ahead
of him. However, when we are dealing with bulk imports, it will become very hard
to manually keep a track of the last student's ID that was added. MariaDB provides
a last_insert_id() function, using which we can retrieve the last successfully
inserted ID for an AUTO INCREMENT column.
Use select last_insert_id(); to print the last inserted ID.
In order to add this to our p_insertStudents() stored procedure, we will have
to drop the existing stored procedure and create a new one with the updated code.
Though MariaDB provides the ALTER PROCEDURE functionality, we can modify
any characteristics of the procedure such as the definer or comment, but we cannot
modify the parameters or the body of the stored procedure. Assuming we have all
the required permissions, let's drop the procedure and rebuild the whole procedure
again as shown in the following screenshot:
It is recommended to use DROP PROCEDURE IF EXISTS
p_procedureName; to avoid an error if the procedure does not exist.
Now, let's make the required changes to the previous p_insertStudents procedure
to add an extra parameter that would send the value of the last inserted ID out of
the stored procedure. We will use the OUT keyword prepended to the parameter to
denote that data will be retrieved out of the procedure using this new parameter as
shown in the following screenshot:
[ 48 ]
Chapter 2
There are two changes that have been made to the previous p_insertStudents
procedure. Let's examine both carefully shown as follows:
[ 49 ]
Advanced Programming with MariaDB
The first addition to the procedure is the new OUT parameter of datatype INT.
This parameter will be used to send the last inserted ID out of the stored procedure
shown as follows:
The second change that we have made is to store the last inserted ID into our OUT
parameter, which is pa_student_id. Now let's create this procedure and call it as
shown in the following screenshot:
When working with OUT parameters, we will be using variables that are outside
the stored procedure and are only passed into the stored procedure to retrieve the
data. Session variables begin with a @ symbol unlike procedure variables that are
declared inside stored procedures. While calling the stored procedure, we are giving
the required information about the user and then using the @student_id session
variable to retrieve the student's ID from our stored procedure. Commonly, we
would need to declare the session variable and assign a datatype to that variable
but since this will be used as an OUT parameter, the characteristics such as the
datatype are applied by the stored procedure itself. Once the procedure is executed
successfully, the value is stored in the @student_id variable. We will run the SELECT
command to retrieve the value from this session variable.
[ 50 ]
Chapter 2
The third type of parameter that can be used with stored procedures
is the INOUT parameter where the parameter that carries that data
can also carry out the modified data.
Working with stored routines
Stored routines are similar to stored procedures; both of them contain a block of
SQL statements. There are a few differences such as a stored routine cannot return a
result set and that a stored routine has to return a value and therefore, not preferred
over stored procedures. Stored routines are invoked using the SELECT statement and
can interchangeably be called as functions. The SHA1 function that we are using is a
system-built stored routine to generate hashes for strings. Let's build a simple stored
routine that would return the full name of the student when a username is passed in
as a parameter, as shown in the following screenshot:
We will be using the fn_functionName convention for our
stored routines.
[ 51 ]
Advanced Programming with MariaDB
We begin by using the CREATE FUNCTION DDL command, appended by the name
of the function, to create the function. This function takes the student's username
as a parameter and returns the full name of the student, if the student exists. In the
function, we begin by declaring a function variable called FullName, defaulted to the
User does not exist message, which would be used if no student record is found.
If the student record is found, we would retrieve the first name and last name of the
student and use the CONCAT function that is provided by MariaDB to concatenate
multiple strings. We are using the return keyword to return the data back, when the
function is invoked. Let's invoke the function and pass a username that exists in the
students table shown as follows:
In the preceding example, we are passing in the username of john.doe who is a
valid student. Upon execution, we retrieve the valid full name of the student; that is,
John Doe. Now, let's test a failure case where we pass in a username that does not
exist in the students table as shown in the following screenshot:
In the preceding example, we have deleted the last character from the original
username of John Doe. Upon execution, we get the default message that says User
does not exist, which is true. The final example that we will work with in the
stored routines is to drop the stored routine as shown in the following screenshot:
[ 52 ]
Chapter 2
We are using the DROP FUNCTION command to drop a stored routine.
It is recommended to use DROP FUNCTION IF EXISTS
fn_functionName; to avoid an error if the function does not exist.
Working with triggers
In the last few sections, we have looked at different scenarios for adding, updating,
and deleting data. These are considered to be common operations in an everyday
environment. However, what if we would like to watch certain events and use these
events to perform another operation? As in, have an audit table that keeps a track of
the user on whom an operation was performed or, assuming that there is a limit on
the number of students that can register for a course, subtract that number whenever
a student registers for the course. In our case, let's take a look at how an audit table
can be used to keep track of different operations on a user. MariaDB provides the
TRIGGER statement, which is a chain reaction that is set off in response to a SQL DML
operation such as INSERT, UPDATE, or DELETE. To track these changes, let's build an
audit table that would hold the person who performed the operation, the time when
the operation was made, the type of the operation (INSERT, UPDATE, or DELETE),
and the username on which the operation was performed, as shown in the
following screenshot:
We will use the audit_students table to house the information about all the
operations that are made on the students table. We will begin by creating a
trigger that would track the INSERT operations on the students table.
[ 53 ]
Advanced Programming with MariaDB
For successfully creating a trigger, we would need at least four pieces of information;
the first is the name of the trigger, which has to be unique; the second is the table with
which the trigger will be associated; the third is the operation that fires our trigger;
and the fourth is if the trigger is fired before or after the operation shown as follows:
We will be using the ti_triggerName convention for our triggers
that will be fired upon inserts.
To create a trigger, we will use the CREATE TRIGGER DDL statement and append
this with the name of the trigger. After we give the name for our trigger, we are
mentioning when the trigger will be fired, which is after INSERT has been made on
the students table. On the next row, we let MariaDB know how often the trigger
should be fired, and will be fired on every row, shown as follows:
After this comes the body of the trigger where we will be keeping track of the SQL
user who performed this operation, the time of the operation, the kind of operation,
and finally the student on whom the operation was performed. Similar to a stored
procedure and a stored routine, the body of the trigger will be inside the BEGIN and
END statements, shown as follows:
[ 54 ]
Chapter 2
Inside the body of the trigger, we are performing an INSERT statement that would
store the information about this operation, and we are storing that information in
the audit_students table. The changed_by column tracks the SQL user that has
performed this operation. There can be multiple users who have access to login into
the MariaDB system and who are performing different operations based on their
access rights. MariaDB provides the USER() function to retrieve the person who is
currently performing the operation. Let's take a quick look at how this function can
be used, shown as follows:
Now that we know who the SQL user is on MariaDB that is performing this operation,
we will have to retrieve the time when this operation took place. The changed_at
column tracks the time when this operation was performed, MariaDB provides the
NOW() function to retrieve when this operation was made, shown as follows:
Now that we have the user and the time when this operation was made, we will use
the letter I to denote that the operation that was made was an INSERT operation.
MariaDB stores a copy of old or existing data in an alias called OLD and the new data
in another alias called NEW. Since this is an INSERT operation, there will not be any
data in OLD, but NEW will carry the student record that has just been added. We can
use the dot (.) notation to access the data from NEW, so it will be NEW.columnname.
We will use single-letter abbreviations for the operations that we are
making: I for inserts, U for updates, and D for deletes.
[ 55 ]
Advanced Programming with MariaDB
Upon successful creation of the trigger, let's test this trigger by inserting a record into
the students table, shown as follows:
We begin by testing if the audit_students table is empty and then use the INSERT
statement to add a new student record with robert.senna as the username. Once
the INSERT statement has been successfully executed, let's query the audit_students
table to see if our trigger was fired after INSERT. Upon querying the audit_students
table, we can see that a record was added after the user was added to the students
table. Now, let's move on to the trigger that will be fired when an UPDATE statement is
executed upon a record on the students table, shown as follows:
We will be using the tu_triggerName convention for our triggers
that will be fired upon updates.
[ 56 ]
Chapter 2
This trigger is similar to the earlier trigger; we begin by using the CREATE TRIGGER
DDL statement. As part of the creation, we are expecting this trigger to run after a
successful update on the students table. This trigger contains the same query as the
insert trigger because we are only looking for the username on whom the update has
been performed.
It is recommended to store complete record information, as it is hard to
identify the exact column upon which the update was performed.
Once the trigger has been successfully created, let us make an update to the
students table and see if our trigger is fired upon the update, shown as follows:
In the preceding example, we are performing an update on the record that has a
first_name as Robert. As we only have a single record with first_name as Robert,
this would only update one record. Upon updating Robert's address, we have
queried the audit_students table and the second record carries the information
about the user who made the update, when this update was made, and on what
record was the update made.
It is always recommended to use the WHERE condition with a
column that has been indexed; in our case, either student_id,
which is the primary key or the username, which has a unique
index. The first_name column has only been used for descriptive
purposes, as this can cause multiple updates if there is more than
one student with Robert as their first name.
[ 57 ]
Advanced Programming with MariaDB
Now, let's look at our last example that would be the trigger that is fired upon
deletion of a student record, shown as follows:
We will be using the td_triggerName convention for our
triggers that will be fired upon deletes.
In the preceding example, we are creating a trigger that will be fired before a student
record is deleted. This trigger is similar to the earlier triggers; the big difference is
that this trigger will be fired when a student record is deleted. The other difference is
that the single letter abbreviation is D to denote deletion; since a student record will
not exist after the delete, the NEW alias here will be empty and we use the OLD alias to
retrieve the required information.
Triggers that are fired on UPDATE statements will have both
NEW and OLD aliases populated.
Now the trigger has been successfully created, let's delete a record from the
students table and see if our trigger is fired upon the delete, shown as follows:
[ 58 ]
Chapter 2
In the preceding example, we are deleting the student record with first_name
as Robert. Upon successful deletion of this record, let's query the audit_students
table; we can see that a third record is added having type as D that denotes
deletion of a record, and the record that has been deleted has a username of
robert.senna. The final example that we will work with is to drop the trigger.
We will use the DROP TRIGGER DDL command to drop a trigger, shown as follows:
Summary
In this chapter, we have covered a lot of advanced concepts such as altering tables,
working with indexes and column characteristics, working with stored procedures,
working with stored routines, and working with triggers. MariaDB offers many
more advanced concepts and the concepts discussed in this chapter will lay a
good foundation to acquire a better understanding of the more advanced database
concepts. Now that we have talked about a few advanced concepts of MariaDB, let's
switch gears and dive in to understand a popular programming paradigm called
object-oriented programming and understand its implementation in PHP 5.
[ 59 ]
Advanced Programming
with PHP
The most recognized minor version of PHP 5, in the last few years, is PHP 5.3, which
has been widely accepted and is used currently. After PHP 5.3, two other minor
versions of PHP, PHP 5.4, and PHP 5.5, have been released. Though a lot of hosting
providers are still sticking with PHP 5.3, a few providers have started updating their
PHP version to PHP 5.4. For users who do not depend on hosting providers, we can
use the current version of PHP, PHP 5.5. There are a vast number of resources in
books and on the Internet discussing the features of PHP 5.2 and 5.3, and very few
resources discussing PHP 5.4 and 5.5. We will begin this chapter by learning a few
new features that have been shipped out with PHP 5.4 and PHP 5.5.
For more information about the object-oriented programming with PHP,
please refer to the Bonus chapter 2, Object-oriented Programming with PHP.
New features in PHP 5.4 and 5.5
A lot of new features have been added to PHP with PHP 5.4 and 5.5. Most of these
features were originally part of the PHP 6.0 release, which had to be postponed as
rewriting PHP to support Unicode did not go as planned. Unicode is an industry
standard character encoding set that supports most of the world languages, unlike
ASCII that only encodes the Latin alphabet. One prominent issue that the developers
faced during rewriting languages for Unicode support was that it almost took twice
the runtime memory to execute the scripts. A few new features that have been
shipped with PHP 5.4 and 5.5 are the ability to monitor upload progress, multiple
improvements to arrays, a built-in web server, the password hashing API, the
generators, partial Unicode support, updates to closures, and the powerful traits.
PHP 5.4 and 5.5 arrives with multiple updates that will help us execute scripts faster
and use less memory.
Advanced Programming with PHP
Updated array declaration
Short array syntax has been added to PHP 5.4 and we can now use the square
brackets to declare arrays. Prior to this we would have had to use the array()
language construct to declare and add elements to the array.
The following code is available in the array-declaration.php file, present in
the code bundle:
In this example, we begin by looking at how an array declaration is done in the
versions before PHP 5.4. We use the array() language construct and supply the
array elements as arguments. In the second snippet, we are using the square brackets
or the short syntax that has been shipped with PHP 5.4 to create an array. This syntax
has been available in other scripting languages for a good amount of time, and this is
a welcome addition to PHP. Let us execute this script and examine the output.
The output is of the previous code snippet is as follows:
1
1
Both the snippets print the first element of the array. If this script was executed
with PHP 5.3, we would get a parse error as the engine encounters an unexpected
square bracket.
[ 62 ]
Chapter 3
The array dereferencing function
Array dereferencing has been added to PHP in PHP 5.4. Let us utilize this feature
for retrieving elements from arrays that are returned by functions and/or methods.
Prior to PHP 5.4, we would have had to store the array that is being returned by
a function into a local variable before accessing an element. The following code is
available in the array-dereferencing.php file, present in the code bundle:
[ 63 ]
Advanced Programming with PHP
In this example, we begin by defining the retArray() function. This function returns
an array of letters when invoked. The first snippet shows how the first element can
be retrieved if we are using a PHP version prior to 5.4. In the second snippet, as array
dereferencing for functions has been added in PHP 5.4, we can directly retrieve the
element in a single step. Let us execute this script and examine the output.
The output of the previous code snippet is as follows:
a
a
The list() function in the foreach statement
One of the most common statements used to loop over an array of elements is the
foreach statement. Prior to PHP 5.5, unpacking a nested array would have had to
be done manually by referencing the index of that element. An interesting addition
to PHP 5.5 is the ability to use the list() function to break a nested array into local
variables in a foreach statement. Before we take a look at how the list() function
and the foreach statement can be used together, let us get a quick refresher as to
how the list() function works.
The following code is available in the list.php file, present in the code bundle:
The list() function is a language construct used to assign a list of variables in
a single operation. In this example, we begin by assigning an array of numbers
to a list of variables using the list() languages construct. In the next step,
we are echoing these variables to the screen.
The output of the previous code snippet is as follows:
1 2 3
The list() language construct has been around since PHP 4; with PHP 5.5,
we can use it with the foreach statement.
[ 64 ]
Chapter 3
The following code is available in the foreachlist.php file, present in the
code bundle:
In this example, we begin by creating a list of students; each student is a list of
attributes such as the first name, last name, and age. In the first snippet, we are not
using the list() construct, so we will have to pick each element from every student
by the index. With PHP 5.5, we can now use the list() construct with the foreach
statement to directly load the values into temporary variables. When we execute this
script, the output will be the same for both of the snippets; the main difference here
is that the second snippet is easier on the eyes as we are dealing with variable names
that represent the data stored in that variable. Let us execute this script and examine
the output.
[ 65 ]
Advanced Programming with PHP
The output of the previous code snippet is as follows:
John
Jane
John
Jane
Doe 101
Dane 102
Doe 101
Dane 102
As expected, the output of both the snippets is the same.
Availability of $this in closures
Until PHP 5.4, when closures were declared in a class, they would have been
considered as anonymous functions that would not have access to the surrounding
properties and methods. With PHP 5.4, the anonymous functions will behave as
closures where they will have access to the surrounding properties and methods
via the $this instance variable.
The following code is available in the closures.php file, present in the code bundle:
name;
};
}
}
$student = new Student();
$name = $student->getName();
echo $name()."\n";
?>
[ 66 ]
Chapter 3
In this example, we are creating a closure to return the value of the $name property.
Prior to PHP 5.4, though closures existed, they did not have access to the $this
instance variable. Let us execute this script and examine the output.
The output of the previous code snippet is as follows:
John Doe
Class member access on instantiation
The next feature that we will be looking at is the ability to access members of a class
upon instantiation. For this example, we will work with the range(), rand(), min(),
and max() functions. The range() function takes a minimum of two values as
parameters to return an array of elements between those values. The rand() function
returns a random number when the minimum and maximum limits are passed in.
The min() and max() functions return the least and the maximum values in an array.
The following code is available in the classMemberAccess.php file, present in
the code bundle:
studentIds = range(1, 500);
}
function getRandomStudent(){
return rand(min($this->studentIds),
max($this->studentIds));
}
}
[ 67 ]
Advanced Programming with PHP
//Before PHP 5.4
$student = new Students();
echo $student->getRandomStudent()."\n";
//Using PHP 5.4 or greater
echo (new Students())->getRandomStudent()."\n";
?>
In this example, we are creating a Students class. This class carries the $studentIds
property that would store the ID of all the students. For this example, let us populate
this property using the range() function. The aim of this script is to retrieve a
random student and print his/her ID; we will be using the getRandomStudent()
method to retrieve the random student's ID.
We can extend this example to create a system that would assign
a different set of question papers based on the picked student's
ID. This will not be a part of this book, but readers are welcome to
try this example.
In the first snippet, we are creating an instance of the Students class and are assigning
it to a local variable. After that we use the local variable (an object of that class) to call
the getRandomStudent() method. In the second snippet, we are creating an instance
and then using that instance on-the-fly to call the getRandonStudent() method.
Before PHP 5.4, this was a two-step process where we would create an instance
and assign that to a local variable, and then use the local variable to call a method;
however, with PHP 5.4, we can directly call the method in a single step.
The output for the previous code snippet is as follows:
332
224
As we are generating these student's IDs using the rand() function, the IDs
are not the same.
[ 68 ]
Chapter 3
Generators
A generator is very similar to a function that returns an array, the difference being
(as the name suggests) that a generator generates a sequence of values. In PHP, we will
have to implement the Iterator interface for object iteration, which can sometimes get
a little tedious. A generator already implements the Iterator interface, so this reduces
the complexity of building an iterator. The first difference between a function returning
an array and a generator is that the generator uses the yield keyword.
The following code is available in the student-generator.php file, present in the
code bundle:
In this example, we are yielding three students via the student() generator. Later,
we are running our generator through the foreach construct to retrieve one student
at a time. The difference between returning data via a function and yielding the data
with a generator is that a generator will retain the state and yield the next value
when it is used again. This behavior is unlike a function, where it would return the
whole array upon invoking again. Let us execute the script and examine the output.
The output for the previous code snippet is as follows:
John Doe
Jane Dane
Richard Roe
[ 69 ]
Advanced Programming with PHP
Upon execution, the script will print the names of each student on the screen,
but this looks similar to what we would do with a return statement. Generators
are commonly used to perform tasks that apply to large datasets; an example would
be a set of operations on a file that has over a million lines. If we would handle a
file with so much data, a lot of memory would not only be dedicated to store the
file in memory, but also the PHP arrays that would store that data. PHP arrays
are very expensive as they are ordered HashMaps that provide on-demand access
to data, making it resource-intensive. Ordered HashMaps are associative arrays,
which are internally used to store data on the RAM. Let us now look at a real-time
implementation of generators to handle the file operations.
This example is just for demonstration purposes as providing a file
that is over 800 MB is unrealistic.
The following code is available in the file-reader.php file, present in the
code bundle:
In this example, we are building a generator to return the data from a large file
and then we perform our file operations one line at a time. By using generators,
not only do we save memory but, as the data size gets larger, the execution time
compared to other methods will be far shorter.
[ 70 ]
Chapter 3
Traits
In Bonus chapter 2, Object-oriented Programming with PHP, we discussed object-oriented
concepts in PHP and worked with inheritance to understand how functionality in one
class can be extended to other classes and how common functionality can be shared
among different subclasses. With PHP 5.4, we will go over a new concept called Traits
that facilitates using the functionality from more than one source at a time. If we would
have to achieve this prior to PHP 5.4, we would have ended up duplicating the code
into multiple classes as required by making our code difficult to work with. This is
commonly referred to as horizontal inheritance. To declare a trait, we will use the
trait keyword followed by the name of the trait; the functionality for the trait will be
placed inside the curly braces next to the trait.
The following code is available in the trait.php file, present in the code bundle:
helloWorld();
$obj->helloWorldFromTrait();
?>
In this example, we have two classes and a trait. The subclass is already extending
the functionality of the base class. When a subclass would like to use the functionality
of another entity other than its base class, we can house such reusable functionality
in a trait and let the subclasses use the trait. To use the functionality from a trait,
we will use the keyword use. Let us execute this script and examine the output.
The output of the previous code snippet is as follows:
Hello World from Base Class
Hello World from Trait
To use multiple traits, we will use the comma separator to add more than one
trait to a class.
Here is an example of the syntax to be followed:
class SubClass extends BaseClass{
use Trait1, Trait2, Trait3;
}
This code is only for demonstration purpose and should not be
executed by adding the required class and traits.
Traits are a very powerful feature and help us in making our code more reusable
and object-oriented. A real-time implementation of traits is to create a singleton trait
that can convert any class into a singleton when used. Given the numerous ways of
working with traits, they will certainly be one of the popular additions to PHP 5.4.
[ 72 ]
Chapter 3
Addition of the finally block to exception
handling
In this section, we will take a quick look at exception handling and the addition of
the finally block in PHP 5.5. Exception handling is commonly used to alter the
flow of execution when a specified condition occurs. Exceptions can be caused due
to different events such as a file not being available to be used, a faulty database
connection, or just bad code. By using an exception handling strategy, we will be
able to predict any exceptions that might occur and handle these exceptions in a
graceful method. In PHP, the exceptions are thrown during the code execution and
to catch these exceptions we will use the try block and add our code into the try
block to catch the error. If the exception is not caught and handled, a fatal error will
occur that will halt the execution of our script.
The following code is available in the exceptionHandling.php file, present in the
code bundle:
getMessage();
}
}
echo divide(4,0)."\n";
?>
[ 73 ]
Advanced Programming with PHP
In this example, we begin by defining the divide() function that would take two
integers as parameters and recreate the Divide By Zero exception. We will place the
code that is performing the division into the try block. In our code, we are checking
to see if the value of the second parameter that is being used as the denominator is
equal to zero. If the condition is a success, we will throw a new exception and pass
the message. The catch block that is placed right after the try block will catch this
exception and return the exception message back to the execution.
The output of the previous code snippet is as follows:
Divide by Zero Exception
Until now we can either throw an exception or catch an exception; with PHP 5.5,
we can use the finally block to handle any kind of closing operations. The finally
block is executed after the try and catch blocks are executed. One common
example is deleting any file resource links that were created during the try block.
Another example is unlocking tables that have been locked or closing an open
database connection.
The following code is available in the exceptionHandlingWithFinally.php file,
present in the code bundle:
getMessage()."\n";
}
finally{
//perform clean up operations
echo "executed after try & catch \n";
}
}
divide(4,0);
?>
[ 74 ]
Chapter 3
We are continuing with the example that we used earlier, and have added
the finally block that can be used to perform any sort of clean-up operations
as required.
The output of the previous code snippet is as follows:
Divide by Zero Exception
executed after try & catch
The goal of exception handling is for our application to run without crashing or
throwing errors. Although it is an ideal situation for the application to perform
without crashing, exception handling helps us get our application closer to
performing without crashing. All applications are built with a set of assumptions;
so as long as the users proceed along an expected route, the application will
perform as expected. And when a user performs an unexpected action, the PHP
engine sends an unexpected response back. Exception handling helps us to handle
such unexpected responses in a graceful manner. As good programmers, it is very
important for us to predict and identify such cases.
Unit testing
Now that we understand exception handling, this is a good place to take a look at
the concept of unit testing. As the name suggests, unit testing refers to testing the
application one unit at a time. A unit is an arbitrary term but it is always advised to
divide the code into the smallest independent working fragment and test that unit.
In this section, we will briefly go over automated unit tests. Automated unit testing
makes the process of testing the functionality easier as the application grows. Let us
take the example of a student portal. We begin by building a simple portal that will
allow us to add a student. Once we provide the portal to add students, the users of
the portal would need an interface to view the student information that they have
added. Once we provide this interface, they might request the development team for
a lot more features. As we keep adding features, the amount of functionality that has
to be tested will increase, and sometimes the code that has been added to support one
functionality might either break or not coexist with the code of another functionality.
Normally these issues are identified by regression testing, and automated unit testing
helps the developers to understand and predict where a code fix might cause issues
at another location. We will be using the PHPUnit testing framework, which is an
instance of the xUnit architecture for unit-testing frameworks.
[ 75 ]
Advanced Programming with PHP
Installing PHPUnit
The installation process for PHPUnit is pretty simple and we will install PHPUnit
v3.7, which was the stable version while writing this book. The installation
commands will be same on all operating systems, as we will use the PHP Extension
and Application Repository (PEAR) to get and install the PHPUnit libraries. PEAR
is a distributed code repository that is used to maintain common code packages, and
other developers and development teams can use the pear command to download
them onto their local environments.
These commands should be run in the terminal window on a
Linux or Mac OS X operating system or in the command prompt
window on the Windows operating system.
Here are the two lines of command we will need here:
pear config-set auto_discover 1
pear install pear.phpunit.de/PHPUnit
Using the first command we are setting up the configuration to allow PEAR
to automatically discover new channels from the command line or to look for
dependencies that are required while installing a library. With the second command,
we are installing the PHPUnit framework, which also installs other dependencies such
as the File_Iterator library, the PHP_Timer library, and the PHP_CodeCoverage
library. Upon running these commands, the PHPUnit framework and its dependencies
will be successfully installed. To verify the installation, we can run the phpunit
command and add the option to print out the version of PHPUnit that we have
installed. After this, the following code needs to be run:
phpunit --version
This has to be run using the same command-line utility that was used earlier to
install the PHPUnit library.
The output for the previous command line is as follows:
PHPUnit 3.7.32 by Sebastian Bergmann.
Upon execution of the command, the version 3.7.32 will be printed onto the screen.
Sebastian Bergmann is the creator of PHPUnit. Now that we have successfully
installed PHPUnit, we are all set to write our first test.
[ 76 ]
Chapter 3
The following code is available in the exampleTest.php file, present in the
code bundle:
assertTrue(true);
}
public function testCount(){
$array = [1,2,3,4];
$this->assertCount(4, $array);
}
}
?>
There are three important things to note from our test case (exampleTest.php):
•
The test case will always inherit from PHPUnit_Framework_TestCase.
•
The tests inside the test case will always be public and are named test*.
We can also use the @test annotation in the method's doc block.
•
As part of conventions, the name of the test case and the filename carrying
the test should always end with test.
The tests inside a test case will carry an assert method that is used to ensure
that the data that is being passed in matches the expected result. In our tests,
we are looking at two different assert methods; the first assert method checks
to see if the value being passed in is true, while the second assert method ensures
that the array length is equal to the expected value that has been passed in.
To run a test case, use the phpunit command that we have used
earlier in phpunit exampleTest.php.
[ 77 ]
Advanced Programming with PHP
The output for the previous code snippet is as follows:
PHPUnit 3.7.32 by Sebastian Bergmann.
..
Time: 80 ms, Memory: 1.25Mb
OK (2 tests, 2 assertions)
We have successfully asserted both our tests; when the message says that there are
two tests and two assertions, it means that both of our tests have been asserted or
have passed. Now let us build a custom class with a single method and then come
up with a test case that would have multiple tests focusing on that method. Just to
verify the output message for a failed test, let us add a test that would fail.
The following code is available in the Math.php file, present in the code bundle:
This is a very basic class that has an add() method that takes two numbers
as input and returns their sum. Now let us create a test case for the Math class.
The following code is available in the MathTest.php file, present in the code bundle:
assertEquals(5, (new Math())->add(2,3));
}
public function testAdd2(){
$this->assertNotEquals(6, (new Math())->add(2,3));
}
public function testAdd3(){
$this->assertEquals(4, (new Math())->add(2,3));
}
}
?>
In this test case, we have three tests that are passing in numbers two and three to
the add() method and are testing to see if the expected value matches with the
result. The first two tests will be successful, as the first test is asserting to see if
the value returned by the method is equal to five. Upon execution, this will be a
successful test. The second test is asserting to see if the value returned by the method
is not equal to six. Upon execution, this test will be successful too. In the last test,
we are asserting if the value returned by the add() method is equal to four.
This will fail as the value returned will be five.
The output of the previous code snippet is as follows:
PHPUnit 3.7.32 by Sebastian Bergmann.
..F
Time: 89 ms, Memory: 1.50Mb
There was 1 failure:
1) MathTest::testAdd3
Failed asserting that 5 matches expected 4.
/var/www/UnitTesting/MathTest.php:21
FAILURES!
Tests: 3, Assertions: 3, Failures: 1.
[ 79 ]
Advanced Programming with PHP
As expected, the last assertion failed while the other two were successful. Refactor
the code to remove the last assertion and utilize the documentation available on the
PHPUnit website to discover more about how to implement unit testing in your
projects. Unit testing might appear to add more time in the development of a project
or a feature. However, this will help in reducing the total time taken by us to manually
test the whole application every time a new feature is added. PHPUnit also comes with
multiple other features such as custom bootstrapping that would allow us autoload
the required files rather than using require or require_once; another popular feature
is code coverage that helps us find dead code. Dead code is code that still exists in
our project but isn't being used. There are numerous features provided by PHPUnit
and a good place to understand and read about these features is the official website of
PHPUnit at http://phpunit.de/manual/current/en/.
Working with MariaDB
So far we have worked with core programming concepts of PHP and we have also
worked with the MariaDB database server in Chapter 1, CRUD Operations, Sorting,
Filtering, and Joins and Chapter 2, Advanced Programming with MariaDB. In this section,
let us focus on setting up communication between PHP and MariaDB. PHP provides
three APIs to connect to MariaDB; they are as follows:
API
mysql
Description
Comment
This is probably the most used API to connect to
MySQL and MariaDB databases. This API has been
around since PHP 2.0. Active development for this
API has been stopped and it is not advised to use
this API in any of new projects.
The mysql API
has been deprecated
since PHP 5.5.0 and
will be removed in
the future.
mysqli
MySQL Improved is the new API that has been
introduced with PHP 5. This API is a huge upgrade
over the last API. This API supports features such
as client-side and server-side prepared statements,
stored procedures, and transactions. This API has an
object-oriented interface and a procedural interface.
The mysqli API
stands for MySQL
Improved.
PDO
PDO is an object-oriented interface to work with a
database. PDO can support a wide range of databases
as this provides the flexibility to move to other
database management systems at some point in
the future.
The PDO API stands
for PHP Data Objects.
[ 80 ]
Chapter 3
We will go over examples for both mysqli and PDO in this section
and it is recommended to go over the API documentation provided at
http://www.php.net/manual/en/mysqlinfo.api.choosing.php to
make an informed decision.
PHP – mysqli
Though the mysqli API supports both procedural and OOP interfaces, we will
use the OOP interface for this example. To work with this example, let us create
an employee database with a few employee records.
The following code is available in the employees.sql file, present in the
code bundle:
CREATE DATABASE IF NOT EXISTS `employee_db`;
--- Database: `employee_db`
--- -------------------------------------------------------USE `employee_db`;
--- Table structure for table `employees`
-CREATE TABLE IF NOT EXISTS `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(60) NOT NULL,
`last_name` varchar(60) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--- Dumping data for table `employees`
-INSERT INTO `employees` (`id`, `first_name`, `last_name`) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Dane'),
(3, 'Richard', 'Roe');
[ 81 ]
Advanced Programming with PHP
Now that we have created the required data in the employee database, let's use
the mysqli API to connect to the database and fetch the employee records.
The following code is available in the php-mysqli.php file, present in the
code bundle:
connect_error) {
trigger_error("Database connection failed: "
E_USER_ERROR);
. $conn->connect_error,
}
//query to retrieve employees
$sql = "select id, first_name, last_name from employees;";
//execute the query
$result = $connection->query($sql);
//check if result is valid
if($result === false){
trigger_error("Sql Error, verify SQL", E_USER_ERROR);
}
//iterate over the result
while($row = $result->fetch_assoc()){
echo $row['id']." ".$row['first_name']." ".$row['last_name']."\n";
}
?>
[ 82 ]
Chapter 3
In this example, we begin by storing the connection parameters to the database in the
constants and then we create a mysqli object and use the connection parameters to
establish a connection. In the next step, we verify if the connection was successful.
If this isn't true, we will trigger an error so that appropriate steps can be taken.
In the next step, we will query the database to retrieve the available employee
records and then we will iterate over the retrieved record set to print the
information about each employee.
The output for the previous code is as follows:
1 John Doe
2 Jane Dane
3 Richard Roe
PHP – PDO
In this section, we will use the PDO API to fetch all of the employee records from
the employees table.
The following code is available in the php-pdo.php file, present in the code bundle:
setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
//query to retrieve employees
$sql = "select id, first_name, last_name from employees";
$data = $connection->query($sql);
foreach($data as list($id, $first_name, $last_name)){
echo $id." ".$first_name." ".$last_name."\n";
}
[ 83 ]
Advanced Programming with PHP
}
catch(Exception $ex){
echo $ex->getMessage();
}
finally{
$connection = null;
}
?>
In the preceding example, we begin by building a PDO object. In the next step,
we are setting the error reporting mode to ERRMODE_EXCEPTION. This will route
the execution to the catch block and an exception of the class PDO_EXCEPTION is
thrown. In the next step, we are executing our select query and we are printing
the results out onto the screen.
The output for the previous code is as follows:
1 John Doe
2 Jane Dane
3 Richard Roe
Now that we have worked with both MariaDB and unit testing, this is a good place
to set up a test case to test the database integrity. A few operations that we can
perform are to create tables and insert data into those tables on-the-fly. Once we
have the data in those tables, we can perform the select queries to verify if all the
data has been inserted, or even verify if a specific row was inserted as part of a spot
check. Once our tests are successful, we can drop the tables and continue with our
development. Let us look at a very basic example for testing the data integrity.
The following code is available in the DatabaseTest.php file, present in the
code bundle:
connection = new
PDO("mysql:host=localhost;dbname=employee_db",
"root", "admin");
$this->connection->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
[ 84 ]
Chapter 3
}
public function testData(){
$data = $this->connection->query("select count(*)
as ct from employees where first_name =
'John';")->fetchObject();
$this->assertEquals(1, $data->ct);
}
public function tearDown(){
//clean up
$this->connection = null;
}
}
?>
In this example, we begin by establishing a connection to the database in the setup()
fixture, which is used to fetch the data during our test. We are using the setup() and
tearDown() fixtures in our example; they are referred to as test fixtures in the xUnit
architecture. A test fixture is defined as a point where everything that is required to
successfully run a test has to be available. The setup() test fixture is commonly used
to create the required resources, while the teardown() fixture would clean up all
the resources that were set up. In our example, we are using the setup() fixture
to establish a database connection, while we are ending that database connection
in the tearDown() fixture.
The output of the previous code snippet is as follows:
PHPUnit 3.7.32 by Sebastian Bergmann.
.
Time: 91 ms, Memory: 1.50Mb
OK (1 test, 1 assertion)
Here we can see that our test was successful.
[ 85 ]
Advanced Programming with PHP
Summary
In this chapter, we have taken a look at a few new features that are available with
PHP 5.4 and PHP 5.5. We have also looked at unit testing, and how it could help us
streamline our software development life cycle by testing individual units of our
application by using PHPUnit's test cases. The last topic that we have discussed is
establishing a connection with our MariaDB database. In the next chapter, we will
begin by discussing how HTML interacts with PHP and move forward with building
our student portal application.
[ 86 ]
Setting Up Student Portal
In the previous chapter, we went over a few advanced concepts such as the new
features that are part of PHP 5.4 and 5.5, using unit testing to test individual units
of an application, and different methods of connecting to the MariaDB database
server in order to retrieve data. In this chapter, we will use all the concepts that
we have learned in the earlier chapters to build an application. Until now, we have
used PHP CLI to execute most of the scripts that we have worked with. In this
chapter, we will use PHP to build an interactive student portal. Using this student
portal, we can perform the following tasks:
•
Setting up the nuts and bolts of our application
•
Setting up MVC
•
Adding a student
•
Listing all students
•
Adding a course
•
Listing all courses
•
Registering a student to a course
•
Viewing all registrations
We will use HTML, PHP, and MariaDB to accomplish these tasks.
Setting Up Student Portal
Setting up the nuts and bolts of our
application
In Bonus chapter 2, Object-oriented Programming with PHP, we discussed design
patterns and how we can use design patterns to better organize our code. We will
use the MVC design pattern to build our student portal application. The MVC
pattern or the Model-View-Controller pattern is one of the most used patterns to
build web applications. The features of MVC are as follows:
•
The model is responsible for data management. The model handles the
common data operations such as retrieving, updating, and deleting data.
•
The view is responsible for data presentation. The view will commonly
carry the required HTML that would be responsible for displaying the
data on the browser.
•
The controller is responsible for data processing. The controller houses
any application logic that has to be performed on the data that has been
retrieved by the model, before sending it to the view. A controller can
have one or many actions that will serve as a single functional unit of
the application logic. An action is a method in the controller.
In order to build a MVC-based web application, we will need to fulfill a few
prerequisites such as activating the rewrite functionality on Apache, and setting up
the directory structure for our application. We will need the rewrite functionality to
implement clean URLs that are easier to read than a complicated query string.
Before using the rewrite functionality, the URL is as follows:
http://student-portal/index.php?url=students
After using the rewrite functionality, the URL changes to:
http://student-portal/students
Setting up URL rewrite
Apache web server is shipped with a number of very useful modules; one of them
is the mod_rewrite module. The mod_rewrite module provides a rule-based
engine to rewrite URLs on-the-fly. We can also use this module to redirect one URL
to another URL and to invoke an internal proxy fetch. By using the mod_rewrite
module, we can successfully hide the file system path from users.
[ 88 ]
Chapter 4
The mod_rewrite module is turned off by default and has to be explicitly turned on.
To turn on the mod_rewrite module, we will have to either modify the configuration
files of the Apache web server in a few operating systems or use internal commands
to turn on the module. In Ubuntu, we will use the a2enmod command to turn on the
mod_rewrite module:
sudo a2enmod rewrite
As the mod_rewrite module is turned off by default, the overriding capability of
Apache to override URLs dynamically is also turned off. To turn this back on, we
will look for the apache2.conf file or the httpd.conf file, and search for the string
AllowOverride. It will be set to None by default, and we will have to change it to
All. This change has to be applied only to the document root that we are currently
working with and not anything else, as this could create security issues:
Before
Options Indexes FollowSymLinks
AllowOverride None
Require all granted
After
Options Indexes FollowSymLinks
AllowOverride all
Require all granted
As we have made the changes to core Apache configuration files, we will have
to restart the Apache web server. To restart the web server, we will use the
restart command:
sudo service apache2 restart
Now that we have added the rewrite functionality, let us go ahead and set up
the folder structure that we will use for building our MVC student application.
This script has to be run in a terminal window and has to be run in the document
root folder. Now, we create a folder structure using the following commands,
in the build.sh script:
mkdir student-portal
cd student-portal
mkdir models
mkdir controllers
mkdir views
mkdir lib
[ 89 ]
Setting Up Student Portal
The build.sh script will create the required folder structure for housing our scripts.
Let us begin by creating the index.php page that would serve as our entry point to
our student portal. We will be using this index.php page as our primary landing
point and router. To set this as our primary router, we will use the .htaccess file
where we will put our web server rules and conditions.
The .htaccess file is commonly used to decentralize the management
of web server configurations. Using the .htaccess file, we will be able
to add in application-specific web server configurations. The .htaccess
file is housed in the directory of the application, and the configurations in
this file override any global web server configurations.
In our .htaccess file, we will begin by turning on the rewrite engine that is
available via the mod_rewrite module and route all the requests to our index.php
page. During this routing process, we will extract the first part from the URL and
redirect this call to our index.php with url query string parameter.
An example URL request that will be made is as follows:
localhost/student-portal/student
The redirected URL will be as follows:
localhost/student-portal/index.php?url=student
This redirect is done in order to capture the page that was requested and then
process the request from there on. We will use an .htaccess file to facilitate the
redirect that contains the following code:
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_FILENAME} !-l
RewriteRule ^(.*)$ index.php?url=$1 [QSA,L]
This script should be added to the document's root directory and the file should be
saved as .htaccess. Now let us add our index.php and verify if our application
level web server configurations in the .htaccess script have been applied on
request. Add the following code to the index.php file:
[ 90 ]
Chapter 4
In our index.php file, we are retrieving the value of url that will be populated
during the redirection process.
The output is as follows:
Since "student" is the requested page is printed, we can believe that our redirection
was successful. Now let us continue with our student application and build our first
form that will allow us to add a student to our course_registry database.
We will be working with the course_registry database that we
built in Chapter 1, CRUD Operations, Sorting, Filtering and Joins on
MariaDB, and Chapter 2, Advanced Programming with MariaDB.
Setting up MVC
Now that we have our folder structure and the rewrite functionality working, we
can begin setting up our MVC-based application. The first thing that we will have
to focus on would be to bootstrap our application by loading the required classes.
We are storing these required classes in the lib folder and we will use the config.php
file to store the required configurations such as the location of the lib folder, the base
URL for our application, and the database connection information.
get();
return false;
}
$file_name = "controllers/".$url[0].".php";
//should be logged
if(!file_exists($file_name)){
//replace the message
//redirect the user to a custom 404 page
echo "File does not exist";
return false;
}
require_once($file_name);
$ct_name = ucfirst($url[0]);
$controller = new $ct_name;
if(empty($url[1])){
$controller->get();
return false;
}
$action_name
= isset($url[1]) ? $url[1]:NULL;
if($action_name && method_exists($controller, $action_name)){
if(empty($url[2])){
$controller->{$url[1]}();
}
[ 92 ]
Chapter 4
else{
$controller->{$url[1]}($url[2]);
}
}
else{
//should be logged
//replace the message
//redirect the user to a custom 404 page
echo "Action does not exist";
}
}
}
In the preceding code snippet, we begin by retrieving the data in the url parameter.
We begin by using the explode function to build a list of the incoming data.
Before we go further, let us look at the following examples of our URLs to
understand the URL structure:
URL
http://localhost/studentportal/students/add
http://localhost/studentportal/students/get
http://localhost/studentportal/students/delete/1
Controller
Action
Params
Students
add
-
Students
get
-
Students
delete
1
From this table, it is clear that the data that will be carried in through the url
parameter will have the controller, action, and optional parameter. Our bootstrap
class will handle all these cases and the cases where enough data is not present.
It is now time to implement this functionality in our index.php page, which is the
entry point to our application. Enter the following code in our index.php file:
[ 93 ]
Setting Up Student Portal
In the index.php file, we begin by importing the configurations from the config.
php file. Then we are using the _autoload magic function to import all the required
classes in the lib directory. Once the required library files are loaded (including
the Bootstrap.php file), we instantiate a bootstrap object that will take the request
forward. Now that we have our database configurations loaded into the application,
we can create our database library file that will provide the required database
operations. In our case, let us keep our database library file simple and extend
the PDO class.
Enter the following code in our lib/Database.php file:
db = new Database(DB_VENDOR, DB_HOST, DB_NAME, DB_USR,
DB_PWD);
}
}
The Base_Model class will be an abstract class as there will not be a need for us to
instantiate this class and we will extend this class whenever we would need the
functionality in it. This class will serve as the parent class for all the models. In this
class, we are instantiating the Database class. Now let us build our base view class that
will carry the functionality that will help us in controller-view interactions and other
view-related functionalities. Enter the following code in our lib/Base_View.php file:
view = new Base_View();
}
public function loadModel($name) {
$path = 'models/'.$name.'_model.php';
if (file_exists($path)) {
require_once("models/$name_model.php");
$modelName = ucfirst($name)."_Model";
$this->model = new $modelName();
}
}
}
The base controller library file will be quite simple and we will be instantiating an
object of the Base_View class. The view object will be available to all the sub classes
that extend the Base_Controller class, and will thereby be able to use the render
function to call specific views. We also have the loadModel method that takes the
name of the controller and performs a little processing to import the model file.
Once the model is loaded, we can use the model object to query the database.
[ 95 ]
Setting Up Student Portal
Now that we have set up the base libraries for our MVC application, let us build
our first controller. The first controller that we will work with is the Students
controller. The Students controller will house all the application logic for the
students. Enter the following code in our controllers/students.php file:
id)){
echo "New user has been successfully added";
}
?>
student_data as $student): ?>
[ 101 ]
Setting Up Student Portal
In the preceding code snippet, we are using the data stored in $this->student_data
and are iterating over the array to print the student ID, first name, and last name.
Now let us load this page onto the browser. The URL to load this page will be
http://localhost/student-portal/students/get. The output is as follows:
Upon the successful load of this page, we will be able to view the student data in a
list format. Now that we have created actions to successfully add and view students.
Let us implement the functionality of adding a course and listing all the courses.
Adding a course
In this section, we will add the new Courses controller to our controllers'
directory. Our Courses controller will extend the Base_Controller class and,
upon instantiation, we will load the courses model (we are yet to create the model).
The code snippet for the Courses controller will be similar to our Students
controller; this is shown in the following code snippet; in the controllers/
courses.php file:
loadModel("courses");
}
[ 102 ]
Chapter 4
public function add(){
if(isset($_POST['submit'])){
unset($_POST['submit']);
$this->view->id = $this->model->addCourse($_POST);
}
$this->view->render('courses/add');
}
}
We will be using the add action to create a new course and add it to our courses table.
In our add action, we are passing in the data in the $_POST superglobal over to the
addCourse method provided by our courses model. Now let us look at the addCourse
method in our courses model, present in the models/courses_model.php file:
db->prepare("INSERT INTO courses($columns)
VALUES($values);");
foreach($course as $key=>$value){
$stmt->bindValue(":$key", $value);
}
$stmt->execute();
return $this->db->lastInsertId();
}
}
In this snippet, we have the addCourse method that expects the course data as
an argument. Similar to the process of adding a student, we will sort the course
data by key and generate the column data and the value data. Later we prepare
the insert SQL statement to be run on our course_registry database. Upon a
successful insert operation, the last inserted ID is returned back to the controller.
The controller will pass this ID to the view, and the view will print the success
message that the new course has been successfully added.
[ 103 ]
Setting Up Student Portal
Let us now look at our view to add a new course, in the views/courses/add.php file:
| Student Id | First Name | Last Name |
|---|---|---|
id)){
echo "New course has been successfully added";
}
?>
In this snippet, we have an HTML form with two fields for course name and course
description. Upon clicking the Add Course button, the data will be submitted to the
add action. The add action then forwards the data to the addCourse method in the
courses model that adds this new course to the database. The output is as follows:
[ 104 ]
Chapter 4
In the preceding screenshot, we are adding the new course, DS-101, to our database.
In the next section, we will look at how to build a list of all the courses available in
the database.
Listing all courses
In this section, we will focus on listing all the courses in the database on the page.
We will begin by adding the get action to our Courses controller that will use the
getCourses method provided by the courses model. Once the data is retrieved
from the getCourses method, the data is forwarded to the get.php view from
our get action in the controllers/courses.php file:
public function get($id=null){
$this->view->course_data = $this->model->getCourses();
$this->view->render('courses/get');
}
The code of the models/courses_model.php file is as follows:
public function getCourses(){
return $this->db->query("SELECT course_id, name,
description FROM courses;")->fetchAll(PDO::FETCH_ASSOC);
}
The code of the views/courses/get.php file is as follows:
course_data as $course): ?>
[ 105 ]
Setting Up Student Portal
Now let us load this page onto the browser to verify if the DS-101 course is
displayed in this list. The output of the views/courses/get.php file is as follows:
Registering a student to a course
In this section, we will register a student for a course. We will allow the users
to register a student to a course by accepting the student ID and course ID.
We will add the new StudentsCourses controller to our controllers directory
and it will extend the Base_Controller class. Upon instantiation, we will load
the StudentCourses model (similar to other controllers in the application) in the
controllers/studentsCourses.php file:
loadModel("studentsCourses");
}
public function register(){
if(isset($_POST['submit'])){
unset($_POST['submit']);
$student_id = $_POST['student_id'];
$course_id = $_POST['course_id'];
$this->view->id = $this->model->registerStudentCourse
($student_id, $course_id);
}
[ 106 ]
Chapter 4
$this->view->render('studentsCourses/register');
}
}
We will be using the register action to register a student for a course and add the
registration information to our student_courses table. In our register action,
we are extracting the student ID and course ID from the $_POST superglobal.
We pass these values to the registerStudentCourse method provided by our
StudentCourses model. Now let us look at the registerStudentCourse method
in our StudentCourses model in the models/studentsCourses_model.php file:
db->prepare("INSERT INTO students_course
(student_id, course_id) VALUES(:student_id, :course_id)");
$stmt->bindValue(":student_id",$student_id);
$stmt->bindValue(":course_id",$course_id);
$stmt->execute();
}
}
In the preceding code snippet, we have the registerStudentCourse method that
expects the student ID and course ID as arguments. Later, we prepare the insert SQL
statement that will register a student for a course. Let us now look at our view to
register a student to a new course, in the views/studentsCourses/register.php file:
| Course Id | Course Name | Description |
|---|---|---|
id)){
echo "Student has been successfully registered for the
course";
}
?>
In this snippet, we provide two textboxes for the users to enter the student ID
and the course ID. On clicking the Register Course button, the student will be
registered to the specific course. Now let us load this page onto the browser to view
the output. The URL to load this page will be http://localhost/student-portal/
studentsCourses/register. The output is as follows:
Now that we have this page to register a student for a course, let us look at the screen
to view all the registrations.
[ 108 ]
Chapter 4
Viewing all registrations
In this section, we will build the screen that would retrieve all the current
registrations in our course_registry database. We will begin by adding the get
action to our StudentsCourses controller that will use the getStudentsCourses
method provided by the StudentCourses model. Once the data is retrieved from
the getStudentsCourses method, the data is forwarded to the get.php view from
our get action. Let us add these methods to the existing scripts. The code in the
controllers/studentsCourses.php file is as follows:
public function get(){
$this->view->studentsCourses_data =
$this->model->getStudentsCourses();
$this->view->render('studentsCourses/get');
}
The code in the models/studentsCourses_model.php file is as follows:
public function getStudentsCourses(){
$stmt = $this->db->prepare("SELECT s.first_name, s.last_name,
s.student_id, c.course_id, c.name as course_name
FROM students_courses sc INNER JOIN students s ON
sc.student_id=s.student_id INNER JOIN courses c ON
sc.course_id=c.course_id");
$stmt->execute();
$studentsCourses = [];
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$studentsCourses[] = $row;
}
return $studentsCourses;
}
The code in the views/studentsCourses/get.php file is as follows:
[ 109 ]
Setting Up Student Portal
studentsCourses_data as $
studentCourseData): ?>
Now that we have added the required scripts to fetch the existing registrations
and to render them onto the browser, let us load this page into the browser.
The URL to load this page onto the browser is http://localhost/studentportal/studentsCourses/get. The output is as follows:
In the preceding screenshot, we are rendering the list of existing registrations that
are available in our course_registry database. We have covered many tasks for our
student application; a few similar tasks that we will tackle in Chapter 6, Authentication
and Access Control, are as follows:
•
Deregistering a student from a course
•
Deleting a student
•
Deleting a course
[ 110 ]
Chapter 4
We have successfully laid the foundational work towards building our student
application. Now let us go over a few files that I have mentioned briefly but did
not go through in any length. These files are as follows:
•
header.php: The header section of all the pages will be coming from this
file. This file can be split further to have a partial view file that will be specific
ally used for holding the navigation system and to dynamically accept a
page-specific title. This file is located in the views/layouts/ subdirectory.
•
footer.php: The footer section of all the pages will be coming from this file.
This file is located in the views/layouts/ subdirectory.
•
styles.css: This file will be used as the main CSS file for our student portal
application. A good feature to implement is partial- or controller-specific
CSS files. The file is referenced in the header.php file.
Both the header.php and footer.php files are used for view generation, which is
performed by the render method in the Base_View library class. Now let us look
at the folder structure and all the files that will be part of the code bundle for this
chapter, as shown in the following screenshot:
[ 111 ]
Setting Up Student Portal
Summary
In this chapter, we began by building our student portal application that can be
used to add students, view a list of students, add courses, view a list of courses that
are available, register a student to a course, and to view all the registrations in the
database. During this chapter, we built our own MVC framework. There are a lot
of MVC frameworks that are already available; for any application development
purpose, it is advised to use an existing MVC framework as it would be thoroughly
tested and will have been extensively used by others. The MVC framework that
we during this chapter should be used as a reference to understand the nuts and
bolts of existing MVC frameworks. In the next chapter, we will go over common file
operations and how PHP allows us to interact with files.
[ 112 ]
Working with Files and
Directories
In the last chapter, we discussed the basics of building our student portal
application. In this chapter, we will focus on file interactions and operations.
PHP allows us to work with files that are available both locally and on a remote
server. Files are commonly used to store logs and configurations of an application.
They are also used to carry data from one application to another. In this chapter,
we will begin by working with data imports from files and data exports to files.
Later, we will look at two different types of logging mechanisms that will take
us further with our interaction with files.
Data imports
In real-world applications, data may have to be consumed from multiple sources,
and a lot of applications are still built to use flat files for data storage. In this section,
we will work with a file containing data about students, and import that data into
our course_registry database. There are multiple formats that are commonly
used to store data in a flat file. These formats use delimiters such as a comma, tab,
or space, to separate one data item from another. The most popular formats are the
CSV (comma separated values) and TSV (tab separated values) formats. We will
work with a comma-separated list of student data that is stored in a flat file.
Let's take a quick look at this data.
The following code snippet is contained in the students.csv file:
George,Johnson,3225 Woodland Park Dr,Houston,TX,77087,george.johnson,
6579e96f76baa00787a28653876c6127
Charles,Davis,3225 Woodland Park Dr,Houston,TX,77087,charles.davis,
6579e96f76baa00787a28653876c6127
Working with Files and Directories
Edward,Moore,3225 Woodland Park Dr,Houston,TX,77087,edward.moore,
6579e96f76baa00787a28653876c6127
Brian,Anderson,3225 Woodland Park Dr,Houston,TX,77087,brian.anderson,
6579e96f76baa00787a28653876c6127
This file contains the data about four new students that we would like to add to our
existing students table. This data includes the first names, last names, addresses,
cities, states, zip codes, user names, and the SHA1 hashes of the students' passwords.
To import this file, we need to have at least two things: a form to upload this file and
an action that would take this file, extract the data, and call the appropriate function
in the model to add this data to our course_registry database.
Let's begin by building the form that will allow a user to upload this file. Since the
file import functionality will not be used often, let's make this form toggleable so that
it will appear upon clicking a link. We will add the ImportStudents link, and then
add the toggle functionality to this link, so that the form will appear upon clicking
this link. We will add the link and form to the get.php view.
The following code snippet is contained in the views/students/get.php file:
| First Name | Last Name | Course Name |
|---|---|---|
message)): ?>
message ?>
[ 114 ]
Chapter 5
student_data as $student): ?>
| Student Id | First Name | Last Name |
|---|---|---|
Login
username; echo ''; echo 'This is the message: '.$this->message; echo '
'; ?> [ 126 ] Chapter 6 The index.php file is a very simple login form that will allow the user to enter username and password. Upon form submission, the data is posted to the runLogin method in our Login controller. In the runLogin method, the posted data is retrieved and is passed to the login method in Login_Model. Let us look at our Login_Model method to understand how this data is processed to check if the user exists in the database and if there is a match of the username and password combination. In the following code snippet, we begin by creating the Login_Model class that carries the login method. The models/login_model.php file is modified as follows: db->prepare("SELECT username FROM students WHERE username = :username AND password = :password"); $st->execute(array( ':username' => $username, ':password' => SHA1($password) )); $data = $st->fetch(PDO::FETCH_ASSOC); $hasData = $st->rowCount(); if($hasData >0){ Session::set('loggedin',true); Session::set('username',$data['username']); header('Location:'. BASE_URL. 'students/get?message= '.urlencode('login successful')); } else{ header('Location:'. BASE_URL. 'login/index?message='.urlencode('login failed')); } } } [ 127 ] Authentication and Access Control The login method accepts the username and the password for the user. We will begin by querying the students table with this username and password. If the username of the user is returned, we will set the username and a flag to denote that the user is logged-in to the session data. After setting the session variables, we will redirect the user to the students/get page upon success and redirect the user to the login page to allow the user to login again. Now let us look at the login page and use the credentials of John Doe to login. This is shown in the following screenshot: The final code change that we'll make will be to the header to add a logout link that will use our runLogout method, which will invoke the destroy method from our Session library. In the following snippet, we are adding the username of the currently logged-in user and the logout link for the user to logout to the header section. The views/layout/header.php file is modified with the following code:
Contact Us | About Us | Home
id)){
echo "Student has been successfully registered
for the course";
}
?>
[ 132 ]
Chapter 6
Now let's render the following page to verify our changes:
[ 133 ]
Authentication and Access Control
From the output, we can verify that the logged-in users cannot register anybody
apart from themselves as the student ID field is no longer a textbox.
The student ID field has been converted from a textbox to hidden
input, so this completely does not prevent the logged-in user
from changing the student ID before registering from a course.
In Chapter 9, Security, we will fix this issue by making this check
on the server, rather than on the client.
Now that we have laid down the access rules, let's set up a new user role as an
administrator. The two examples that we will discuss in the next section are:
•
Adding courses
•
Registering students for courses
User roles
In this section, let's begin by creating administrators who will have access
across the student portal and will be able to perform operations such as adding
courses and registering any student for a course. Let's begin by creating a few
administrators. We will be building an admin table that will store the information
for the administrators. The following script will create the admin table and add a
couple of administrators. The script is saved as the assets/sql/admin.sql file:
CREATE TABLE IF NOT EXISTS 'admin' (
'admin_id' int(11) NOT NULL AUTO_INCREMENT,
'name' varchar(45) NOT NULL,
'username' varchar(45) NOT NULL,
'password' varchar(45) NOT NULL,
PRIMARY KEY ('admin_id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--- Dumping data for table 'admin'
-INSERT INTO 'admin' ('admin_id', 'name', 'username', 'password')
VALUES
(1, 'admin1', 'admin1', '5f4dcc3b5aa765d61d8327deb882cf99'),
(2, 'admin2', 'admin2', '5f4dcc3b5aa765d61d8327deb882cf99');
[ 134 ]
Chapter 6
Now that we have our administrators, let's begin by modifying our login view
that will allow both students and administrators to login. As we only have two
roles currently in the views/login/index.php file, we can add a checkbox to
allow administrators to set a flag that we will utilize during the login:
Login
username; echo ''; } if($this->message){ echo 'This is the message: '.$this->message; echo '
'; } ?>