However, this is not enough to create even the simplest website with a registration form. This requires WEB PROGRAMMING SKILLS. We are heading towards this part of the exciting journey.

Frontend and Backend

Programming can be divided into 2 parts: Frontend and Backend.

Frontend development- This appearance site, the result of this work is seen by users on the monitor screen.

Backend- this is the internal part of the site, you see the result of this work through frontend development. When you make any request: in a search, click an active link, button, etc., you almost instantly get the result on the screen.

This path from your request to receiving the result is the invisible side of the work of any site: the logic of all requests to produce the required result.

Let's give an analogy with mental calculation: add 32 and 47. No one sees your thought process, it is inside you. You use your own calculation logic, and the site also uses backend development logic to complete the task.

Backend Development Basics - PHP/MySQL

PHP is a programming language for creating websites.

Web site(website) - collection HTML pages and others electronic documents, with a common design, interconnected by hyperlinks and located on the same domain name.

PHP integrates well into the layout of the HTML language you already know.

PHP is used when you need to store some information on the server.

Language PHP answers FOR LOGIC behavior of the site, but just knowing the language will not make you a developer, you need to learn BUILD ALGORITHMS.

For example, we build logic in such a way that administrator only can edit a blog post or view a sales report in an online store; these actions are not available to other users.

Any language is simple if you learn to understand it!

Our task is to teach you READ other people's code and CREATE your own.What does it mean?
When you see the code of an already written program, then reading it, you can easily imagine what will happen on the monitor screen as a result of the logic of the algorithms.

To learn web programming, figure out what building materials you have for website building. On at this stage it's a language PHP programming and base MySQL data.

To understand the language, it is enough to understand the syntax, basic definitions and rules of application.

Your main building materials:

Variables and their types,

Constants, operators,

Arrays and working with them,

Functions and creating your own functions,

Cycles,

Sending and receiving data from HTML forms (GET, POST),

Object-oriented programming (Classes, encapsulation, polymorphism, inheritance, interfaces)

Website Security Basics

It is important to know HOW to protect your creation. Learn to competently write programs in PHP so that the likelihood of your site being hacked tends to zero.

Let's move on to MySQL/MariaDB databases

Almost every website has a database. This is a repository in which information used on the site is collected and structured. For example, if this is a blog, then the database stores: topics of articles, articles themselves, names of authors, date of publication, title of section, comments, etc.

Are you interested in knowing your website statistics? How many visits, time spent on the site, number of new users, how many sales there were, etc. This is analytical information and it is also stored in the database.

MySQL/MariaDB is one of the most popular and most widespread DBMS (database management system) on the Internet. Most of the website data is stored in MySQL/MariaDB.

SQL- a database query language that allows you to select data from tables, sort data, and summarize numerical data stored in table cells. Database tables are similar to Excel tables, but SQL allows you to work with the data in a convenient way.

It is necessary to learn how to work with databases.
Basic operations:
- data input and output,
- sorting,
- update,
- deletion.

During the training process, you become familiar with the site administration system and design your first database table structure.

Make friends with them in your online website building activities.

Programs are written in PHP for: user registration and authorization, site search, you can create your own engines, a computing robot, automate repetitive algorithms, create an online store, etc.

There are a lot of vacancies for work as a PHP programmer! PHP is one of the most common web programming languages! That is, knowing just one programming language, you can implement thousands of all kinds of projects, both for yourself and for other people, for good money!

PHP is quite simple and this will be very important for those who have never programmed before! Once you learn PHP, you will learn other programming languages ​​easier.

The secret to moving forward steadily is to take the first step. The secret of the first step is to break down complex tasks into simple ones and start with the very first one.

Mark Twain

Be at your maximum!Join the web!
beONmax team

There is no need to wait for the group to fill up or for the start date of the course to arrive - at beONmax you start training right away!

SQL - Lesson 1. Creating a database and tables

So, you have installed MySQL, and we are starting to master the SQL language. In Lesson 3 on Database Basics, we created a conceptual model of a small database for the forum. It's time to implement it in the MySQL DBMS.

To do this, first of all you need to start the MySQL server. Let's go to system menu Start - Programs - MySQL - MySQL Server 5.1 - MySQL Command Line Client. A window will open asking you to enter a password.

Press Enter on the keyboard if you did not specify a password when setting up the server, or specify a password if you specified one. We are waiting for the mysql> invitation.

We need to create a database, which we will call forum. There is an operator for this in SQL create database

Create database database_name;

The maximum length of a database name is 64 characters and can include letters, numbers, the "_" character, and the "$" character. The name may begin with a number, but should not consist entirely of numbers. Any database query ends with a semicolon (this character is called a delimiter). Having received the request, the server executes it and, if successful, displays the message "Query OK ..."

