MySQL 5 How to Create a VIEW – and what is it anyway?

We were recently disussing adding the ship-via method to the orders table because we had simplistic software mapping into our (or more precisely out of) our osCommerce database – it merely maps field for field.

So when you want to query something even mildly complex you have trouble – for example, the temptation to duplicate the ship-via field in the orders table.

That’s where VIEWs come in. A view looks like a table, but that’s about it. Under the hood, it’s really a SELECT statement that you access as if it were a table. The advantage being, for complex queries, a greatly simplified call.

Here’s a view I created in one of my osCommerce shops:

mysql> CREATE VIEW shippingView AS
SELECT orders.orders_id, orders.billto_lname, orders_total.title, orders_total.value

FROM orders
LEFT JOIN orders_total ON orders.orders_id = orders_total.orders_id
WHERE orders_total.class = ‘ot_shipping’;
Now, let’s see what shippingView will look like to the outside world:

mysql> DESCRIBE shippingView;
| Field        | Type          | Null | Key | Default | Extra |
| orders_id    | int(11)       | NO   |     | 0       |       |
| billto_lname | varchar(64)   | NO   |     |         |       |
| title        | varchar(255)  | YES  |     |         |       |
| value        | decimal(15,4) | YES  |     | 0.0000  |       |

Now, if I were to run the SQL SELECT I used in the ‘CREATE VIEW’, I would get everything, right? Right, I would. But check out what I can do with the VIEW shippingView;
mysql> SELECT * FROM shippingView WHERE orders_id = ‘300’;
| orders_id | billto_lname | title                    | value   |
|       300 | Jadhav       | UPS (UPS Ground( <acronym| 33.8200 |
1 row in set (0.01 sec)
Hope this helps before some poor soul duplicates a field in their database!