CodeX
Published in

CodeX

A Super-Simple PHP Tutorial for Beginning to Code — Part 3 — Working with Databases via PDO

Photo by Jan Antonin Kolar on Unsplash

Purpose of Part 3

  • Understand the basics of relational databases and be able to integrate them into web-based applications using PHP.
  • Learn about PHP extensions and how to use other people’s code in your web app.

Prerequisites

  • Part 1 of this series — the basics of PHP
  • Part 2 of this series — PHP inter-page communications
  • A need for working with data. Databases are never easy to work with. I’ve tried to create the simplest possible introduction, but still — if you don’t need a database — don’t bother!
  • The basic concept behind databases — here is a great 5 minute YouTube tutorial (of which you only need the first 3 minutes).
  • The basic concept of SQL — Structured Query Language — here is the world’s cutest 4 minute YouTube tutorial on SQL
  • What is NOT a prerequisite — to get started with PHP installed, you do NOT need a web server like apache or a database server like MySQL installed. We’ve already seen how to use the PHP built-in web server. Here you’ll use its built in SQLite database routines.

Basic Concepts

  • PDO — PHP Data Objects. SQL is a fairly standardized language that is used by numerous different implementations: MySQL, Postgress, Microsoft SQLServer, Oracle and others. Rather than having to rewrite your code for each one, PHP has PDO. While we’re NOT going to dive into the complexities of object-oriented coding, we can safely use PDO in our procedural coding without too much bother.
  • PHPINFO. Your installation of PHP may or may not have PDO built in. How would you know? You need a simple one-line page phpinfo.php as follows:
<?php phpinfo(); ?>
header from a very long page from phpinfo
header shows which database drivers are installed
  • PHP Extensions: You’ll notice above that sqlite is installed. If it were not, you’ll need to install the sqlite extension for your version of php. For example, it was not there when I installed php7.4, so in linux I need to:
sudo apt install php7.4-sqlite3
  • (For windows, the instructions are here.) I also discovered when I first tried to run phpliteadmin (as described below) that I also needed the mbstring extension. Once these are installed, they stay installed.
sudo apt install php7.4-mbstring
  • An Admin Page. As you build a complex, multi-page web app you do NOT need to build an an administrator page yourself. Fortunately, you can simply download one and link to it (with proper protections once you move to a public server). For SQLite, you can download a single page phpLiteAdmin which allows you to create or delete databases and tables, insert, edit or delete records and test out queries. It also can import and export to CSV files compatible with spreadsheet programs.
  • Primary Key: The records in a database are accessed by the values in the fields, and often by a Primary Key which is often — but not necessary — an auto-incrementing integer and often named id. You don’t have to enter that field — the database will create it for you.
  • Index: Big database tables often have one or more indexes that allows the database to find your record without having to read the whole file. For example, if you had a table of a mllion people, you might want to create an index on their last name, or their zip code. In the case of playing with small tables in SQLite, there is no reason to create an index unless you really want to learn about them.
  • Query: A query is a statement that fetches data from the database, as was shown in the tutorial. It is usually of the form “select <fields> from <tables> [where clause]. For example, in the people table in test1, you might do the query below. There are also SQL statements to do all the other things to a database: create, drop, delete, insert update that you’ll find here.
select first_name,last_name from people where name='Brown' 

Finally! Building a database PHP web app!

  • Project folder: You app will be made up of a bunch of little files, so please create a folder for them. If you’re not already using an editor like VSCode that can handle a folder of files, this might be a good time to switch! I’ve put a copy of all these files into my github repository, so you can download or “clone” them all from https://github.com/jcoonrod/demo1
  • Error reporting: PHP does not normally show errors in the browser. In a production environment, you really don’t want users to see these message — they show up on the server. But when you’re developing a database app, you will actually want to see them. I’ve added them to the next section.
  • D.R.Y. (Don’t Repeat Yourself): We’ll be creating a bunch of similar pages with the same header, so header.php will hold the common lines and then we can include it in the other pages, including a super-simple menu, super-lean CSS and the headers necessary to score 100% on Google’s Lighthouse tests (which is something I always like to do when I can.) You’ll see an svg icon in there — SVG graphics is a fun thing to learn. My favorite place to learn about all things web
<!DOCTYPE html>
<html lang='en'>
<head><title>My App</title>
<link rel="icon" type="image/svg+xml" href="/favicon.svg">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name = "Description" content = "My first database app">
<style> * {font-family:sans-serif;}
nav, th {color:white; background-color:navy;padding:5px;}
nav a {color:white; font-weight:bold; text-decoration:none;}
nav a:hover {background-color:green;}
</style>
</head>
<body>
<nav>
<a href="/">Home</a> |
<a href="/people.php">People</a> |
<a href="/products.php">Products</a> |
<a href="/phpinfo.php">PHPInfo</a> |
<a href="/phpliteadmin.php" target="_blank">Admin</a>
</nav>
  • Connecting PHP to your database: Here is a simple example that connects to your database, and copies the names of your customers into a table. You’ll see your first dose of OOP code here in expressions containing “->.” An object like PDO has a bunch of functions in it that you reference with “objectname->function.” The “new” commands creates a new PDO object which we name $db pointing to our database file. The query command creates a statement object we call $stmt whose fetch function returns one $row at a time as a regular array. Frankly, these are the only things I ever use.
<?php require_once("header.php"); ?>
<h1>People in Test1</h1>
<?php
$db=new PDO("sqlite:test1.db");
if(!$db) Die("Failed to connect");
$stmt=$db->query("select first_name,last_name from customers order by 2");
if(!$stmt) die(print_r($db->errorInfo(),true));
?>
<table>
<tr><th>First Name</th><th>Last Name</th></tr>
<?php
while($row=$stmt->fetch())
echo("<tr><td>".$row[0]."</td><td>".$row[1]."</td></tr>");
?>
</table>
</body>
</html>

Problem Set

  • Add similar query language to a products.php page that lists the products ordered by price.
  • Create a new table — purchases — which links a customer to a product (hint — 4 integer fields and one , id, customer_id, product_id, purchase_date). For this latter, learn about Unix time at https://www.sqlite.org/datatype3.html
  • Add a new page to the menu — purchase.php — which selects a customer and a product and inserts a new record into the purchases table.

Where to go from here!

  • Once you’re familiar with this, you can easily apply what you’ve learned to “real world” systems that use MySQL or other professional-grade databases.
  • If you start building applications in the cloud (like Google or AWS), multi-page apps typically send EVERY URL to index.php where you need pull in the URI ($url=$_SERVER[‘REQUEST_URI’]) and parse it and then “require_once” relevant files.
  • One of beauties of using PHP is that any problem you come across has already been solved by someone else, and you can simply google it and adapt it to your need. A lot of these people will refer to all sorts of packages that require the “Composer” package manager, and learning about classes and namespaces. I generally avoid those (see my piece here). If you have to include a package via composer, you probably still won’t need to learn much about classes or namespaces.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
John Coonrod

John Coonrod

A guy committed to human dignity for all.