Let's learn PHP and MySQL correctly. Dedicated to real “Dummies” or MySQL for beginners Building an interface for adding information
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'm 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.
MySQL 8. Designing and creating databases
MySQL is one of the popular relational database servers. Used on a huge number of websites and content management systems (CMS). Wordpress, Joomla, Drupal, Bitrix and other CMS can and do work with the MySQL DBMS. The most popular PHP frameworks can work with MySQL: Laravel, Symfony, yii and others. To use MySQL effectively, you need to know its tools, capabilities and features.
On course "MySQL 8. Design and creation of databases for the web" you will master the basic techniques and methods of working effectively with the MySQL 8 server. During the training, you will consider the stages of designing and creating databases, study SQL operators in detail, and learn how to manage transactions on the MySQL server. You will be able to create stored procedures and triggers, and learn how to effectively use the storage engines (“engines”) of the MySQL server. This course is a must have for any website developer.
The course is intended for web developers and webmasters who plan to use the MySQL server in their practice. The course will be especially interesting for specialists using PHP and Perl.
By the end of the course, you will have developed a large volume of SQL query templates, which you can later use in your projects, and create a database for the selected project. Each module contains a large number of practical tasks and ends with a test to consolidate knowledge. All course participants are given homework assignments.
The course program meets the requirements of the professional standard “Programmer”. Upon successful completion of the course, the center is issued a prestigious certificate of advanced training.
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 is the appearance of the 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) - a collection of HTML pages and other 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. At this stage, this is the PHP programming language and the MySQL database.
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!
Most modern web applications interact with databases, usually using a language called SQL. Luckily for us, this language is very easy to learn. In this article we will look at simple SQL queries and learn how to use them to interact with MySQL database.
What will you need?
SQL (Structured Query Language) a language specifically designed to interface with database management systems such as MySQL, Oracle, Sqlite and others... To complete SQL requests in this article, I advise you to install MySQL to your local computer. I also recommend using phpMyAdmin as a visual interface.
All this is available in everyone's favorite Denver. I think everyone should know what it is and where to get it :). Can also use WAMP or MAMP.
Denver has a built-in MySQL console. This is what we will use.
CREATE DATABASE:database creation
Here is our first request. We will create our first database for further work.
To begin, open MySQL console and log in. For WAMP The default password is empty. That is, nothing :). For MAMP - "root". For Denver, we need to clarify.
After login, enter the following line and click Enter:
CREATE DATABASE my_first_db;Note that a semicolon (;) is added at the end of the query, just like in other languages.
Also commands in SQL case sensitive. We write them in capital letters.
Options formally: Character SetAnd Collation
If you want to install character set (character set) and collation (comparison) can be write the following command:
CREATE DATABASE my_first_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;Finds a list of character sets that are supported in MySQL.
SHOW DATABASES:displays a list of all databases
This command is used to list all available databases.
DROP DATABASE:deleting a database
You can delete an existing DB using this query.
Be careful with this command as it runs without warning. If there is data in your database, it will all be deleted.
USE:Database selection
Technically this is not a query, but a statement and does not require a semicolon at the end.
It tells MySQL select the default database for the current session. Now we are ready to create tables and do other things with the database.
What is a table in a database?
You can represent the table in the database as Excel file.
Just like in the picture, tables have column names, rows and information. By using SQL queries we can create such tables. We may also add, read, update and delete information.
CREATE TABLE: Creating a table
C Using this query we can create tables in the database. Unfortunately, the documentation MySQL not very clear for beginners on this issue. The structure of this type of query can be very complex, but we'll start off easy.
The following query will create a table with 2 columns.
CREATE TABLE users (username VARCHAR(20), create_date DATE);Please note that we can write our queries on multiple lines and with tabs for indentation.
The first line is simple. We simply create a table called "users". Next, in parentheses, separated by commas, is a list of all columns. After each column name we have information types, such as VARCHAR or DATE.
VARCHAR(20) means that the column is of type string and can be a maximum of 20 characters in length. DATE is also an information type that is used to store dates in the following format: "YYYY - MM-DD".
PRIMARY KEY ( primary keyh)
Before we run the next query, we must also include a column for "user_id", which will be our primary key. You can think of PRIMARY KEY as information that is used to identify each row in a table.
CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20), create_date DATE);
INT makes a 32-bit integer type (for example, numbers). AUTO_INCREMENT automatically generates a new value ID every time we add new series of information. This is not necessary, but it makes the whole process easier.
This column does not have to be an integer value, but it is most often used. Having a Primary Key is also optional, but is recommended for database architecture and performance.
Let's run the query:
SHOW TABLES:show all tables
This query allows you to get a list of tables that are in the database.
EXPLAIN:Show table structure
To show the structure of an existing table, you can use this query.
Columns are displayed with all properties.
DROP TABLE:delete table
Same as DROP DATABASES, this query deletes the table and its contents without warning.
ALTER TABLE: change table
This query can also contain complex structure due to the greater number of changes it can make to the table. Let's look at examples.
(if you deleted the table in the previous step, create it again for tests)
ADDING A COLUMN
ALTER TABLE users ADD email VARCHAR(100) AFTER username;Due to the good readability of SQL, I think there is no point in explaining it in detail. We are adding a new column "email" after "username".
REMOVING A COLUMN
It was also very easy. Use this request with caution because your data may be deleted without warning.
Restore the column you just deleted for further experiments.
MAKING CHANGES IN A COLUMN
Sometimes you may want to make changes to the properties of a column, and you don't have to completely delete it to do this.
This query renamed the user column to "user_name" and changed its type from VARCHAR(20) to VARCHAR(30). This change should not change the data in the table.
INSERT: Adding Information to a Table
Let's add some information to the table using the following query.
As you can see, VALUES() contains a list of values separated by commas. All values are enclosed in single columns. And the values must be in the order of the columns that were defined when the table was created.
Notice that the first value is NULL for the PRIMARY KEY field called "user_id". We do this so that the ID is generated automatically, since the column has the AUTO_INCREMENT property. When information is added for the first time the ID will be 1. The next row will be 2, and so on...
ALTERNATIVE OPTION
There is another query option for adding rows.
This time we are using the SET keyword instead of VALUES and it does not have parentheses. There are several nuances:
You can skip the column. For example, we didn't assign a value to "user_id", which would default to its AUTO_INCREMENT value. If you omit a column with a VARCHAR type, then an empty row will be added.
Each column must be referred to by name. Because of this, they can be mentioned in any order, unlike the previous version.
ALTERNATIVE OPTION 2
Here's another option.
Again, since there are references to the column name, you can set the values in any order.
LAST_INSERT_ID()
You can use this query to get the ID that was AUTO_INCREMENT for the last row of the current session.
NOW()
Now it's time to show how you can use the MySQL function in queries.
The NOW() function displays the current date. So you can use it to automatically set the date of a column to the current one when you insert a new row.
Please note that we received 1 warning, but please ignore it. The reason for this is that NOW() also serves to output temporary information.
SELECT: Reading data from a table
If we add information to a table, then it would be logical to learn how to read it from there. This is where the SELECT query will help us.
Below is the simplest possible SELECT query to read a table.
In this case, the asterisk (*) means that we have requested all fields from the table. If you only want certain columns, the query would look like this.
ConditionWHERE
Most often, we are not interested in all columns, but only in some. For example, let's assume that we only need an email address for the user "nettuts".
WHERE allows you to set conditions in a query and make detailed selections.
Note that for equality, one equal sign (=) is used, not two, as in programming.
You can also use comparisons.
AND or OR can be used to combine conditions:
Note that numeric values must not be in quotes.
IN()
This is useful for sampling on multiple values
LIKE
Allows you to make "wildcard" requests
The % icon is used as a "wildcard".
ConditionThat is, anything could be in its place.
ORDER BY
If you want to get the result in an ordered form according to any criterion
The default order is ASC (smallest to largest). For the opposite, DESC is used.
LIMIT ... OFFSET ...
You can limit the number of results returned.
LIMIT 2 takes only the first 2 results. LIMIT 1 OFFSET 2 gets 1 result after the first 2. LIMIT 2, 1 means the same thing (just note offset comes first and then limit ). UPDATE:
Make changes to the information in the table
This query is used to change information in a table.
In most cases, it is used in conjunction with a WHERE clause, since you will most likely want to make changes to certain columns. If there is no WHERE clause, the changes will affect all rows.
You can also use LIMIT to limit the number of rows that need to be changed. DELETE:
Removing information from a table
Just like UPDATE, this query is used with WHERE:
To delete the contents of a table, you can simply do this:
DELETE FROM users; But it's better to use
TRUNCATE AUTO_INCREMENT In addition to deleting, this request also resets values and when adding rows again, the countdown will start from zero. DELETE
does not do this and the countdown continues.
Disabling Lowercase Values and Special Words
String values Some characters need to be disabled ( escape
), or there may be problems.(\).
A backslash is used for this.
Special words Because in MySQL there are many special words ( SELECT or UPDATE(`).
), to avoid errors when using them, you must use quotes. But not ordinary quotes, but like this That is, you will need to add a column named " delete
", you need to do it like this:
Conclusion