Recently a friend of mine contacted on how to simplify a query. The query in question involved so many joins that were even to him getting a little frustrating trying to master every property . So we set out on the voyage. The solution to his problem being creating a view which would simplify his query from what is shown here below to a simple select * from view_name.
1. Open phpmyadmin by using the address http://localhost/phpmyadmin from your favorite browser.
2. This should list on the left all the databases that mysql server on your machine is hosting. Click on the database that has the tables you are reading data from. Am assuming you keep all related data in the same database.
3. Click on the SQL tab. This should give you a large text area where you will be required to paste your original apparently large query.
4. Run the query by clicking “GO”.
5. This should return a result set if the query is ok. It’s at this point that we create a view which upon select * from it will give us exactly the same result as what we currently have from the complex query. A query like the one in the image below might need simplification.
At the bottom of this page you will usually see various options.
6. Click on the CREATE VIEW option. It returns a similar page as the image below. Give the view a name and check the CASCADED CHECK OPTION. When done click “GO”.
NOTES We now have a view which follows the table specified in the query. The cascaded check option means that we have a bidirectional communication between the view and the related tables. If we delete anything from the view it’s deleted from the table and the reverse is also true. We can access the same data now by simply using a simple select query.
This work is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 3.0 United States License.
No comments:
Post a Comment