topic

MySQL is simply!

First we shall answer a question - that such MySQL? It is the Database (DB) in which it is structured the data are stored{kept}. It is necessary - to refuse what for the second question simple and convenient files to a complex DB? Because the files generated what or script on the basis of a file DB (guest books, CMS, forums), gradually start to increase in size and as their quantity{amount} steadily grows. Search is strongly complicated. After overcoming some threshold (1000, 10000 files..) the script starts to brake terribly, and it is necessary to wait long when he will finish the job. The DB are deprived these lacks - even if in the table there will be one million recordings, search among them will go shares of second. So they are arranged to manipulate huge quantity{amount} of the data. But here also there is a question - and how to work from a DB, in this case with MySQL? This clause{article} just about it.

Connection to the server.

To MySQL server to be connected to it , it is necessary to pass 4 parameters:

? A host. It serover on which MySQL server is located. The host is equal overwhelming majority "localhost".

? A login name. A login name which can work with this table. On local computers on a default it is put "root". On a hosting - depending on you.

? The password. The password of the chosen user. On defaults it is equal to an empty line - " ".

? A name of the Database. A name of a DB as you have named her . By default - "test".

To be connected to the server it is necessary from the help of the command:

$msconnect=mysql_connect ("Host", "User", "Password");

To choose a concrete Database:

mysql_select_db (" the Name of a database ", $msconnect);

To close connection it is necessary the command:

mysql_close ($msconnect);

And accordingly a full example of connection, vybiranie the necessary table and closing podkljucheinija:

$mshost = "localhost"; // the Host

$msuser = "root"; // the Login name

$mspassword = " "; // the Password

$msname = "test"; // the Name of a database

$msconnect = mysql_connect ($mshost, $msuser, $mspassword);

mysql_select_db ($msname, $msconnect);

mysql_close ($msconnect);

Creation of tables. Filling with the information of a DB

We were connected to MySQL server and have chosen a DB. What is farther? It is necessary to create the new table further. Managements of a DB in MySQL copes with the help of special language of searches SQL. Firstly he seems terrible, not clear and complex , but after a while at active job from a DB you can read easily the most complex with a kind SQL-searches (itself as it was confused in the beginning..). We shall start.

Transfer SQL of search of a DB. For this purpose there is a unique command:

mysql_query ("SQL-search", " the connected DB ");

Let's try to create the table. For this purpose we shall pass the server such SQL search:

mysql_query (" CREATE TABLE imja_tablicy (pole1 tippolja1,

pole2 tippolja2, pole3 tippolja3) "," the connected DB ");

It is possible to create some fields. Zamesto TipPolja1 it is written:

? INT - integers

? TEXT - the text information

These are 2 basic such as a field. Actually it is a lot of them, but to list{transfer} them is will borrow{occupy} lishkom a lot of place. So, have understood with creation of tables. An example of use:

mysql_query (" CREATE TABLE test_zero (num INT, title TEXT,

text TEXT) ", $msconnect);

Addition of the information in a DB

For this purpose there is a same command, only other SQL-search:

mysql_query (" INSERT INTO nazvanie_tablicy VALUES (' what to thrust in pole1 ',

' Still that that in pole2 ',' and at last in pole3 ') ", " the connected DB ");

I think, here any complexities will not arise. The only thing, values of a field with type TEXT to enter in unary inverted commas. And, as always, an example:

mysql_query (" INSERT INTO test_zero VALUES (1, ' Heading of clause{article} ',

' The text of clause{article} ') ", $msconnect);

Change of the information in a DB

For this purpose such command is used:

mysql_query (" UPDATE nazvanie_tablicy SET (pole1 ='znachenie1 ', pole2 ='znachenie2 ')

WHERE the expression "," the connected DB ");

Such command we update the recordings specified in brackets, and the given table if they approach on what or to a condition (WHERE...). It we consider hardly later. Now - an example:

mysql_query (" UPDATE test_zero SET (num = '2', title ='Zagolovok 2 ')

WHERE num=1 ", $msconnect);

Now completely - connection, creation of the table, filling of the information, its{her} updating and closing of connection:

$mshost = "localhost"; // the Host

$msuser = "root"; // the Login name

$mspassword = " "; // the Password

$msname = "test"; // the Name of a database

$msconnect = mysql_connect ($mshost, $msuser, $mspassword);

mysql_query (" CREATE TABLE test_zero (num INT, title TEXT,

text TEXT) ", $msconnect);

mysql_select_db ($msname, $msconnect);

mysql_query (" INSERT INTO test_zero VALUES (1, ' Heading of clause{article} ',

' The text of clause{article} ') ", $msconnect);

mysql_query (" UPDATE test_zero SET (num = '2', title ='Zagolovok 2 ')

WHERE num=1 ", $msconnect);

mysql_close ($msconnect);

In a result at us the new table test_zero in which there will be one recording will appear.

Reception of the information from a DB.

We admit{allow}, at us the enormous DB in which hundreds recordings are stored{kept}. How these recordings to receive? Very simply:

$res=mysql_query (" SELECT pole1, pole2, pole3 FROM imja_tablicy ");

while ($row=mysql_fetch_array ($res))

{

$pole1 = $ row [pole1];

$pole2 = $ row [pole2];

$pole3 = $ row [pole3];

}

SELECT command we receive recordings the table. Zamesto lines " pole1, pole2, pole3 " there can be a sign "*" which means, that it is necessary to read all fields of recordings. It is possible to choose only one field which is necessary. It accelerates job of a DB. What does{makes} a cycle while? He means, that while in the table there are recordings, he will place values of their fields in a file with the help of function "mysql_fetch_array" and as soon as recordings will be terminated, the cycle will stop. To obtain from a file the data, it is possible to use way which I have specified in an example. Inside a cycle it is possible to generate, for example, clauses{articles}, pulling out the data from a DB. An example:

$res=mysql_query (" SELECT * FROM test_zero ");

while ($row=mysql_fetch_array ($res))

{

$num = $ row [num];

$title = $ row [title];

$text = $ row [text];

echo " ($num) - $title <br> <p align=justify> $text ";

}

Conditions of reception of the information from a DB

When in the table of hundred recordings to receive all not always it is expedient. For this purpose enter what or a condition:

$res=mysql_query (" SELECT * FROM imja_tablicy WHERE pole1 a sign ' value '

The OPERATOR pole2 a sign ' value ' ");

Let's consider more in detail a line " pole1 a sign ' value ' ". Pole1 - the name of a field, for example "title". The sign - logic expression, accepts values:

? = - it is equal

? > - it is more

? <-it is less

? ! = - it is not equal

lIkh as much enough, it is the basic.

The OPERATOR - the logic operator:

? AND - logic "¿"

? OR - logic "or"

It as the basic.

Example vytaskivanija from the table of recordings which number{room} is more 10:

$res=mysql_query (" SELECT span style = " color: red; " */span FROM span style = " color: black; " test_zero/span WHERE span style = " color: red; " num/span> 10 ");

And, so to say, a final example of application of a DB:

$mshost = "localhost"; // the Host

$msuser = "root"; // the Login name

$mspassword = " "; // the Password

$msname = "test"; // the Name of a database

$msconnect = mysql_connect ($mshost, $msuser, $mspassword);

mysql_query (" CREATE TABLE test_zero (num INT, title TEXT,

text TEXT) ", $msconnect);

mysql_select_db ($msname, $msconnect);

mysql_query (" INSERT INTO test_zero VALUES (1, ' Heading of clause{article} ',

' The text of clause{article} ') ", $msconnect);

mysql_query (" UPDATE test_zero SET (num = '2', title ='Zagolovok 2 ')

WHERE num=1 ", $msconnect);

mysql_close ($msconnect);

$res=mysql_query (" SELECT * FROM test_zero ");

while ($row=mysql_fetch_array ($res))

{

$num = $ row [num];

$title = $ row [title];

$text = $ row [text];

echo " ($num) - $title <br> <p align=justify> $text ";

}

Well that's all! Certainly, it is very simple example, but will allow you will learn the elementary to job from a DB. On the basis of the received knowledge it is possible to make the guest book, a script deducing{removing} news, system of statistics, etc.

If this clause{article} poran`she has got to me - I would not began to write her . When I yet did not know MySQL, I have rummaged clauses{articles} 10. Has quickly enough learned to create tables and to fill in them. And to deduce{remove} it was impossible. It was necessary practically at random studies. Specially for this purpose I also have written this clause{article}.

P.S: On registration of this clause{article} left approximately three times more time, than on its{her} spelling. At registration I more than 50 times reloaded clause{article}. So that:).

Attention!!! Given clause{article} can be reprinted only with the instruction{indication} of the author - Hyacinths Andrey, and as direct link to a site of the author - http://spryt.ru/. The reprint should conduct in HTML a code, starting{beginning} from a code <! - the Beginning of clause{article}-> <style....> and zakachivaja a code </div> </div> <! - the End of clause{article}->. Accommodation of given clause{article} at forums is forbidden! This paragraph as should be specified!