| Lesson 7 || Selecting records based on user input |
| Objective || Use parameterized queries to select records based on user input values. |
Selecting Records based on User Input
Practically all Web sites today have some level of search capability. The typical scenario includes a search page in which the user selects a search criterion, such as author or title, enters a search term or phrase, and finally clicks a button to submit the search to the Web server.
In the scenario just described, the search criterion and term or phrases are not known at design time .
Rather, the complete search definition isn't known until runtime. At runtime, the SQL query is built dynamically after the user submits the search. There are generally two ways to implement runtime dynamic searches.
- Design time: The mode of operation when the application is being written.
- runtime: The mode of operation when the application is running.
- Stored procedures: SQL statements stored in a database
- Parameterized queries: SQL query that uses dynamic parameters
Parameterized queries use a SQL statement with a placeholder. A placeholder is simply a character, such as a question mark, that will be replaced at runtime with the values the user entered. Using placeholders, your application can build SQL queries dynamically at runtime. The following SQL statement shows a placeholder for the book status value.
SELECT ItemNo, Title, Author, Retail FROM BookTable
WHERE Status = ?
You can implement parameterized queries in PHP using several of the database products you read about in the ealier Module on database concepts.
The VirtualBookShelf.com database used in this course is a Microsoft Access database. Unfortunately, PHP doesn't support parameterized queries in Microsoft Access. The simulation in this lesson uses a Microsoft SQL Server database. You use the Recordset DTC to create a parameterized query.
The control is bound to a SQL query, which will have one or more placeholders for user-entered values.
The following simulation demonstrates how to create an HTML page that lets the user enter a state abbreviation as the search term.
Using Parameterized Queries