Open Source Software Technical Articles

Want the Best of the Wazi Blogs Delivered Directly to your Inbox?

Subscribe to Wazi by Email

Your email:

Connect with Us!

Current Articles | RSS Feed RSS Feed

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:

Step 1

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:

Step 2

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.

19a98812-f823-48dc-841e-bf029c63c6d7

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.



Step 3

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.

Step 4

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.



Step 4

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.



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




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

Comments

I can't find a link to the visual designer, and I'm using 3.4.11 :( Any ideas?
Posted @ Monday, December 03, 2012 6:35 AM by Jan
I'm on phpMyAdmin 4.0.5 and cannot see the visual designer link either under the query link. 
 
I'm wondering if this is because the extra tables for PMA configuration storage need to be created: 
 
http://docs.phpmyadmin.net/en/latest/setup.html#phpmyadmin-configuration-storage 
 
 
 
 
Posted @ Saturday, October 05, 2013 2:19 PM by Keith
OK - I think I've found this out now: 
 
You need to turn on the advanced PMA features: 
 
If you have this on the homepage for your PMA installation: 
 
The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. To find out why click here. 
 
Open that link in a new tab and you will see something like: 
 
$cfg['Servers'][$i]['designer_coords'] ... not OK [ Documentation ] 
Designer: Disabled 
 
Where it says 'not OK' click the documentation link to open in a new tab. This will take you to the following section of the documentation about configuring PMA: 
 
$cfg['Servers'][$i]['designer_coords'] 
Type: string 
Default value: '' 
 
Since release 2.10.0 a Designer interface is available; it permits to visually manage the relations. 
 
To allow the usage of this functionality: 
 
set up $cfg['Servers'][$i]['pmadb'] and the phpMyAdmin configuration storage 
put the table name in $cfg['Servers'][$i]['designer_coords'] (e.g. pma__designer_coords) 
 
******************************* 
 
I've not enabled these advanced features yet - I'm still working through the PMA user documentation so I know exactly how to configure and use PMA to get the best from it. 
 
So it looks like you will have to setup PMA to use the advanced features, and then you should see the Visual Query Designer enabled under the Query tab. 
 
HTH - Keith Roberts
Posted @ Saturday, October 05, 2013 2:56 PM by Keith
Right, you need to set up PMA. If you are using a recent version of phpMyAdmin, note that in the template pma setup there are double underscores in some locations, I guess to ensure that the default fails until the double underscores are singled up.
Posted @ Saturday, October 05, 2013 4:09 PM by Colin Beckingham
Also note that you won't see the designer until you have selected a database, and then it will be under the "More" tab.
Posted @ Saturday, October 05, 2013 4:11 PM by Colin Beckingham
Thanks for pointing that out Colin. 
 
All the required tables are included already in the config.sample.inc.php in the base installation directory. 
 
/* 
* phpMyAdmin configuration storage settings. 
*/ 
 
/* User used to manipulate with storage */ 
// $cfg['Servers'][$i]['controlhost'] = ''; 
// $cfg['Servers'][$i]['controluser'] = 'pma'; 
// $cfg['Servers'][$i]['controlpass'] = 'pmapass'; 
 
/* Storage database and tables */ 
// $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin'; 
// $cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark'; 
// $cfg['Servers'][$i]['relation'] = 'pma__relation'; 
// $cfg['Servers'][$i]['table_info'] = 'pma__table_info'; 
// $cfg['Servers'][$i]['table_coords'] = 'pma__table_coords'; 
// $cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages'; 
// $cfg['Servers'][$i]['column_info'] = 'pma__column_info'; 
// $cfg['Servers'][$i]['history'] = 'pma__history'; 
// $cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs'; 
// $cfg['Servers'][$i]['tracking'] = 'pma__tracking'; 
// $cfg['Servers'][$i]['designer_coords'] = 'pma__designer_coords'; 
// $cfg['Servers'][$i]['userconfig'] = 'pma__userconfig'; 
// $cfg['Servers'][$i]['recent'] = 'pma__recent'; 
 
so that file only needs to be copied to config.inc.hp and those tables un-commented and the pmadb set up. 
Posted @ Saturday, October 05, 2013 4:24 PM by Keith
A slight typo there: 
 
so that file only needs to be copied to config.inc.hp 
 
I meant so that file only needs to be copied to config.inc.php 
 
BTW: thanks for a good tutorial about the Visual Designer. I will be coming back to this when I have finished reading the PMA documentation, and after setting up those advanced features!
Posted @ Saturday, October 05, 2013 4:27 PM by Keith
Post Comment
Name
 *
Email
 *
Website (optional)
Comment
 *

Allowed tags: <a> link, <b> bold, <i> italics