So, let's create a forum database:

Press Enter and see the response “Query OK...”, meaning that the database has been created:

It's that simple. Now we need to create 3 tables in this database: topics, users and messages. But before we do this, we need to tell the server which database we are creating the tables in, i.e. you need to select a database to work with. For this purpose the operator is used use. The syntax for selecting a database to work with is as follows:

Use database_name;

So, let’s choose our forum database for work:

Press Enter and see the response “Database changed” - the database is selected.

You must select a database in each session of working with MySQL.

To create tables in SQL there is an operator create table. Creating a database has the following syntax:

Create table table_name (first_column_name type, second_column_name type, ..., last_column_name type);

The requirements for table and column names are the same as for database names. Each column has a specific data type associated with it, which limits the type of information that can be stored in the column (for example, preventing letters from being entered into a number field). MySQL supports several data types: numeric, string, calendar, and a special type NULL, which denotes no information. We will talk about data types in detail in the next lesson, but for now let's return to our tables. In them we have only two data types - integer values ​​(int) and strings (text). So, let's create the first table - Topics:

Press Enter - the table is created:

So, we created a topics table with three columns:
id_topic int - topic id (integer value),
topic_name text - topic name (string),
id_author int - author id (integer value).

Let's create the remaining two tables in a similar way - users (users) and posts (messages):

So, we have created a forum database and there are three tables in it. Now we remember this, but if our database is very large, then it is simply impossible to remember the names of all the tables and columns. Therefore, we need to be able to see what databases we have, what tables are present in them, and what columns these tables contain. There are several operators for this in SQL:

show databases- show all available databases,

show tables- show a list of tables in the current database (you must first select it using the operator use),

describe table_name- show a description of the columns of the specified table.

Let's try. Let's look at all the available databases (you have only one so far - forum, I have 30, and they are all listed in a column):

Now let’s look at the list of tables in the forum database (to do this, you must first select it), do not forget to press Enter after each request:

In the answer we see the names of our three tables. Now let's look at the description of the columns, for example, the topics table:

The first two columns are familiar to us - this is the name and data type, the values ​​of the rest we still have to find out. But first, we will still find out what types of data there are, which ones and when to use.

And today we will look at the last operator - drop, it allows you to delete tables and databases. For example, let's delete the topics table. Since two steps ago we selected the forum database for work, now there is no need to select it, you can simply write:

Drop table table_name;

and press Enter.

Now let's look at the list of tables in our database again:

Our table is indeed deleted. Now let's delete the forum database itself (delete it, don't be sorry, it will still have to be redone). To do this we write:

Drop database database_name;

and press Enter.

And verify this by querying all available databases:

You probably don’t have a single database; I have 29 of them instead of 30.

That's all for today. We learned how to create databases and tables, delete them and retrieve information about existing databases, tables and their descriptions.

For those who want to learn PHP, we can recommend the excellent "PHP Tutorial" from the site PHP5.RU
The course is in the process of being written, but already there are links to individual lessons from it in various sections of this FAQ. And, believe me, it's worth it.
I can’t help but recommend the wonderful material by Vadim Tkachenko AKA Bizon “Introduction to PHP and MySQL”. It was even published as a separate book, and now, corrected and expanded, it is posted on the website
"PHP in detail." This resource stands apart. Unlike the previous ones, only a sadist can recommend reading it in its entirety - there is too much information there. but that's the beauty of it. This is an inexhaustible resource of information on PHP. The only note is to pay attention to the date the article was written. You shouldn’t really trust anything written before 2003.
Well, and, of course, this site, http://site
If you haven't read it in its entirety yet, be sure to do so. Here are the problems that EVERYONE who writes PHP will face sooner or later.

