12. Generating Multi-table Queries
by Marc Delisle
Mastering phpMyAdmin 3.4 for Effective MySQL Management
- Mastering phpMyAdmin 3.4 for Effective MySQL Management
- Mastering phpMyAdmin 3.4 for Effective MySQL Management
- Credits
- About the Author
- About the Reviewers
- www.PacktPub.com
- Preface
- 1. Getting Started with phpMyAdmin
- PHP and MySQL: The leading open source duo
- What is phpMyAdmin?
- Installing phpMyAdmin
- Configuring phpMyAdmin
- Installing phpMyAdmin configuration storage
- Upgrading phpMyAdmin
- Summary
- 2. Configuring Authentication and Security
- 3. Over Viewing the Interface
- 4. Creating and Browsing Tables
- 5. Changing Data and Structure
- Changing data
- Changing table structure
- Summary
- 6. Exporting Structure and Data (Backup)
- 7. Importing Structure and Data
- 8. Searching Data
- 9. Performing Table and Database Operations
- 10. Benefiting from the Relational System
- 11. Entering SQL Statements
- 12. Generating Multi-table Queries
- 13. Synchronizing Data and Supporting Replication
- Synchronizing data and structure
- Supporting MySQL replication
- Summary
- 14. Using Query Bookmarks
- 15. Documenting the System
- 16. Transforming Data using MIME
- Browsing data without transformations
- Enabling transformations
- Examples of transformations
- Summary
- 17. Supporting Features Added in MySQL 5
- 18. Tracking Changes
- 19. Administrating the MySQL Server
- A. Troubleshooting and Support
The Search pages in the Database or Table view are intended for single-table lookups. This chapter covers the multi-table Query by example (QBE) feature available in the Database view.
Many phpMyAdmin users work in the Table view, table by table, and thus tend to overlook the multi-table query generator, which is a wonderful feature for fine-tuning queries. The query generator is useful not only in multi-table situations but also in single-table situations. It enables us to specify multiple criteria for a column, a feature that the Search page in the Table view does not possess.
Note
The examples in this chapter assume that a multi-user installation of the phpMyAdmin configuration storage has been made (refer to Chapter 1), and that the book-copy table created during an exercise of Chapter 9 is still there in the marc_book database.
To open the page for this feature, we go to the Database view for a specific database (the query generator supports working on only one database at a time) and click on Query.
The following screenshot shows the initial QBE page. It contains the following elements:
- A visual builder link (covered at the end of this chapter)
- Criteria columns
- An interface to add criteria rows
- An interface to add criteria columns
- A table selector
- The query area
- Buttons to update or to execute the query
The initial selection includes all tables. Consequently, the Column selector contains a great number of columns. For our example, we will work only with the author and the book tables. Hence, we select only these from the Use Tables selector.
We then click on the Update Query button. This refreshes the screen and reduces the number of columns available in the Column selector. We can always change the selected tables later, using our browser's mechanism for multiple choices in drop-down menus (usually control click).
-
No Comment