Views in MySQL?

sledgeweb

Hello, this is more a mysql question than PHP, but since the two are used together so frequently, I though someone here might have an answer.

In MS-SQL, you can create VIEWS. Basically, something like a table, but the data it holds comes from other tables. So, you can have a collection of data that pulls information from other tables when you hit it. Does MY-SQL have anything like this?

Say for instance, I have a film rating site. And I record in a table what a user has ranked a film. Now, I want to have an overall rating for each film. So, I could do a select statement and pull all ratings for the particular film, then count the rows, and add up the ratings and divide to get an average. BUT, it would be nice if I could just have a view that grouped all the rating by film ID and held an average. Any ideas?

Thanks,
Sledge

AstroTeg

Views in MySQL will be available in v5 or v5.1. The Postgres guys would know for sure, but I think Postgres allows for views.

There's a number of nifty tricks MS SQL can do but is not currently available in MySQL. From what I've read, Postgres may be a better fit for you.

Sxooter

Postgresql has had views for quite some time. they can be made updateable via user triggers, but simple updateable views are not implemented automatically like the spec says they should be.

That said, I've never used a simple updateable view, there's always been more to it than that, so no great loss there.

also unions, user defined functions (in about 12 languages) subselects, transactions, constraints, etc... etc...

Incredible amounts of optimzation has gone into the cost based query planner in the last year.

MySQL: content storage with good speed but poor relational integrity constraints.
Postgresql: Relational Database with full integrity built right in. Used to be a bit pokey, but is now doing quite well, and especially good at handling high parallel loads.