I use phpMyAdmin to administer my remote MySQL databases and find the GUI good enough for most tasks, but I found myself wondering “How do I create a view in phpMyAdmin?” A careful review of the GUI turned up nothing, but this seems like such a common task I was surprised there was no way to create a view other than directly via SQL. A few web searches later and I realized there was a way, it was just not obvious until you figure it out. So here’s my quick tutorial on creating a MySQL view directly in phpMyAdmin.
It’s simple really; create, run, and verify the SQL SELECT statement that generates the results of the view you’re looking to create, and then use that to create the view.
Lets say we have a 3 column table of information related to cameras:
But often we’d prefer a summary of the brand, style, and price (like ‘Canon SX20IS ($479)’. After coding this in our scripts three of four times, maybe doing it slightly differently in some cases (‘Canon – SX20IS – $479’ perhaps), it becomes clear that a MySQL view would make sense.
Step 1. Write the SQL SELECT statement that returns the columns we want for the view
We’ll use the CONCAT operator to create the format of summary string we consistently want. To simplify our script even further, we’ll specify the default order we want, as well.
Step 2. Verify that the SQL SELECT statment returns the correct results
Run the query and make sure the results contain everything we’re looking for in the view. In this case we have the camera style as one column, and the summary in the second column. Notice also that the prices are increasing:
Step 3. Create the SQL View from the result set
Click on the CREATE VIEW link to finally create the view. There are a number of options that you should be aware of, but in many cases the defaults will do.
You will have to supply:
- VIEW name
- Column names – a comma delimited list of the names to use for each column in the view
Once you’re done, click the Go button to create the view!
And you’re done. You’ve now created a MySQL view and can use that in place of more complicated SQL in your applications. To work with the view right after you’ve created it you may have to re-load the database in phpMyAdmin, but then it will appear alongside you ‘normal’ tables: