PHP MySQL. Basic concepts of MySQL and differences from text files.


A list is a text file. Or, say, a list of students in a class journal.
To get an ordered list, we must reorder the list when adding any record. When adding any! And if we want to have two or more types of ordering? For example, the list of students is compiled in alphabetical order, but we are interested in seeing it in terms of academic performance. Start two lists? NO! Start a database.
And to understand the first, most basic principle. The order of the data is only when you select them from the database! This is a very important fact. The data in the database is not stored as in a class journal - in order. And as in the hourglass - a bunch. Do not specify the order of output - will be displayed from the nood. The base itself does not remember which record was the first and which was the last. But it can sort by the attributes specified by the user when creating the table.

The first thing to remember . There is no first and last record in the database. You can talk about the order only when displaying, if the user himself specified this order. For example, by sorting records alphabetically. Here in this sample there is both the first and the last.
If we need to know the order in which records are written to the database, add a field with which to determine this.

The next thing that a database differs from a file is random access. In a file, in order to refer to a line in the middle, you need to sort through all those going to it. When upgrading - even worse. In order to change the line in the string file - you need to rewrite it ALL! Whole. With the same database - everything is simple. We want to choose one line - choose! We want to change one line - we change. We want to delete two - delete!
Note: In fact, the database is not a magic box, and it stores data not in the non-physical ether, but in the same files. And in the same way they rewrite them. But it makes it completely transparent to the user.

With arbitrary access and lack of internal numbering "first-second-third", we face the problem of identifying strings. Suppose we have outputted lines from the database to the screen, and now, clicking on the link, we want to edit one of them. How can I request it? By the contents of one of the fields? But what if this content coincides with a few more records? We need a unique identifier! MySQL has an ingenious solution. One of the fields, usually called `id`, is autoincrement, that is, automatically increasing. When adding each new record, if the id is not specified or the value is zero, the database assigns a value to the id field, one more than the previous one. So we got a unique identifier - none of the lines in this table will have the same id. Now it's very easy to edit or output any entry - just specify its id.
By the way, auto-incremental id has one more side property. Sorting the table for this field, you can get the very notorious first or last record Smile happy

However, there are a lot of mistakes and misunderstandings with this field.

FAQ. ID.
Firstly, this field is mistaken for the numerator. As in the class journal - 1,2,3-th student ... And if the student was expelled from school? And if we choose not all students, but only boys?
Rule one: id to numbering has not the slightest relation! Firstly, because id can go not in order, and secondly, that all the same, we have the order is only for SAMPLE. Which can be anything, id in it can go completely different! If we want to number the results - please, in the output we add the code to PHP, which will do it. IN THIS PICK. It is necessary to number when outputting. It is because the sampling options can be an infinite number.
Let's see why another beginner may need a numbering? To determine the number of selected records. For this, there is a function - mysql_num_rows (). She will help us with ANY sample. While the numbering does not help us at all, for the reasons indicated above.
Rule two: You can not change the record id, never in any way. First, it's just never necessary. Secondly, this is our UNIQUE ID. The id field only looks like numbers. In fact - this is the only way, and at any time to identify the record. Let's say we have a website with news. Someone put a link to the news with id = 1. Then we changed this id. As a result, a person will come to the wrong link. If you need to renumber it, then you simply do not need an autoincrement field.

The SQL language.
Now a little bit about the SQL language. This, as I said, is a genius invention of mankind. Almost artificial intelligence. Judge for yourself.
In order to choose the names of all the pupils of the school, students in class 7A, sorting them alphabetically, you should write this query:
SELECT lastname FROM school WHERE klass='7A' ORDER BY lastname
This is an almost meaningful proposal in English!
Let's try to make a translation:
ВЫБРАТЬ фамилии ИЗ школы [такие,] ГДЕ класс равняется 7А, ОТСОРТИРОВАВ ФАМИЛИИ ПО АЛФАВИТУ
Well, is not it bad? We are practically in the natural language telling the database what we need - and it gives us the right lines in the right order! And all this is ONE STITCH! In this case, the complexity of the sample can be any. If we chose from a text file - we would have to write the program not on one sheet. And here - one line!
Well, how? Do you still want to file files?

FAQ. Transferring data between different servers.
Data transfer via dump files containing INSERT requests.
That is, on the server from which we want to transfer the database, we generate a file with INSERT requests for each row of each table in the database. And on the remote server, we simply execute these requests.

The most correct and direct way to get a dump is to use the command line utility mysqldump
Go in DOS or in the shell and write
mysqldump -u<логин> -p<пароль> база > dump.sql
We get the file dump.sql, which should be transferred to a remote server and executed there.
To do this, call mysql shell like this:
mysql -u<логин> -p<пароль> база < dump.sql
Everything, the database is moved.
If you do not have access to the shell, you can use the PHP script Sypex Dumper

Practical use.
A very good selection of materials that teach you how to work with the database is on the site "PHP in details".