Views

Views are stored SELECT queries. If you repeatedly use the same query, it is worthwhile creating it as a view.

To make resource links easily accessible, let's order them alphabetically and create hyperlinks to each letter of the alphabet. With this in mind, take a look at the alphabet view shown in Listing 15-2.

Listing 15-2. Views
CREATE VIEW alphabet AS
    SELECT DISTINCT UPPER(SUBSTR(linktext,1,1)) AS letter
    FROM tblresources
    WHERE reviewed = 1
    ORDER BY letter;
CREATE VIEW specific_link AS
    SELECT id, url,
    (precedingcopy || ' ' || linktext || ' ' || followingcopy)
    AS copy
    FROM tblresources;

The alphabet view creates a row of links as pictured at the top of Figure 15-1.

Rather than repeat the SQL statement that makes up the alphabet ...

Get Object-Oriented PHP now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial