Download our latest
Android and iPhone apps ...
At a glance ...
Pages
335
Exercises
10
Sections
13
Public
On-Site
Level
1
Days
2

Course Name: MariaDB 10

Duration:
Minimum skill level required:
On-Site course:
Public course:
Sections:
No of exercises:
No of pages in the manual:
Pre-Requisite course(s):
Suggested further course(s):
Price guide:
2 days
1 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
13
10
335
PHP 7.1
None
Contact us for the latest pricing

Synopsis for course

This course covers accessing a MariaDB 10 database via PHP, it assumes prior knowledge of PHP 7.1

Course uses the mysqli functions via PHP.

Exercise highlights

Loading data into MariaDB
Selecting and displaying records from a MariaDB database
Using where clauses to limit records

Course Contents

MariaDB 10
 
 

Section
Section Name
Page

One
Introduction to MariaDB
2

 
- What is SQL used for ?
3

 
- Why use MariaDB with PHP ?
4

 
- Definition of a Database
5

 
- Using MariaDB with PHP
6

Two
Simple MariaDB Queries in PHP
8

 
- Introduction
9

 
- Show Tables
10

 
- Show Columns
14

 
- Describe
17

 
- Selecting Data from a Table
18

 
- Counting records in a Table
21

 
- Selecting Data from a Table
23

 
- Selecting Columns from a Table
24

 
- Selecting Literals from a Table
25

 
- Selecting Literals from Dual
26

 
- Exercise One
27

 
- Manipulation with Functions
29

 
- Concatenating Columns
33

 
- Performing Calculations
34

 
- Using Variables
35

 
- Using mysqli_fetch_array()
37

 
- Using Aliases
38

 
- Ordering Data
41

 
- Ordering more than One Column
43

 
- Manipulating Dates using date_add()
44

 
- Displaying Dates using date_format()
46

 
- Exercise Two
48

Three
MariaDB Errors
56

 
- Determining Errors in the SQL
57

 
- Using mysqli_errno() and mysqli_error()
58

Four
Limiting Data Selected with a Where Clause
59

 
- Limiting Data
60

 
- Different types of MariaDB Where Clauses
62

 
- Using Table Aliases
73

 
- Combining Where Clauses
75

 
- Different types of MariaDB Where Clauses
76

 
- Handling Nulls in Where Clauses
77

 
- Using Functions in Where Clauses
78

 
- Using PHP Variables in Where Clauses
80

 
- Exercise Three
81

Five
Aggregating Values
85

 
- Introduction
86

 
- Using Group By
88

 
- Using Having with Group By
90

 
- Using With Rollup to Total Grouping
91

 
- Using Distinct()
92

 
- Exercise Four
93

Six
Regular Expressions
96

 
- Introduction
97

 
- Testing for 'Contains' within a String
98

 
- Starts or Ends using Line Anchors
99

 
- Starts and Ends using Line Anchors (or)
100

 
- Using the Dot (Any Character)
101

 
- Using {Intervals}
102

 
- Using Character Lists []
104

 
- Using Character Lists to exclude matches
107

 
- POSIX
108

 
- Using the Question Mark (Optional)
110

 
- Using the Plus sign (Mandatory)
112

 
- Using the Star sign (Optional)
113

 
- Exercise Five
114

Seven
Joining Tables in a MariaDB Statement
116

 
- Introduction
117

 
- Linking Tables in an SQL Statement
118

 
- Theta style of SQL
123

 
- ANSI style of SQL
124

 
- Problems with Data matching
125

 
- Problems with Joining Tables in SQL
126

 
- Ansi SQL Standards (Natural Joins)
127

 
- Ansi SQL Standards (Cross Joins)
128

 
- Ansi SQL Standards (Joins/Using)
129

 
- Ansi SQL Standards (On)
130

 
- Ansi SQL Standards (Multiple On)
131

 
- Ansi SQL Standards (Left Outer Joins)
132

 
- Ansi SQL Standards (Right Outer Joins)
133

 
- Merging Tables
134

 
- Using Union to Merge Tables
135

 
- Using Union All to Merge Tables
136

 
- Using Intersect to Merge Tables
137

 
- Using Minus to Merge Tables
138

 
- Knowledge Check
139

 
- Knowledge Check - Union
140

 
- Union Syntax in MariaDB
141

 
- Knowledge Check - Union All
142

 
- Union All Syntax in MariaDB
143

 
- Knowledge Check - Intersect
144

 
- Intersect Syntax in MariaDB
145

 
- Knowledge Check - Minus
146

 
- Minus Syntax in MariaDB
147

 
- Using the Merge Syntax in MariaDB
148

 
- Exercise Six
149

