MySQL – it’s easy!

First let’s answer the question – what is MySQL? It is a Database (DB) in which data is stored in a structured way. The second question follows – why to refuse from simple and convenient files to a complex database?

Because the files generated by any script based on the file database (guestbooks, CMS, forums), gradually begin to increase in size, as well as their number is steadily growing. Searching is very difficult.

After overcoming some threshold (1000, 10000 files…) the script starts to slow down eerily, and you have to wait a long time for it to finish its work. The database is deprived of these drawbacks – even if there are a million records in the table, the search among them will take a fraction of a second. That’s how they’re designed to manipulate a huge amount of data. But here the question arises – how to work with the database, in this case with MySQL? This article is just about that.

Connecting to the server

To connect to the MySQL server you need to pass 4 parameters:

• Host. This is the server on which MySQL server is located. In absolute majority of cases the host is “localhost”.
• Username. The name of the user that can work with this table. On local computers, “root” is set by default. On hosting – depending on you.
• Password. Password of the selected user. By default, it is an empty string – “”.
• Database name. Database name, as you called it. The default is “test”.

You need to connect to the server with a command:

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

Select a specific Database:

mysql_select_db("Database name", $msconnect);

You should close the connection with the command:

mysql_close($msconnect);

And so the full example of connection, selecting the desired table and closing the connection:

<?
$mshost = "localhost"; // Host
$msuser = "root"; // Username
$mspassword = ""; // Password
$msname = "test"; // Database name
$msconnect = mysql_connect($mshost, $msuser, $mspassword);
mysql_select_db($msname, $msconnect);
mysql_close($msconnect);
?>

Table creation. Database filling with information

So we connected to the MySQL server and selected the database. What’s next? Next, we need to create a new table. Database management in MySQL is controlled using a special SQL query language. At the beginning it seems scary, incomprehensible and complex, but after a while, when you are actively working with the database, you can easily read the most complex SQL queries (itself is also confused in the beginning …). Here we go.

Transferring the SQL query to the database

Transferring the SQL query to the database. There is a single command for this purpose:

mysql_query("SQL query", "connected database");

Let’s try to create a table. To do this, let’s pass the SQL query to the server:

mysql_query("CREATE TABLE name_table") ( field1 of type1,
field2 of type2, field3 of type3)", "connected database");

You can create as many fields as you like. The substitution of Type Fields1 is written:

• INT – integers
• TEXT – text information

These are the two main field types. Actually there are a lot of them, but it will take too much space to list them. So, we’ve sorted out how to create tables. An example of how to use them:

mysql_query(«CREATE TABLE test_zero ( num INT, title TEXT,
text TEXT)»,$msconnect);

Adding information to the database

There is the same command for this, only another SQL query:

mysql_query("INSERT INTO name_table VALUES ("what to stick in field1',
'something else in field 2', 'and finally in field 3')', 'the connected database');

I don’t think there will be any problems here. The only thing is to enter the values of a field with the TEXT type in single quotes. And, as always, an example:

mysql_query("INSERT INTO test_zero VALUES (1, 'Article title'),
'Article Text')', $msconnect);

Changing the information in the database

This command is used for this purpose:

mysql_query("UPDATE name_table SET(field1='value1', field2='value2')
WHERE expression", "connected database");

With this command we update the records specified in brackets, and with this table if they fit any condition (WHERE …). We will consider this a little bit later. Now, this is an example:

mysql_query("UPDATE test_zero SET(num='2', title="Header 2')".
WHERE num=1", $msconnect);

Now completely – connect, create a table, fill the information, update it and close the connection:

<?
$mshost = "localhost"; // Host
$msuser = "root"; // Username
$mspassword = ""; // Password
$msname = "test"; // Database name

$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, 'Article title',
'Article Text')', $msconnect);

mysql_query("UPDATE test_zero SET(num='2', title="Title 2')")
WHERE num=1", $msconnect);
mysql_close($msconnect);
?>

As a result, we will have a new test_zero table with one record.

Getting information from the database

Let’s say we have a large database with hundreds of records. So how do we get those records? It’s very simple:

$res=mysql_query("SELECT field1, field2, field3 FROM table_name");
while($row=mysql_fetch_array($res))
{
$field1=$row[field1];
$field2=$row[field2];
$$row[field3=$row[field3];
}

With the SELECT command we get the table entries. The lines “field1, field2, field3” may be replaced by an “*” sign, which means that all fields of the records must be read. You can select only one field that you need. This speeds up the database operation.

What does the while loop do? It means that as long as there are records in the table, it will place the values of their fields in the array using the “mysql_fetch_array” function, and as soon as the records are over, the cycle will stop. To get the data from the array, you can use the method I indicated in the example. Within the loop, you can generate, for example, articles by pulling data out of the database.

Here is 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«;
}

Terms of receiving information from the database

When there are hundreds of records in a table, it is not always reasonable to get everything. For this purpose, some condition is introduced:

$res=mysql_query("SELECT * FROM table_name WHERE field1 'value' character
OPERATOR field2 sign 'value');

Let’s examine the “field1 character ‘value'” line in detail. Field1 is the field’s name, for example, “title”. Sign – a logical expression that accepts values:

• = – equal
• > – more
• < – less
• != – does not matter

And also quite a lot, these are the main ones.

Operator is a logical operator:

• AND – logical “and”
• OR – logical “or”

They’re also basic.

Example of pulling out of a table of records whose number exceeds 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 speak, the final example of database application:

<?
$mshost = "localhost"; // Host
$msuser = "root"; // Username
$mspassword = ""; // Password
$msname = "test"; // Database name

$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, 'Article title',
'Article Text')', $msconnect);

mysql_query(«UPDATE test_zero SET(num=‘2’, title=‘Title 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«;
}
?>

That’s it! Of course, this is a very simple example, but it will allow you to learn the simplest work with the database. On the basis of this knowledge you can make a guestbook, a script displaying news, statistics system, etc.

MySQL Tutorial for Beginners (video)