Get the Most Out of phpMyAdmin's Visual Query Designer

Database programmers and administrators often need to design SQL queries against database structures with which they're not intimately familiar. By using a visual query designer, programmers and admins can cut down on coding errors and save time. FOSS tool phpMyAdmin offers a simple builder that can help anyone design MySQL queries. Here's how to get started designing basic queries visually in phpMyAdmin.

Why use a tool like phpMyAdmin when you can study tables and type up queries by hand? Because that process is prone to error. You may not know what fields are used as keys in the database tables, you may mistype field names in your table joins and select lists, and, if you use a number of relational database management systems, each of which uses a slightly different flavor of SQL, you may trip against crucial differences in syntax.

Software tools like phpMyAdmin help by examining the table structure for you, presenting the list of field names grouped by tables, and marking indexed fields. They let you select and deselect fields using a graphical interface, and finally create a SQL statement appropriate for your relational DBMS. phpMyAdmin in particular allows you to create and edit databases, tables, fields, and indexes, and browse and edit data contained in the tables. It can alter table structures, import and export table structure and data, copy and move tables, and edit privileges. phpMyAdmin has long had a fill-in-the-boxes query designer, but since version 3.4.0 it offers a more visual approach to query design.

The database I'm using here, named qdtest, consists of three tables: top, middle, and lower. Each has a unique auto-incremented ID as its first field; middle has a second numeric field that links the middle table to the top; and the lower table links to the middle.

When you start phpMyAdmin and point to the qdtest database you see three tables listed in the left frame. Click on the Query tab and follow the link to the Visual Builder (if you don't see one, check that you have a recent version) to display a screen like this:



At the top of the right frame is a menu of icons. The chevron icons on the far left and right allow you to control the display of tables and free up the menu for repositioning. The remainder, in order from left to right: save the position (save changes), create a table, create a relation, mark a field, reload, get help, toggle between angular and direct links, snap to grid, toggle between full and minimal table information, export PDF information, and, most importantly in this context, build query.

If you need to navigate away from this frame, you can click on one of the tables in the left column or on one of the star icons on the header of a table.

The three tables are displayed as rectangles, and each of the tables has three icons in its header bar that we'll discuss in a moment. By default the table boxes are displayed collapsed. You can move the boxes around easily by clicking and dragging, which is helpful when you're dealing with a large number of tables.

To display all the tables fully, click on the menu icon (down arrow in a blue circle); you may need to give it two clicks:



Inside each box the unique-indexed fields are marked with a key icon, text fields are marked with a text page icon, and numeric integer and float fields (both indexed and not indexed, but not unique-indexed) have a # icon.

Now remember the icons on the tables' title bars? The first icon on the left lets you toggle a tick mark to include a field in your query output. The V allows you to collapse or expand the individual table box to manage space more efficiently. The star takes you back to the table structure view, where you can change the properties of the table.

The stars on the right of each field row, by contrast, pop up a box that allows you to change the properties of the SQL statement you're building that are related to the field, including clauses such as WHERE, ORDER BY, GROUP BY, HAVING, and field AS aliases.

 

Adding, Removing, and Editing Relations

Now that you've examined the tables, you can graphically build relations between them. First, to set up a relation between topid in the top table and topid in the middle table, click the "create relation" icon once. The icon turns green, and a hint box appears, inviting you to select referenced key. In this case, that is the topid field in the top table. When you click on that, the floating hint box changes to select foreign key. Now click the topid field in the middle table. A blue create relation box appears. Click OK. phpMyAdmin draws a green link between the topid fields in the two tables.

The left end of the link has a small semicircle, and the right end a larger one. If you click the larger one you'll get the option to delete the relation – but don't. Instead, create a relation between the middleid fields in the middle and lower tables.

While the two different sizes of semicircle ends to the link appear to indicate a one-to-many relationship, this is not necessarily the case. Setting up a relationship in reverse, a many-to-one, results in the small and large circles reversed. The larger semicircle just indicates a spot you can click to adjust the properties of the join.



With the relations in place, use the menu icon to toggle between angular and direct links. The shape of the link changes from point-to-point direct lines to horizontal and vertical segmented lines. You can drag table boxes around in the frame, causing the link to follow. The green and blue relation link colors don't mean anything; they just help distinguish the individual joins in the spaghetti that can result from many links crowding into the same space. Red, however, indicates links reserved for deletion.

Building a Query

With the relations in place, you can start to make meaningful queries. For your first query, tick the box next to the toplabel, middlelabel, and lowerlabel fields, then click the menu icon with the white B in a blue circle to build the query. A window pops up on top of the query builder.

At this point you can submit the query to see what the data contains, or copy the query text for further editing outside of phpMyAdmin and cancel the select box.

A few things to note about the output and options: The builder defaults to LEFT joins. You can't specify in the visual builder that you want INNER or RIGHT joins; to use them, you must edit your query outside of phpMyAdmin. Similarly, AS table aliases are not supported in the visual tool, and the tool provides no UNION or subquery capabilities.

Query building can be a process of trial and error to get the result you want. phpMyAdmin's visual query builder makes it easy to try a query, modify it, and try again until you have the desired outcome. While the tool doesn't support the complete range of SQL syntax, the builder helps programmers clear the major hurdles of correct spelling of field names, key field selection, and building SQL statements using standard SQL syntax, and thus leads to greater accuracy and less delay in the development process.

phpMyAdmin is not your only option for these tasks, though it is one of the most popular. For instance, MySQL provides its own Workbench application, and LibreOffice Base has a great SQL designer.
Follow @openlogic
Follow @CloudSwing

This work is licensed under a Creative Commons Attribution 3.0 Unported License
.

Follow @openlogic
Follow @OSCloudServices

This work is licensed under a Creative Commons Attribution 3.0 Unported License
.