The Multi-Table Query Generator using phpMyAdmin and MySQL

Exclusive offer: get 50% off this eBook here
Mastering phpMyAdmin 2.11 for Effective MySQL Management

Mastering phpMyAdmin 2.11 for Effective MySQL Management — Save 50%

Increase your MySQL productivity and control by discovering the real power of phpMyAdmin 2.11

$20.99    $10.50
by Marc Delisle | February 2009 | PHP

The Search pages in the Database or Table view are intended for single-table lookups. This article by Marc Delisle, 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 for a single table. It enables us to specify multiple criteria for a column, a feature that the Search page in the Table view does not possess.

The examples in this article assumes that a multi-user installation of the linked-tables infrastructure has been made and that the book-copy table created during an exercise in the article on Table and Database Operations in PHP is still there in the marc_book database. To access the code used in this article Click Here.

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 screenshot overleaf shows the initial QBE page. It contains the following elements:

  • 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

Mastering phpMyAdmin

Choosing Tables

The initial selection includes all the tables. In this example, we assume that the linked-table infrastructure has been installed into the marc_book database. Consequently, the Field selector contains a great number of fields. For our example, we will work only with the author and book tables:

Mastering phpMyAdmin

We then click Update Query. This refreshes the screen and reduces the number of fields available in the Field selector. We can always change the table choice later, using our browser's mechanism for multiple choices in drop-down menus (usually, control-click).

Column Criteria

Three criteria columns are provided by default. This section discusses the options we have for editing their criteria. These include options for selecting fields, sorting individual columns, entering conditions for individual columns, and so on.

Field Selector: Single-Column or All Columns

The Field selector contains all individual columns for the selected tables, plus a special choice ending with an asterisk (*) for each table, which means all the fields are selected:

To display all the fields in the author table, we choose `author`.* and check the Show checkbox, without entering anything in the Sort and Criteria boxes. In our case, we select `author`.`name`, because we want to enter some criteria for the author's name.

Sorts

For each selected individual column, we can specify a sort (in Ascending or Descending order) or let this line remain intact (meaning no sort). If we choose more than one sorted column, the sort will be done with a priority from left to right.

When we ask for a column to be sorted, we normally check the Show checkbox, but this is not necessary because we might want to do just the sorting operation without displaying this column.

Showing a Column

We check the Show checkbox so that we can see the column in the results. Sometimes, we may just want to apply a criterion on a column and not include it in the resulting page. Here, we add the phone column, ask for a sort on it, and choose to show both the name and phone number. We also ask for a sort on the name in ascending order. The sort will be done first by name, and then by phone number, if the names are identical. This is because the name is in a column criterion to the left of the phone column, and thus has a higher priority:

Mastering phpMyAdmin 2.11 for Effective MySQL Management Increase your MySQL productivity and control by discovering the real power of phpMyAdmin 2.11
Published: March 2008
eBook Price: $20.99
Book Price: $34.99
See more
Select your format and quantity:

Updating the Query

At any point, we can click the Update Query button to see the progress of our generated query. We surely have to click it at least once before executing the query. For now, let's click it and see the query generated in the query area. In the following examples, we will click Update Query after each modification:

We have selected two tables, but have not yet chosen any columns from the book table, so this table is not mentioned in the generated query.

Criteria

