I really don't know nothing about SQL.. I know how to insert and select data from database using PHP but besides that, nothing.

I found this view And I am sure that 5 minutes for you to answer this question will give me a lot more knowledge.

I understand the fact the the view is created and given the name film_list and the AS defines what this view is going to retrieve.

Then we select from the Schema or Database film the field film_id and define it AS FID.

Q1. Why do we do that? What is the main purpose of this definition?

... The statement continues until GROUP_CONCAT.

Q2. What is the difference between CONCAT, that already combines the two fields to one element, and GROUP_CONCAT ?

.. Then the FROM syntax. Now we choose the schemas and tables to select from. After reading on w3schools, I understand the INNER JOIN, FULL JOIN, LEFT JOIN and RIGHT JOIN statements, but not the JOINonly statement.

Q3. Which requirements does the JOIN statement have?

.. Okay now.. This one I haven't been able to find anything about.

Q4. What is the ON statement, and what is it good for?

The GROUP BY statement I understand.. The SQL simply groups everything together related to the film_id

CREATE VIEW film_list 
SELECT film.film_id AS FID,
film.title AS title,
film.description AS description,
category.name AS category,
film.rental_rate AS price,
film.length AS length,
film.rating AS rating,
GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR ', ') AS actors
FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id
LEFT JOIN film ON film_category.film_id = film.film_id
JOIN film_actor ON film.film_id = film_actor.film_id 
JOIN actor ON film_actor.actor_id = actor.actor_id 
GROUP BY film.film_id

Q5. What would the correct way to display returned data be?

I assume that the view would create some kind of table during the SELECT statement. Usually I would be able to simply echo a row in PHP. Is that still possible, and if so - how would I have to do? Because usually I echo like this echo $row['field_name'] but how would I know which field to call? Is that why we defined the elements with FID, title, desciption etc.?

In my MySQL database I have 14 different tables each one containing a little bit information about a cooking recipe.

Q6. Would a view be ideal for me to use when having that amount of tables to search in?

I Really hope that some of you please would take your time to answer these questions. It would help me a lot. And then a final Question:

Q7. Which websites or books are the best to teach me how to work with SQL?

  • INNER JOIN and (just) JOIN are the same thing, in SQL. – ypercubeᵀᴹ Feb 7 '13 at 18:33
  • Thanks... It is not because it is SQL specific or something? – Philip Feb 7 '13 at 18:34

A1. You don't have to do that, it's pretty arbitrary. The column alias just renames the id column.

A2. CONCAT() and GROUP_CONCAT are very different -- CONCAT('a','b','c') is just a function that would evaluate to 'abc' GROUP_CONCAT() is an aggregate function that works with GROUP BY; for each group of rows that you "roll up" into a group, GROUP_CONCAT() will create a string based on the value for each row in that group.

A3. JOIN is just shorthand for INNER JOIN

A4. ON defines how the tables are joined. Each set of possible combination rows are examined, and each combination where the ON clause evaluates to true, then you get a match. Visual Join Explanation

A5. I can't find your actual question here.

A6. No, a view doesn't sound like the right approach. If you're searching for the same thing in many tables, then all that stuff should probably be in one tables. But that's a generalization, you haven't described nearly enough about your actual project to give a clear answer.

A7. This question is answered other places.

| improve this answer | |
  • Q5. Basically what I would like to know is how to echo the returned data in PHP, cause I don't know what the returned table will look like. Would I have to do as following echo $row['FID'] or echo $row['film_id'] – Philip Feb 8 '13 at 9:39
  • Q6. In my SQL I have a table for the recipe information (rating, servings etc.) Then i have another table for the titles 'cause there might be titles for multiple languages. Then there is a table for preparations, but this table is made with the possibility of multiple languages and multiple preparations per recipe. There are a lot of data stored in different tables because I don't want the same information to be stored more than one time in the same table. it would be a waste of space. But at the moment I retrieve data from a select statement and then I use the data for a new select.. – Philip Feb 8 '13 at 9:43
  • By the way.. sorry I don't have reputation enough to give you a thumb up.. you definitely deserve it.. I actually learned a lot just by reading your answer.. Thanks – Philip Feb 8 '13 at 10:03
  • A5. If you're not sure, the just do echo "<pre>".print_r($row,true)."</pre>" Since you have the query and the result, you can inspect it yourself. – Gavin Towey Feb 8 '13 at 18:36
  • A6. I think the thing to keep in mind is that a view in MySQL is just a alias to a query -- there are no special efficiency gains to be had, and quite often a view can be less efficient than just writing the query directly. Multi-language support is definitely a challenging things in databases. My advice would be to just go with what works for you and seems reasonable. Then do your profiling later and if you find out your search page takes 5 seconds to load and 4.5 of that time is your search query, then you can decide if you want to optimize it. – Gavin Towey Feb 8 '13 at 18:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.