Php queries to mysql. SQL queries and their processing using PHP
Currently, everyone can observe the rapid growth in the volume of digital information. And since most of this information is important, there is a need to store it on digital media for later use. In this situation, the following may be used modern technologies like databases. They provide secure storage of any digital information, and data can be accessed anywhere in the world. One of the technologies under consideration is a database management system MySQL data.
MySQL DBMS - what is it?
MySQL is one of the most popular and frequently used information storage technologies. Her functionality superior in many respects to existing DBMSs. In particular, one of the main features is the ability to use MySQL nested queries.
Therefore, many projects where performance time is important and it is necessary to provide information storage, as well as carry out complex data retrievals, are developed on the basis of the MySQL DBMS. Most of these developments are Internet sites. At the same time, MySQL is actively being implemented in the implementation of both small (blogs, business card websites, etc.) and fairly large tasks (online stores, etc.). In both cases, a MySQL query is used to display information on a website page. In the query, developers try to make maximum use of the available capabilities provided by the database management system.
How should data storage be organized?
For convenient storage and subsequent processing, the data must be organized. The data structure allows you to determine how the tables used to store information will look. Database tables are a set of fields (columns) responsible for each specific property of a data object.
For example, if a table of employees of a certain company is compiled, then its simplest structure will look like this. Each employee is assigned a unique number, which is usually used as the primary key to the table. Then the employee’s personal data is entered into the table. It can be anything: full name, number of the department to which it is assigned, telephone number, address, etc. According to the requirements of normalization (6 normal forms of databases), as well as in order for MySQL queries to be structured, table fields must be atomic, that is, not have enumerations or lists. Therefore, as a rule, there are separate fields in the table for last name, first name, etc.
Ivanovich | Admin. | Director | |||||
Petrovich | Admin. | Deputy directors | |||||
Gregory | Grigorievich | Boss | |||||
Sergeevich | Sales consultant. |
Above is a trivial example of a database table structure. However, it does not yet fully meet the basic requirements of normalization. In real systems, an additional department table is created. Therefore, the table above should contain department numbers instead of words in the “Department” column.
How data is sampled
To obtain data from tables in a DBMS, use special team MySQL- request Select. In order for the server to respond correctly to the request, the request must be formed correctly. The request structure is formed as follows. Any call to the database server begins with a keyword select. It is from this that all queries in MySQL are built. The examples may vary in complexity, but the construction principle is very similar.
Then you need to indicate from which fields you want to select the information of interest. The fields are listed separated by commas after the sentence select. After all the necessary fields have been listed, the query specifies the table object from which the selection will occur using the clause from and specifying the table name.
To limit the selection, special operators provided by the DBMS are added to MySQL queries. To select non-repeating (unique) data, use the clause distinct, and to set conditions - the operator where. As an example applicable to the above table, consider a query requesting full name information. employees working in the Sales department. The request structure will look like in the table below.
The concept of a subquery
But main feature The DBMS, as mentioned above, has the ability to process nested MySQL queries. What should it look like? From the name it is logically clear that, formed in a certain hierarchy of two or more queries. In theory for studying the features of a DBMS, it is said that MySQL does not impose restrictions on the number of MySQL queries that can be nested in the main query. However, you can experiment in practice and make sure that after the second dozen nested requests, the response time will significantly increase. In any case, in practice there are no tasks that require using an extremely complex MySQL query. A query may require a maximum of 3-5 nested hierarchies.
Building Nested Queries
When analyzing the information read, a number of questions arise about where nested queries can be used and whether it is possible to solve the problem by breaking them down into simple ones without complicating the structure. In practice, nested queries are used to solve complex problems. This type of problem includes situations where the condition by which further sampling of values will be limited is unknown in advance. It is impossible to solve such problems if you simply use a regular MySQL query. A query consisting of hierarchies will look for constraints that may change over time or may not be known in advance.
If we consider the table above, then the following example can be given as a complex task. Let's say we need to find out basic information about the employees subordinate to Grigory Grigorievich Grishin, who is When we formulate the request, we do not know his identification number. Therefore, initially we need to know it. To do this, use a simple query that will allow you to find a solution to the main condition and complement the main MySQL query. The query clearly shows that the subquery receives an employee identification number, which further defines the restriction of the main query:
In this case, the proposal any is used to eliminate errors if there are several employees with such initials.
Results
To summarize, it should be noted that there are many other additional features that greatly facilitate the construction of queries, since the MySQL DBMS is a powerful tool with a rich arsenal of tools for storing and processing data.
Syntax:
* Where fields1— fields for selection separated by commas, you can also specify all fields with a *; table— the name of the table from which we extract data; conditions— sampling conditions; fields2— field or fields separated by commas by which to sort; count— number of lines to upload.
* The query in square brackets is not required to retrieve data.
Simple examples of using select
1. Normal data sampling:
> SELECT * FROM users
2. Data sampling with joining two tables (JOIN):
SELECT u.name, r.* FROM users u JOIN users_rights r ON r.user_id=u.id
* V in this example data is being sampled with tables being merged users And users_rights. They are united by fields user_id(in the users_rights table) and id(users). The name field is retrieved from the first table and all fields from the second.
3. Sampling with time and/or date intervals
a) the starting point and a certain time interval are known:
* data for the last hour will be selected (field date).
b) the start date and end date are known:
25.10.2017 And 25.11.2017 .
c) start and end dates + time are known:
* select data between 03/25/2018 0 hours 15 minutes And 04/25/2018 15 hours 33 minutes and 9 seconds.
d) pull out the data for certain month and year:
* extract data where in the field date there are values for April 2018 of the year.
4. Sampling of maximum, minimum and average values:
> SELECT max(area), min(area), avg(area) FROM country
* max— maximum value; min- minimal; avg- average.
5. Using String Length:
* this query should show all users whose name consists of 5 characters.
Examples of queries that are more complex or rarely used
1. Concatenation with grouping of selected data into one row (GROUP_CONCAT):
* from table users field data is retrieved id, they are all placed on one line, the values are separated commas.
2. Grouping data by two or more fields:
> SELECT * FROM users GROUP BY CONCAT(title, "::", birth)
* in summary, in this example we will upload data from the users table and group it by fields title And birth. Before grouping, we combine the fields into one line with a delimiter :: .
3. Merging results from two tables (UNION):
> (SELECT id, fio, address, "Users" as type FROM users)
UNION
(SELECT id, fio, address, "Customers" as type FROM customers)
* in this example, data is sampled from tables users And customers.
4. Sample of average values grouped for each hour:
SELECT avg(temperature), DATE_FORMAT(datetimeupdate, "%Y-%m-%d %H") as hour_datetime FROM archive GROUP BY DATE_FORMAT(datetimeupdate, "%Y-%m-%d %H")
*here we extract the average value of the field temperature from the table archive and group by field datetimeupdate(with time division for every hour).
INSERT
Syntax 1:
> INSERT INTO
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | // hostname (to be confirmed with the provider)$database = "db_name" ; // name of the database you should create$user = "user_name" ; // username specified by you, or defined by the provider$passwod = "your_pass" ; // the password you specified$db = mysql_connect ($host , $user , $passwod ) or die ( "Failed to connect to MySQL.") ; mysql_select_db ($database) or die ( "Failed to connect to the database.") ; $query = "SELECT * FROM `table_name`" ; $resource = mysql_query($query); while ($row = mysql_fetch_array ($resource) ) ( echo "Serial number: "
. $row["id"]. " \n"; echo "Name: " . $row["name"] . " \n"; echo "Last name: " . $row [ "surname" ] . " \n"; } ?> |
\n"; ) ?>
And just like before, let’s analyze this code. Let's start with the first queries to our table.
$resource = mysql_query($query);
If the requests are completed successfully, the function "mysql_query()" will return us the identifier "$resource". In our example, we pass it to another function "mysql_fetch_array()", which forms an array and displays its data based on a selection from a database table. Using our table as an example, the array will consist of a number of elements equal to the number of rows (records) in the table. Which in turn will contain the following values: id, name, surname, in each row of the table. We now use this code:
while($row = mysql_fetch_array($resource)) ( echo "Ordinal number: ".$row["id"]."
\n"; echo "Name: ".$row["name"]."
\n"; echo "Last name: ".$row["surname"]."
\n"; )
That is, while the variable we have given $row does not receive function results "mysql_fetch_row" equal to zero, you should return the field values to the browser: $row["id"], $row["name"], $row["surname"] using a variable "echo". This completes the description and analysis of this code.
Let's look at another example request:
| 1 | $query = "SELECT name FROM `table_name`"; |
$query = "SELECT name FROM `table_name`";
Here we no longer select everything from the table, but select only the field values "name". We also need to rewrite the rest of the output code a bit. It will look like this:
| 1 2 3 4 5 | $resource = mysql_query($query); while ($row = mysql_fetch_array ($resource) ) ( echo "Name:" . $row [ "name" ] . " \n"; } |
$resource = mysql_query($query); while($row = mysql_fetch_array($resource)) ( echo "Name:".$row["name"]."
\n"; )
Simple enough isn't it? Well, let's move on. If you want to get say all line numbers, where the value of the surname field will be equal to Ivanov, then the request will already have the following form:
For example, you need to extract the last name from the line under the number 10 , then we will rewrite the request as follows:
After executing such a query, the result will be only one row, and accordingly there is no point in creating a loop while and put everything into an array. We will use simpler code:
$resource = mysql_query($query); $row = mysql_fetch_row($resource); echo "The last name of the tenth person on the list is ".$row."\n";
If you look closely, you will see a new feature" mysql_fetch_row()", and previously we used "mysql_fetch_array()". This function allows you to get the values of fields (or one field) of a specific row. Since we only received the last name, and this is one element, we can access it using $row like this $row;. This is where we’ll end our consideration of such requests for now and move quickly on.
To add to table "table_name" field "middlename"(middle name) after surname:
| 1 | $query = "ALTER TABLE `table_name` ADD `middlename` VARCHAR(50) NOT NULL AFTER `surname`"; |
$query = "ALTER TABLE `table_name` ADD `middlename` VARCHAR(50) NOT NULL AFTER `surname`";
In addition to equal signs, when using MySQL queries, you can use such a concept as "similar to". Let's run the following query and get all the records from the table where the surname contains "ov":
Use of the sign "%" at the beginning or end means that "ov" can be in any part of the word. Let's now consider an example where we select data by letter "IN" at the beginning of the name Pay attention to the location of the sign "%" .
Next Receive Request maximum value fields id:
$query = "ALTER TABLE `table_name` DROP `surname`";
And now let’s completely delete our table "table_name":
| 1 | $query = "DROP TABLE `table_name`" ; |
$query = "DROP TABLE `table_name`";
We'll probably end here. To summarize the article, we looked at building basic queries, outputting data from the MySQL database, and connecting to the database. I would also like to clarify that for receiving requests (SELECT), we can use the function - mysql_query(). That's actually all I wanted to tell you. If you have any questions or if there are additions to the article, write in the comments.
Database operations very often become a bottleneck when implementing a web project. Programmers need to properly structure tables, write optimal queries and more productive code. This article provides a small list of techniques for optimizing work with MySQL for programmers.
Optimization issues in such cases concern not only the database administrator.
1. Optimize your queries for the query cache.
Most MySQL servers use query caching. This is one of the effective performance improvement techniques that is performed by the database engine in background. If a query is executed many times, the cache begins to be used to obtain the result and the operation is completed much faster.
The problem is that it is so simple and at the same time hidden from the developer, and most programmers ignore such a great opportunity to improve the performance of the project. Some actions may actually prevent the query cache from being used during execution.
// Query cache NOT WORKING $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); // The query cache is WORKING! $today = date("Y-m-d"); $r = mysql_query("SELECT username FROM user WHERE signup_date >= "$today"");
The reason the query cache doesn't work in the first case is because of the use of the function CURDATE(). This approach is used for all non-deterministic functions, for example, NOW(), RAND(), etc. Since the function's return result may change, MySQL decides not to cache the query. All that is needed to fix the situation is to add an extra line of PHP code before the request.
2. Use EXPLAIN for your SELECT queries
Using a keyword can help paint a picture of what MySQL is doing to fulfill your query. This picture makes it easy to identify bottlenecks and other problems in queries or table structure.
The result of an EXPLAIN query shows which indexes are used, how the table is scanned and sorted, and so on.
Let's take a SELECT query (preferably a complex one, with a JOIN), and add the EXPLAIN keyword before it. You can use PhpMyAdmin for this. Such a query will output the result in a nice table. Let's say we forgot to add an index on a column that is used for a JOIN:
After adding an index for the group_id field:
Now instead of scanning 7883 rows, only 9 and 16 rows from two tables will be scanned. Good method The performance estimate is to multiply all the numbers in the “rows” column. The result is approximately proportional to the amount of data being processed.
3. Use LIMIT 1 if you need to get a unique string
Sometimes, while using a query, you already know that you are looking for only one row. You can get a unique record or simply check for the existence of any number of records that satisfy the WHERE clause.
In such a case, adding LIMIT 1 to your query may improve performance. Under this condition, the database engine stops scanning records as soon as it finds one and does not scan the entire table or index.
// Is there any user from Alabama? // Don't do this: $r = mysql_query("SELECT * FROM user WHERE state = "Alabama""); if (mysql_num_rows($r) > 0) ( // ... ) // This will be much better: $r = mysql_query("SELECT 1 FROM user WHERE state = "Alabama" LIMIT 1"); if (mysql_num_rows($r) > 0) ( // ... )
4. Index your search fields
Index more than just primary and unique keys. If any columns in your table are used for search queries, then they need to be indexed.
As you can see, this rule also applies to searching by part of a string, for example, “last_name LIKE ‘a%’”. When the start of a string is used for a search, MySQL can use the index of the column being searched.
You should also understand what types of searches you can't use regular indexing for. For example, when searching for the word (“WHERE post_content LIKE ‘%apple%’”), the benefits of indexing will not be available. In such cases, it is better to use or build your own solutions based on indexing.
5. Indexing and using the same types for linked columns
If your application contains many JOIN queries, you need to index the columns that are linked in both tables. This has an effect on internal optimization bind operations in MySQL.
Also the columns being linked must have same type. For example, if you associate a DECIMAL column with an INT column from another table, MySQL will not be able to use the index on at least for one of one table. Even the character encoding must be the same for identical string type columns.
// Search for a company from a specific state $r = mysql_query("SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id"); // both columns for the state name must be indexed // and both must be the same type and character encoding // or MySQL will perform a full table scan
6. Don't use ORDER BY RAND()
This is one of those tricks that looks cool, and many new programmers fall into its trap. They cannot even imagine what a terrible problem they are creating for themselves by starting to use this expression in their queries.
If you really need to randomize the rows in the result of your query, then there are many the best ways solve such a problem. Of course, this will be implemented with additional code, but you will be saved from a problem that grows exponentially with the volume of data. The thing is, MySQL performs a RAND() operation (which takes up CPU time) on every single row in the table before sorting it and giving you just one row.
// DO NOT do this: $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1"); // This will work better: $r = mysql_query("SELECT count(*) FROM user"); $d = mysql_fetch_row($r); $rand = mt_rand(0,$d - 1); $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
This is how you get random number, which is less than the number of rows in the query result, and use it as an offset in the LIMIT clause.
7. Avoid using SELECT *
The more data is read from the table, the slower the query will be. Such operations also take time to complete disk operations. And if the database server is separate from the web server, then delays will also be caused by data transfer over the network between the servers.
It's a good habit to specify a column when doing a SELECT.
// Bad: $r = mysql_query("SELECT * FROM user WHERE user_id = 1"); $d = mysql_fetch_assoc($r); echo "Welcome ($d["username"])"; // This is better: $r = mysql_query("SELECT username FROM user WHERE user_id = 1"); $d = mysql_fetch_assoc($r); echo "Welcome ($d["username"])"; // The difference becomes significant with large amounts of data
8. Try to use the id field everywhere
A good practice is to use an id field in each table that has the PRIMARY KEY, AUTO_INCREMENT properties set, and is of type from the INT family. Preferably - UNSIGNED, since in this case the value cannot be negative.
Even if your table has a field with a unique username, don't make it the primary key. VARCHAR fields are slow to work as primary keys. Also, the structure of your database will be better if it internally uses links to records based on id.
In addition, the MySQL engine uses primary keys for its internal tasks, and the use of the id field creates optimal conditions for solving them.
One possible exception to of this rule are “associative tables,” which are used for many-to-many relationships between two other tables. For example, the “posts_tags” table contains 2 columns: post_id, tag_id. They are used to describe the relationship between two tables “post” and “tags”. The table described may have a primary key that contains both id fields.
9. Use ENUM instead of VARCHAR
// Create a prepared expression if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) ( // Bind the parameters $stmt->bind_param("s", $state); // Execute $stmt->execute(); // Bind the result variables $stmt->bind_result($username); // Get the values $stmt->fetch(); printf("%s is from %s\n", $username , $state); $stmt->close(); )13. Unbuffered requests
Typically, when you execute a request from a script, the script is interrupted until the request is completed. This behavior can be changed using unbuffered queries.
Excellent explanation of the function from the PHP documentation:
“mysql_unbuffered_query() sends an SQL query to the MySQL server without automatic receipt and buffering the result rows as the mysql_query() function does. This way, a certain amount of memory is saved by SQL queries that produce a large result set, and you can start working on the result set immediately after receiving the first row, without waiting for the SQL query to be completely executed.”
However, there are several limitations. You must either read all lines or call before executing the next query. You also cannot use or to type a result.
14. Store the IP address as UNSIGNED INT
Many programmers create a VARCHAR(15) field to store an IP address, without even thinking about the fact that they will store an integer value in this field. If you use INT, the field size will be reduced to 4 bytes, and it will have a fixed length.
You must use the UNSIGNED INT type because the IP address uses all 32 bits of an unsigned integer.
$r = "UPDATE users SET ip = INET_ATON("($_SERVER["REMOTE_ADDR"])") WHERE user_id = $user_id";
15. Tables with a fixed record length (Static) are faster
When each individual column in a table has a fixed length, then the entire table as a whole is treated as . Examples of column types that do not have a fixed length: VARCHAR, TEXT, BLOB. If you include at least one column of this type, the table will no longer be considered "static" and will be processed differently by the MySQL engine.
"static" tables are processed faster by the MySQL engine when searching for records. When you need to read a specific record in a table, its position is quickly calculated. If the row size is not fixed, then determining the position of the record requires time to search and compare with the index of the main key.
Such tables are also easier to cache and easier to recover from failures. But they can take up more space. For example, if you convert a VARCHAR(20) field to a CHAR(20) field, then 20 bytes will always be occupied, regardless of whether they are in use or not.
Using the Vertical Splitting technique makes it possible to separate variable-length columns into a separate table.
16. Vertical separation
Vertical partitioning is the act of dividing a table structure vertically for the purpose of optimization.
Example 1: You have a table that contains home addresses that are rarely used in the application. You can split your table and store the addresses in a separate table. This will reduce the size of the main user table. And as you know, a smaller table is processed faster.
Example 2: You have a field “last_login” in your table. It is updated every time a user registers on the site. But every table update causes the query to be cached, which can create system overload. You can separate this field into another table to make updates to the user table less frequent.
But you need to be sure that you don't need to permanently link the two tables you just split, as this could lead to poor performance.
17. Separate large DELETE or INSERT queries
If you need to perform big request DELETE or INSERT on a live site, you need to be careful not to disrupt traffic. When a large query is running, it can lock your tables and cause your application to stop.
Apache runs many parallel processes/threads. for this reason, it works more efficiently when the script finishes execution as quickly as possible, thus the server does not use too many open connections and processes consuming resources, especially memory.
If you lock tables for an extended period of time (for example, 30 seconds or more) on a heavily loaded web server, you can cause a backlog of processes and requests that will require significant cleanup time or even cause your web server to stop.
If you have a script that removes a large number of records, just use the LIMIT clause to split it into small batches to avoid the situation described.
While (1) ( mysql_query("DELETE FROM logs WHERE log_date<= "2009-10-01" LIMIT 10000"); if (mysql_affected_rows() == 0) { // выполняем удаление break; } // вы можете сделать небольшую паузу usleep(50000); }
18. Small columns are processed faster
For a database engine, disk is the most important bottleneck. The desire to make everything smaller and smaller usually has a positive impact on productivity by reducing the amount of data being moved.
The MySQL documentation contains a list for all types.
If the table will only contain a few rows, then there is no reason to make the primary key type INT rather than MEDIUMINT, SMALLINT, or even TINYINT. if you only need the date, use DATE instead of DATETIME.
You just need to remember about the opportunities for growth.
19. Choose the right storage mechanism
There are two main data storage engines for MySQL: MyISAM and InnoDB. Each has its own advantages and disadvantages.
MyISAM is great for read-heavy applications, but it doesn't scale well when there are a lot of records. Even if you update one field in one row, the entire table will be locked and no process will be able to read anything until the query completes. MyISAM performs calculations quickly for queries like SELECT COUNT(*).
InnoDB is a more complex storage engine, and it can be slower than MyISAM for most small applications. But it supports row locking, which is better for scaling tables. It also supports some additional features such as transactions.
20. Use object-relational mapping
Using an object-relational mapping (ORM - Object Relational Mapper) provides a number of advantages. Everything that can be done in an ORM can be done manually, but with more effort and higher developer level requirements.
ORM is great for lazy loading. This means that values can be retrieved when they are needed. But you need to be careful because you can create a lot of small queries that will reduce performance.
An ORM can also combine your queries into transactions, which are significantly faster than individual database queries.
For PHP you can use ORM.
21. Be careful with persistent connections
Persistent connections are designed to reduce the cost of restoring connections to MySQL. When a persistent connection is created, it remains open even after the script completes. Since Apache reuses child processes, the process is executed for the new script, and it also uses the MySQL connection.
This sounds great in theory. But in reality, this feature is not worth a penny due to the problems. It can cause serious problems with connection limits, memory overflow, and so on.
Apache works on concurrency principles and creates many child processes. This is the reason why persistent connections do not work as expected on this system. Before using the mysql_pconnect() function, consult your system administrator.