Creating views with MySQL instead of filtering out records with SQL
I was just reading Pro PHP security and I‘ve learned something very handy. When you‘re, for example using an active field which marks active pages in your table, Instead of including “WHERE active=‘1’” every time you want the get the active pages you can create a view table that filters out these records. In my example, I‘ve used a table ‘pages’ and created a view table ‘pages_view’. My pages table contains these fields:
ID (PK, INT)
title (varchar)
text (varchar)
active (ENUM 0,1)
to create the view use the following SQL command:
CREATE VIEW pages_view AS SELECT * FROM pages WHERE active=1;
The disadvantage is that you now have two tables instead of one. The view table will automatically be updated when something changes in the original table. It might save you a lot of time when you have a lot of filter criteria to work with.
