4h 45m – Beginner
In this course, author Bill Weinman shows how to architect PostgreSQL databases and integrate them into web applications using PHP. The course covers the basics of creating tables, storing data with data types, and working with expressions, operators, and strings. The course also explores the differences between the PDO and pgsql interfaces and demonstrates managing a database in PHP.
Topics include:
Installing PostgreSQL
Understanding database architecture
Inserting, updating, and deleting data in a table
Creating a database library
Indexing ID fields
Storing numbers, text, and Boolean values
Reading data
Using casts to force type
Using mathematical functions
Concatenating strings
Working with date and time functions and operators
Defining CRUD
Using PHP to insert, read, update, and delete rows in a database
Introduction – 1h 15m
Welcome – 58s
Using the exercise files – 58s
Prerequisites – 3m 19s
Installing PostgreSQL on a Mac – 14m 49s
Installing PostgreSQL on a PC – 11m 16s
Installing PostgreSQL on Ubuntu Linux – 22m 17s
Installing XAMPP and SID on a Mac – 8m 32s
Installing XAMPP and SID on a PC – 13m 34s
1. Quick Start – 44m 17s
Getting the most out of the Quick Start – 3m 52s
Creating a database – 5m 28s
Creating a table – 7m 1s
Inserting data into a table – 8m 25s
Getting data from a table – 4m 37s
Updating data in a table – 4m 29s
Deleting data from a table – 3m 5s
Creating a database library – 7m 20s
2. Creating a Database – 29m 31s
Understanding databases and tables in PostgreSQL – 1m 15s
Creating a database – 7m 14s
Defining a table in SQL – 5m 35s
Creating a table in PHP – 6m 41s
Creating indexes – 5m 20s
Indexing ID fields – 3m 26s
3. Data Types – 23m 45s
Understanding data types – 2m 53s
Storing numbers with INTEGER – 3m 7s
Storing decimal numbers with NUMERIC – 1m 59s
Storing floating-point numbers with REAL – 5m 29s
Storing text – 3m 17s
Storing Boolean values – 2m 11s
Storing dates and times – 4m 49s
4. Storing and Reading Data – 15m 1s
Storing and deleting data with INSERT, UPDATE, and DELETE – 5m 21s
Reading data with SELECT – 5m 24s
Reading with JOIN – 4m 16s
5. SQL Expressions – 8m 7s
Understanding SQL expressions – 1m 7s
Matching patterns with LIKE – 2m 51s
Choosing from multiple conditions with the CASE expression – 1m 43s
Using casts to force type – 2m 26s
6. Mathematical Functions and Operators – 7m 46s
Using operators for simple arithmetic – 4m 44s
Using math functions – 3m 2s
7. String Functions and Operators – 12m 19s
Finding the length of a string – 3m 43s
Concatenating strings – 1m 34s
Returning parts of a string – 1m 1s
Replacing strings within strings – 1m 24s
Trimming and padding strings – 2m 49s
Converting numbers – 1m 48s
8. Date and Time Functions and Operators – 9m 37s
Getting the current time and date – 2m 24s
Specifying a specific time zone with AT TIME ZONE – 4m 58s
Performing date and time arithmetic – 1m 1s
Getting parts of a timestamp with EXTRACT – 1m 14s
9. PHP Interfaces – 34m 24s
Choosing an interface – 1m 39s
Using the PHP PostgreSQL extension interface – 11m 33s
Using the PDO interface – 9m 53s
Creating a library – 11m 19s
10. Web Applications – 24m 1s
Defining CRUD – 4m 18s
Using PHP to insert, read, update, and delete rows in a PostgreSQL database – 7m 42s
Installing the testimonials database – 3m 3s
An overview of the testimonials application – 3m 7s
Managing the database in PHP – 3m 29s
Displaying the testimonials using PHP – 2m 22s
Conclusion – 31s
Goodbye – 31s