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
Q1. Why do we do that? What is the main purpose of this definition?
... The statement continues until
Q2. What is the difference between
CONCAT, that already combines the two fields to one element, and
.. Then the
FROM syntax. Now we choose the schemas and tables to select from.
After reading on w3schools, I understand the
LEFT JOIN and
RIGHT JOIN statements, but not the
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?
GROUP BY statement I understand.. The SQL simply groups everything together related to the
CREATE VIEW film_list AS 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
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?