PHP and PostgreSQL Advanced Web Programming

Ewald Geschwinde was born on June 21, 1976 in Vienna, Austria. After primary school, he attended the high school for economics in Oberpullendorf. During this time he dealt with computers and extended the work in his favorite field while studying at the Technical University in Vienna. A few months later he started working at the computer center of CA (an Austrian bank), where he was responsible for writing data converters and network solutions for backup systems. In February 1999 he joined Synthesis, where he focused on scientifically monitoring the development of unemployment in Austria and generating reports using C, EFEU, LaTeX, and Perl. In his spare time he developed a database solution for business consultants. After focusing on Oracle databases, he left Synthesis to found Cybertec Geschwinde & Schönig OEG¿a company providing commercial support, training courses, tuning, and remote administration for PostgreSQL (www.postgresql.at) as well as LDAP (www.openldap.info). On August 9, 1978 Hans-Jürgen Schönig was born in Knittelfeld, a small town 125 miles southwest of Vienna. After primary school he attended a private school in Seckau. After the high school final exam, he started studying "Economics of Information" at the Technical University and the University of Vienna where he met Ewald Geschwinde. Just for fun, Hans started working on various projects and was finally employed at Synthesis (an Austrian research company focusing on forecasting the Austrian labor market), in September 1998. There he was responsible for the scientific analysis of data provided by the Austrian social security insurance (dozens of millions of records). During his time at Synthesis, he worked with Unix systems and automated text production, using EFEU, LaTeX, and Perl. In addition, he taught Unix classes in an adult education program once a week. In the summer of 2000 Hans and Ewald left Synthesis to found Cybertec Geschwinde & Schönig OEG (www.cybertec.at), focused entirely on PostgreSQL, LDAP, and Unix databases.

(NOTE: Each chapter concludes with a Summary.)

Introduction.
I. GETTING STARTED.

1. Getting Started.


Why Use PHP and PostgreSQL as a Team? What You Can Do with PHP and PostgreSQL. Web Sites Built on PHP and PostgreSQL.

2. Setting Up PHP.


About PHP. Installing Apache with PHP from Source. Installing Binaries on Linux. Configuring PHP.

3. PHP Basics.


Getting Started. Control Structures and Operators. Data Types and Functions. Building Functions. Exception Handling. Working with Files. Variables and Scope. Building Packages. Working with Forms. Debugging PHP.

4. Object-Oriented PHP.


The Concepts of Object Orientation. Building Classes. Working with Inheritance.

II. GETTING STARTED WITH POSTGRESQL.

5. Relational and Object-Relational Database Concepts.


Normalization. Basic Operations in the Relational Model. PostgreSQL as an Object Relational Database. Modeling Techniques. Components of Relational Databases and Technical Terms.

6. Installing PostgreSQL.


Installing PostgreSQL on Unix. Installing PostgreSQL on Windows. Creating and Removing Databases. The User Interface. The Architecture of PostgreSQL. License.

7. Basic SQL.


Data Types. Building Tables. Building Simple SQL Statements. Updates and Deletes. Writing Joins. Aggregating Data. Inserting Huge Amounts of Data. Indexes. Modifying Objects. Mathematical Functions and Operators.

8. Advanced SQL.


Advanced Data Types. Working with Views. Working with Subselects. Working with Aliases and Self-Joins. Object-Oriented SQL. Transactions and Locking. Constraints. Basic Optimizations Using EXPLAIN. Sophisticated Joins. Working with Arrays. Combining Queries and Ranges of Values.

9. Embedded Languages.


Writing SQL Functions. PL/pgSQL. Writing Triggers. Embedded Languages Versus PHP Functions.

10. PostgreSQL Administration.


User Administration and User Rights. Maintenance and Database Internals. Configuring PostgreSQL. Preparing the Database for the Web.

III. PHP/POSTGRESQL INTERACTION.

11. Writing Database-Driven Applications.


Connecting to the Database. Inserting and Retrieving Data. Error Handling and Monitoring. Handling Huge Amounts of Data. Retrieving Objects from the Database. Tracing a PostgreSQL Connection. Locking. PHP and Transactions.

12. Working with BLOBs.


Working with PostgreSQL BLOBs Using SQL. Working with PostgreSQL BLOBs Using PHP. Managing File Uploads. Storing Files as Toasted Text. An Example of a Simple Image Management Tool.

13. Working with Persistent Database Connections.


The Concept of Persistent Database Connections. An Example. Persistent Connections and Performance. Dangers and Hardware Issues.

IV. ADVANCED TECHNOLOGIES.

14. Managing Regular Expressions.


Perl Style. POSIX Style. Regular Expressions and SQL.

15. Session Management.


Managing Sessions with Cookies. Session Management.

16. Working with Dynamic Documents, Images, and Movies.


Creating Dynamic Images. Using Geometric Data Types. Creating Dynamic Flash Movies. Generating PDF Files.

17. Working with Dates and Time.


Dates and Time in SQL. Dates and Time in PHP.

18. Tuning.


Tuning PostgreSQL. Tuning PHP.

19. XML.


The Basic Concepts. Building a Simple XML-Based Application. XML and PostgreSQL.

20. Security Issues.


Potential Threats. Securing Your System. User Authentication with Apache and PHP.

V. PRACTICAL EXAMPLES.

21. Web Applications.


Mail Systems. Building a Web Shop. Building a Content Management System. Creating Stock Charts. PHP for Application Servers.

22. Extending PostgreSQL.


A User-Defined Data Type. Building Substring Indexes. Tolerant Search Algorithms.

23. High-Availability Systems.


An Introduction to High Availability. Building Failsafe Applications.

VI. MIGRATION.

24 - Migration.


Migrating from MySQL to PostgreSQL. Migrating from Oracle to PostgreSQL.

Index.