Practical Database Queries

Abstract

The last several chapters served as an introduction to numerous concepts regarding using PHP and MySQL together to retrieve and manipulate data. This chapter expands your knowledge, demonstrating several challenges that you’re bound to repeatedly encounter while creating database-driven web applications. In particular, you’ll learn more about the following concepts:

• Tabular output: Listing query results in an easily readable format is one of the most commonplace tasks you’ll deal with when building database-driven applications. This chapter explains how to programmatically create these listings.

• Sorting tabular output: Often, query results are ordered in a default fashion, by product name, for example. But what if the user would like to reorder the results using some other criteria, such as price? You’ll learn how to provide table-sorting mechanisms that let the user search on any column.

• Subqueries: Even simple data-driven applications often require queries to work with multiple tables, typically using joins. However, as you’ll learn, many of these operations can also be accomplished with the arguably much more intuitive subquery.

• Cursors: Operating in a fashion similar to an array pointer, a cursor (a feature new to MySQL 5.0) gives you the ability to swiftly navigate database result sets. In this chapter you’ll learn how to use cursors to streamline your code.

• Paged results: Database tables can consist of thousands, even millions, of records. When large result sets are retrieved, it often makes sense to separate these results across several pages and provide the user with a mechanism to navigate back and forth between these pages. This chapter explains how to do so.