Eight
Advanced MariaDB Query Techniques
154

 
- Using Limit
155

 
- Using If()
157

 
- Using Ifnull()
158

 
- Using Nullif
159

 
- Using Case Function
160

 
- Using Sub-queries
164

 
- Using Set to create variables
167

 
- Populating a Variable from a Query
168

 
- Exercise Seven
170

Nine
MariaDB Datatypes
173

 
- Introduction
174

 
- Numeric Datatypes (Integer)
175

 
- Numeric Datatypes (Decimal)
176

 
- String Text Datatypes
177

 
- String Blob Datatypes
178

 
- String List Datatypes
179

 
- Date Datatypes
180

Ten
Creating Database Objects
181

 
- Creating Tables
182

 
- Mandatory Columns
183

 
- Creation Syntax for Tables
184

 
- Column Creation
187

 
- AUTO_INCREMENT
189

 
- Last_insert_id
190

 
- Drop Table Syntax
191

 
- Temporary Tables
192

 
- Creating Indexes
194

 
- Primary Key Creation
197

 
- Unique Key Creation
200

 
- Foreign Key Creation
203

 
- On Delete / On Update
205

 
- Cascade
208

 
- Set Null
209

 
- NO ACTION
210

 
- RESTRICT
211

 
- SET DEFAULT
212

 
- Creating Views
213

 
- Renaming Views
216

 
- Dropping Views
217

 
- Exercise Eight
218

Eleven
Altering Elements
220

 
- Altering Tables
221

Twelve
Inserting and Altering Data
224

 
- Rules
225

 
- Syntax for Populating Tables
226

 
- Inserting using the Set syntax
232

 
- Multiple Inserts
233

 
- On Duplicate Key Update
234

 
- Using Replace for Inserting Records
235

 
- General Comments
238

 
- Updating Records Rules
239

 
- Updating Data
240

 
- Deleting Data
243

 
- Inserting Large Objects
245

 
- Views
247

Thirteen
Prepared Statements
251

 
- Introduction
252

 
- Preparing the Statement
253

 
- Removing Prepared Statements
254

 
- Using Parameters
255

 
- Using mysqli_prepare()
257

 
- Using mysqli_stmt_bind_param()
258

Fourteen
Committing Records
259

 
- DDL and DML
260

 
- mysqli_commit() and mysqli_rollback()
262

 
- What happens if disconnected ?
264

 
- Exercise Nine
265

Fifteen
Importing and Exporting in MariaDB
271

 
- Outfile
272

 
- Problems with Outfile
276

 
- Using Dumpfile
278

 
- Selecting into Variables
279

 
- Importing Data using Infile
280

 
- Ignoring Lines using Infile
282

 
- Using the Set command with Infile
283

 
- Using the Lines option with Infile
286

 
- Loading comma separated files
287

 
- Loading XML file
289

 
- Loading XML file using Ignore
291

 
- Loading XML file naming Columns
292

 
- Loading XML file using Set
293

Sixteen
Using XML with PHP
294

 
- Introduction
295

 
- Reading XML files with PHP with DOM
296

 
- Reading XML with getElementsByTagName()
304

 
- Looping with getElementsByTagName()
306

 
- Writing XML files with PHP
315

 
- Writing XML files with PHP Arrays
320

 
- Writing XML files with PHP from MariaDB
326

 
- Exercise Ten
331