Software.
To work with PHP under Windows, you need to install the following programs:
- Apache web server (5Mb)
- PHP itself (10Mb)
- optional - MySQL (23Mb).
The setup is very simple. Apache is installed by the installer. Where it asks for your server name and administrator email, you need to write localhost and your email twice.
PHP is unpacked from a zip file into any directory of your choice (standard - C:\PHP) and must be configured as an Apache module. To do this, you need to perform three steps:
- rewrite the php5ts.dll file to the WINDOWS directory
- in the httpd.conf file (C:\Program Files\Apache Group\Apache\conf\httpd.conf), at the very bottom, add two lines
LoadModule php5_module c:/php/php5apache2_2.dll
AddType application/x-httpd-php .php .phtml
- restart Apache (using the Apache monitor utility in the tray)
Having completed all these steps, you can put the test php script(let's say it's called test.php and consists of the line

to the directory that is the root of the web server (by default it is C:\Program Files\Apache Group\Apache\htdocs\) and access it by writing the address in the browser
http://127.0.0.1/test.php

At MySQL installation select Standard configuration, on the next screen click Next, on the next screen - set a password or uncheck "Modify security settings" if you want to leave it empty.
To check, launch the Mysql console: Start - Run and copy into the line that appears
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe"
or
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe" -uroot -pPASSWORD
If the console starts, everything works. Type exit to exit and begin configuring mysql support in PHP.
To do this, if you have not done this before, take the file c:\php\php.ini-development and copy it under the name php.ini to the windows directory. Then edit it by removing the semicolon at the beginning of the line
;extension=php_mysql.dll
and editing the extension_dir parameter:
extension_dir = "c:\php\ext\"
at the same time you can fix it right away
short_open_tag = On
so that old scripts and convenient templates work
and do not forget to restart Apache after this, as described above.
Now you can use mysql in your php scripts.

Those for whom these instructions are too complicated can try installing the ready-made Denver-2 kit.
It includes everything you need at once, and much more that is unnecessary. And most importantly, everything works on its own.
Another advantage of Denver is that the volume of the basic kit is 10 times less full versions- only 4 megabytes. And also the fact that its author writes interesting books on PHP.

Also, a VERY sensible article: Installing and configuring Apache+PHP is recommended to all curious people
from the site PHP5.RU. And, of course, sections of the official documentation dedicated to installing the corresponding programs.

Forums.
When studying any case, questions are bound to arise.
It’s convenient to ask questions on forums.
http://phpclub.ru/talk/forumdisplay.php?s=&forumid=12
PHP club forum. The most visited and famous. Unfortunately, fame serves him badly. Very often, a newbie's question is answered by an even greener newbie, giving a completely wrong answer. However, there are also plenty of professionals there, ready to explain mistakes to both the first and the second.

PHP is also represented in the Russian-language segment of Livejournal
In communities Unfortunately, even though they did not shine with the quality of the material, they were long abandoned by their authors and have finally lost their relevance.
All the best on the PHP topic is listed at the top of the page.
If you know a good site, write about it in the "Feedback" section.

MySQL is a type of relational database. MySQL is a server to which various users can connect.

When you connect to the Internet, do you enter your username and password, as well as the name of the server you are connecting to? When working with MySQL, the same system is used.

One more thing: what is a relational database? Relational means based on tables. Famous electronic editor Excel tables from Microsoft is actually a relational database editor.

Connecting to MySQL server

To connect to a MySQL server in PHP, use the mysqli_connect() function. This function takes three arguments: server name, username, and password.

The mysqli_connect() function returns the connection identifier, it is stored in a variable and later used to work with databases.

MySQL server connection code:

$link = mysqli_connect("localhost", "root", "");

In this case I am working for local computer on Denwere, so the hostname is localhost, the username is root, and there is no password.

The connection also needs to be closed after finishing working with MySQL. The mysqli_close() function is used to close the connection. Let's expand the example:

$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_close($link);

Here we checked the connection identifier for truth; if there is something wrong with our connection, then the program will not be executed, the die() function will stop its execution and display an error message in the browser.

Connection errors

The following functions are used to check the connection:

• mysqli_connect_errno() - returns the error code of the last connection attempt. If there are no errors, returns zero.
• mysqli_connect_error() - returns a description of the last connection error to the MySQL server.

define("HOST", "localhost"); define("DB_USER", "root"); define("DB_PASSWORD", ""); define("DB", "tester"); $link = mysqli_connect(HOST, DB_USER, DB_PASSWORD, DB); /* check connection */ if (mysqli_connect_errno()) ( printf("Unable to connect: %s\n", mysqli_connect_error()); exit(); ) else ( printf("Successful to connect: %s\n", mysqli_get_host_info($link)); )

The mysqli_get_host_info() function returns a string containing the type of connection being used.

Also note that using the define command, I saved all connection parameters as constants. When will you write big projects, and there will be a lot of files connecting to the MySQL server, it is convenient to store the connection parameters in separate file and insert it using the include or require function.

Selecting a Database

A MySQL server can have multiple databases. First of all, we need to select the base we need to work with. In PHP, there is another parameter for this in the mysqli_connect() function - the database name.

I created it on my computer via phpMyAdmin with the name tester. Let's connect to it:

$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); mysql_close($link);

So, we have chosen a database to work with. But as we know, a relational database consists of tables, and our database does not yet have tables. The database is created empty, without tables. Tables must be added to it separately. Now let's add a table to it using PHP.