In the Criteria box, line, we can enter a condition (respecting the SQL WHERE clause's syntax) for each of the corresponding columns. By default, we have two criteria rows. To find all authors with Smith in their name, we use a LIKE criterion—LIKE '%SMITH%'—and click Update Query:

We have another line available to enter an additional criterion. Let's say we want to find the author 'Maria Sunshine' as well. This time, we use an = condition. The two condition rows will be joined by the OR operator selected by default from the left side of the interface:

To better demonstrate that the OR operator links both the criteria rows, let's now add a condition, LIKE '%8%', on the phone number:

By examining the positioning of the AND and OR operators, we can see that the first conditions are linked by an AND (because AND is chosen under the name column) and that the second row of conditions is linked to the rest by the OR operator. The condition we just added (LIKE '%8%') is not meant to find anyone, because in an exercise in Chapter 12, we changed the phone number of all authors with name 'Smith' to '444-5555'.

If we want another criterion on the same column, we just add a criteria row.

Adjusting the Number of Criteria Rows

The number of criteria rows can be changed in two ways. First, we can select the Ins checkbox under Criteria to add one criteria row (after clicking on Update Query):

As this checkbox can only add one criteria row at a time, we'll uncheck it and use instead the Add/Delete Criteria Row dialog. Here, we choose to add two rows:

Another click on the Update Query button produces the following:

We can also remove criteria rows. This can be done by choosing negative numbers in the Add/Delete Criteria Row dialog or by ticking the Del checkbox beside the rows we want to remove. Let's remove the two rows we just added because we don't need them now:

The Update Query button refreshes the page with the specified adjustment.

Adjusting the Number of Criteria Columns

We can add or delete columns by using a similar mechanism: the Ins or Del checkboxes under each column, or the Add/Delete Field Columns dialog. We already had one unused column (not shown on the previous images to save space). Here, we have added one column using the Ins checkbox located under the unused column (this time we will need it):

Automatic Joins

phpMyAdmin can generate the joins between tables in the query it builds. Let's now populate our two unused columns with the title and genre fields from our book table and see what happens when we update the query:

phpMyAdmin used its knowledge of the relations defined between the tables to generate a left join on the author_id key field. A shortcoming of the current version is that only the internal relations are examined, not the InnoDB relations.

There may be more than two tables involved in a join.

Executing the Query

Clicking the Submit Query button sends the query for execution. In phpMyAdmin version 2.11.0, there is no easy way (except by using the browser's Back button) to come back to the query generation page after we have submitted our query. One can use Bookmarks to save the generated query for later execution.

Summary

In this article, we have covered various aspects such as opening the query generator, choosing tables, entering column criteria, sorting and showing columns, and altering the number of criteria rows or columns. We also saw how to use the AND and OR operators to define relations between rows and columns, and how to use automatic joins between tables.

If you have read this article you may be interested to view :

Mastering phpMyAdmin 2.11 for Effective MySQL Management Increase your MySQL productivity and control by discovering the real power of phpMyAdmin 2.11
Published: March 2008
eBook Price: $20.99
Book Price: $34.99
See more
Select your format and quantity:

About the Author :


Marc Delisle

Marc Delisle was awarded "MySQL Community Member of the year 2009" because of his involvement with phpMyAdmin. He started to contribute to the project in December 1998, when he made the multi-language version. He is still involved with phpMyAdmin as a developer and project administrator.

Marc is a system administrator at Cegep de Sherbrooke, Québec, Canada. He has been teaching networking, security, and web application development. In one of his classes, he was pleased to meet a phpMyAdmin user from Argentina. Marc lives in Sherbrooke with his wife and they enjoy spending time with their four children.

Books From Packt

    PHP 5 CMS Framework Development
PHP 5 CMS Framework Development

Learning Joomla! 1.5 Extension Development
Learning Joomla! 1.5 Extension Development

AJAX and PHP: Building Responsive Web Applications
AJAX and PHP: Building Responsive Web Applications

Building Websites with Joomla! 1.5
Building Websites with Joomla! 1.5

WordPress for Business Bloggers
WordPress for Business Bloggers

RESTful PHP Web Services
RESTful PHP Web ervices

ASP.NET 3.5 Social Networking
ASP.NET 3.5 Social Networking

PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax
PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax

Your rating: None Average: 4.8 (4 votes)
MySql Query & Stored Procedure Generator by
MySQL Queries & SP Generator is a unique software which make your development rapid. Now you do not need to write queries & stored procedure manually. Just run this software & point to database then select options you require it will do rest of job. http://www.devskills.com/2011/08/mysql-queries-sp-generator.html
I love phpMyAdmin but I don't stay in the same place long time by
I use phpMyAdmin during 2 years and it's a good way to administrate your database in MySQL server. But I found another way to save my time. I use also different tools that can solve some problems much better. For example I use free MySQL GUI tool especially Visual query builder. You can find it here: http://www.devart.com/dbforge/mysql/studio/mysql-query-builder.html Don't stop just in one tool, always do try to figure out and share your knowledge with others. Thanks.

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
6
1
w
Q
e
V
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software