Join Bill Weinman for an in-depth discussion in this video Getting the most out of the Quick Start, part of PostgreSQL 9 with PHP Essential Training.
This chapter is for those of you who are familiar with another relational database management system and who know enough PHP and SQL to get your job done and are looking to get up and running with Postgres as quickly as possible. For the purposes of this quick start, an experienced developer is someone who has experience using PHP or another similar C-like scripting language, and experience with SQL and another SQL-based database management system. The examples in this chapter use a PHP sandbox script as a template.
Before you continue, it's important that you update the password in this file to match the password you entered when you created the sid user in the pgAdmin. You see here on line 14 I have foo.bar. This is where you'll need to put the password for your sid user exactly as you entered it in pgAdmin. Let's take a look at here. This is in PHP, and we're just going to start at the bottom for our purposes right now. And down here at the bottom you see there is a function for displaying errors, there is a function for displaying error messages, there is a function for just displaying a message, and there's a function for displaying content.
These functions, combined with some CSS, make your screen look like this. Then going back up to the top, you see we have some variables are defined. We bring in the bwPgSQL.php library-- and that gets described in some detail later on in the course--and we'll call an init function, the main function where the work is done and a page function to display the pages. The init function defines some global variables, initializes an array for the display variables, and connects to the database, creates a table, populates that table, and it does all of this within a try block so that it can handle exceptions for errors.
Then the main function displays some messages, starts a timer, which is then displayed at the bottom, and has another try block for whatever database operations we're demonstrating in this particular exercise. So this is where the bulk of the work is done here in the main function. Then finally this page function brings in a header, a body, and a footer PHP pages, and fills in the variables that display the actual content on the screen. So this is what it looks like in the browser.
This is our start.php script, which is the same one when we're looking at here in the editor. And you see the little bit messages where it connects to the database, it populates the database with four rows in the table, and it displays the content there in the main function. The examples in this chapter also use a PHP library called bwPgSQL. It's basically a wrapper around the PHP functions that use Postgres and the Postgres driver--and this library is explained detail later in the course.
It uses PHP's excellent PDO interface for talking to the database, and it provides direct SQL support, as well as simple CRUD functionality. Feel free to use this library as a starting place or to write your own library from scratch. The library provides a simple object-oriented interface that can be a real timesaver for you, with very little performance cost. It supports most common database operations. It uses placeholder arguments for SQL parameters. It has a CRUD interface--that's Create Read Update Delete--the basic four functions of a database application.
The CRUD records are passed as convenient associative arrays. Rows are also returned as associative arrays. It does not provide support for prepared statements, although it does use prepared statements internally for performance. So again, this chapter is a quick start. If you have the necessary experience and knowledge, this should help you get up and running with Postgres quickly and easily.
- 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