Create a table

In the name of MySQL databases, the SQL part stands for Structured Query Language, which translates as structured language requests. On SQL language we will write requests from and PHP programs send them to the MySQL server.

To create a table we just need to issue the CREATE TABLE command. Let's create a table with named users in the columns of which logins (login column) and passwords (password column) of users will be stored.

$query = "CREATE TABLE users(login VARCHAR(20), password VARCHAR(20))";

In this code, we have assigned the $query variable a string of text that represents an SQL query. We create a table called users that contains two columns login and password, both of VARCHAR(20) data type. We'll talk about data types later, for now I'll just note that VARCHAR(20) is a string with a maximum length of 20 characters.

To send our request to the MySQL server we use PHP function mysqli_query() . This function returns a positive number if the operation was successful and false if an error occurred (the request syntax is incorrect or the program does not have permission to execute the request).

$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "CREATE TABLE users(login VARCHAR(20), password VARCHAR(20))"; mysqli_query($query); mysqli_close($link);

The SQL query does not need to be written into a variable; it can be written directly as an argument to the mysql_query() function. It just makes the code more readable.

This script has one drawback - it does not output anything to the browser. Let's add a message:

$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "CREATE TABLE users(login VARCHAR(20), password VARCHAR(20))"; if (mysqli_query($query)) echo "The table has been created."; else echo "Table not created."; mysqli_close($link);

If we run this script again, we will see a message in the browser: “The table has not been created.” The fact is that the table was created during the first launch, and it is impossible to create a table with the same name again. We are faced with an error situation, so it’s time to talk about error handling when working with MySQL.

Error processing

When debugging a program, we may need precise information about the error. When an error occurs in MySQL, the database server sets the error number and a line with its description. PHP has special functions to access this data.

• mysqli_errno() - returns the error number.
• mysqli_error() - returns a string describing the error.

Now let's add the mysql_error() function to our script:

$link = mysql_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "CREATE TABLE users(login VARCHAR(20), password VARCHAR(20))"; if (mysqli_query($query)) echo "The table has been created."; else echo "Table not created: ".mysqli_error(); mysqli_close($link);

Now our script will return the line to the browser: “Table not created: Table “users” already exists.”

Deleting a table

So, now we have a table that we don’t need. It's time to learn how to drop tables from a database.

To drop a table, use the DROP TABLE command followed by the table name.

$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "DROP TABLE users"; if (!mysqli_query($query)) echo "Error while deleting table: ".mysqli_error(); else echo "Table deleted."; mysqli_close($link);

Results

So, we have mastered the basics of MySQL. What we learned to do:

• Connect to a MySQL database using the mysqli_connect() function.
• Close the connection to the MySQL server using the mysqli_close() function.
• Send SQL queries to the MySQL server using the mysqli_query() function.
• We learned the SQL query for creating a table: create table.
• We learned the SQL query for deleting a table: drop table.
• We learned how to handle errors using the mysqli_errno() and mysqli_error() functions.

Then we'll take a closer look at MySQL data types.

Read the next lesson:

MySQL DBMS is fundamental when working with databases. And everyone who wants to use a database on their website will have to master it.

And you will have to use relational databases if your site supports at least user registration. I am already silent about the other opportunities they open up. Therefore, you are unlikely to be able to do without databases, and in order to learn how to use them through MySQL, I suggest you familiarize yourself with the materials in this category.

After reading articles on MySQL basics, you will learn:

1) What's the difference between SQL And MySQL.

2) About the structure Database.

3) What types of fields are available in MySQL.

4) What privileges do users have in MySQL.

5) What opportunities do you have? PHPMyAdmin software.

6) How to manage users in PHPMyAdmin.

7) How to manage databases in PHPMyAdmin.

8) How to manage tables in PHPMyAdmin.

9) How to manage entries in PHPMyAdmin.

10) About indexes in MySQL.

11) How to connect to the database via PHP.

12) How to send a query to a database in PHP.

13) How to import a database via PHPMyAdmin.

14) How to find out the syntax SQL query through PHPMyAdmin.

15) How to convert CSV V SQL.

16) How to do case sensitive search for MySQL.

17) How to fix the error max user connections V MySQL.

18) How to optimize queries to MySQL.

19) How to Russify PHPMyAdmin V Denwer.

20) How to find out the latest ID V MySQL.

21) How to import large databases.

22) What is replication in MySQL.

23) How to create a structure correctly Database.

24) Is it worth storing images in a database?

25) How to create a trigger in MySQL.

26) How to fix the error server has gone away.

27) How normalize database.

28) Which database engine choose.

29) Why does the site need MySQL? Tables, rows, queries.