MySQL queries from php mysqli query. Sending database queries in PHP. Using the query language

Thanks to Evgeny Namokonov for his help in preparing the material.

The QUERY function allows you to select the required rows from a table using an SQL query and sort them.

Function syntax:

= QUERY (data; query; [headers])

data is the initial range that will be processed and from which we will form a sample;
a query in the Google Visualization API language (identical to SQL), specified in quotes, following certain rules, which we will discuss below;
headers - the number of lines with headers in the original range. The default is -1 (minus one), which means that the number of header lines will be determined automatically.

So, the rules for forming requests:

The request is indicated in quotation marks.
The following keywords are used in the request:
1. SELECT - determines which columns from the source table to unload and in what order. For example: "SELECT A, C, D, B". If you omit or specify an asterisk ("SELECT *") instead of the column headings, all columns will be loaded in their original order.
2. WHERE is a keyword followed by the conditions by which the selection takes place. Without it, all rows in the original range will be loaded.
3. GROUP BY - groups values by specified fields.
4. PIVOT - allows you to create something like pivot tables, grouping data by values from a specific field in the source table.
5. ORDER BY - specifies sorting. For example: "ORDER BY C DESC" - sorting by column C in descending order.
6. LIMIT - limits the number of rows returned. For example: "LIMIT 50".
7. OFFSET - skips a specified number of lines from the beginning of the range. For example: "OFFSET 100". When used with LIMIT, this keyword acts first, that is, using LIMIT 70 OFFSET 30 will return rows 31 through 100.
8. FORMAT - defines the format of certain columns according to a given pattern.
9. LABEL - allows you to rename columns in the output. For example, “LABEL MAX (D)‘ Average in 2016 ”.” Instead of max 2016, the generated SERP will have the heading “Average in 2016”.

Help from Google on the API Query Language is at: https://developers.google.com/chart/interactive/docs/querylanguage

Let's look at a few examples of how QUERY can be used in practice.

A simple example: we select books of a certain subject from the table

From a simple source table, we will form a list of books by topic:

In this case, we will select the subject from the drop-down list on a separate sheet:

The QUERY function for solving this problem will look like this:

= QUERY (Books‘! A1: C; "SELECT A, C WHERE B =‘ "& A1& "‘ ORDER BY C DESC "; 1 )

We are pulling data from columns A and C in the ‘Books’! A1: C range. Filter the data by column B (subject) of this range by the selected criterion from the drop-down list in cell A1. We sort in descending order by column C of the original range and add headers to our selection (the last argument of the QUERY function = 1).

Grouping data using GROUP BY and PIVOT

To group data using QUERY, you can use two keywords: GROUP BY and PIVOT, below we will look at examples with them.

The table we will be working with:

The task will be to display the total sales for each topic, that is, to group the data by column B.

Let's start with GROUP BY, the function text will be like this:

= QUERY (‘Books‘! A1: C6;" select B, sum (C) group by B ")

Please note: for the function to work, in addition to grouping (group by B), at least one aggregating function is needed, in our case it is sum (C). I'll write, just in case, all the aggregating functions for QUERY: sum (), max (), min (), avg () and count ().

The result of our formula:

Using GROUP BY, it is possible to group by several columns, to do this, simply list them, as in the function below, and do not forget to add these columns to the SELECT:

Grouping with PIVOT.

Please note that here in the SELECT you do not need to write column B, by which the data will be grouped.

So far, the difference is that the grouped elements are arranged in columns, and not in rows, as in GROUP BY.

Let's add another column for grouping.

You see - two grouped columns are displayed in one field, separated by commas. This is the key difference between PIVOT and GROUP BY, if there each grouping column occupies a separate column, then in PIVOT you get something like a pivot table with unique fields from several elements. By these fields, by the way, then you can quite simply search for the desired value using HLP or SEARCH.

Building a pivot table with average / maximum values by subject

In this example, we will build a small pivot table that will display the average values by subject over two years:

QUERY (‘ Books‘! A1: D; "SELECT avg (C), avg (D) pivot B"; 1 )

We use a similar range (unlike the previous one, it has sales for 2015 and 2016), extract the average values for columns C and D (SELECT avg (C), avg (D)) and group them by column B (subject).

We transpose the result for convenient display (using the TRANSPOSE function):

You can use other functions instead of avg (average), for example max (maximum values):

Or display both the average and the maximum, but only on column D:

SELECT avg (D), max (D)

Case "We calculate the average bill, choosing data from a certain date"

In the screenshot, the data array with which we will work:

Our task is to select lines with sales starting from April 1 and calculate the average check for them using the number of customers, that is, to get the weighted average.

Let's start. Let's create a QUERY multiplying the number of customers (column B) by the average check (column C) starting from a certain date:

The correct way to use the date in the QUERY formula is:

QUERY works with dates only in yyyy-mm-dd format. To convert the date from cell E1 to this form, use the TEXT formula with the condition "yyyy-mm-dd";
before the date and before the apostrophe, you need to write date;
you can not make a reference to a cell with a date, but write it directly in QUERY, then the formula will look like this:

the date is surrounded by single quotes (‘) on both sides.

Let's go back to what we got. Our formula produced the following data array:

These are the line-by-line products of the number of customers and the average check. We need to sum them up, for this we enter before the formula SUM:

To get the average bill, the resulting number must be divided by the total amount of customers in the selected rows. To reinforce the use of QUERY, let's use this formula again.

We take the previous formula, change B * C to sum (B) and get the following construction:

Finally, we combine the formulas:

Everything works, hurray! 53 (this result can be seen in the tooltip in the upper left corner) - the average bill, taking into account the number of customers, calculated using the weighted average.

Case "QUERY and drop-down list"

Take the book sales sign. On its basis, we will make a report with a drop-down list, which will contain all the topics, and the QUERY formula, which displays books of the selected topic and sorts them by sales.

So, the dropdown list. First, let's create new leaf(let's say our original table is huge, and we want to do all the analytics on another sheet). Click right click mouse on cell A1, select Data checking.

V Regulations choose List value, we list all our topics separated by commas and click Save:

The list turned out like this:

In the adjacent cell A2, write the following formula:

= QUERY (‘ Books‘! A1: C13, "SELECT A, C WHERE B =‘ "& A1& "‘ ORDER BY C DESC ")

And let's take it apart piece by piece:

‘Books’! A1: C13 - original range, table with sales, books and topics.
SELECT A, C - the table formed by the function will contain data from these columns, that is, the names of books and sales.
WHERE B = ‘" & A1 & "‘ selects only those books whose subject matter (in column B) matches the one specified in cell A1. Pay attention to the syntax: the text from the cell is indicated between the apostrophes, which refer to the text of the request. After them there are quotes (we close the request text), an ampersand (we attach the text from the cell to the request text), the cell address, another ampersand, after which the request text continues in quotes.
ORDER BY C DESC - sort the data by column B (sales) in descending order.

Result:

Changing the subject in cell A1 to philosophy, we get books on philosophy only, sorted by sales. Comfortable.

If our source table had a date, we could add it to the QUERY condition and display data for the selected day, month, week - this way we can get a ready-made sales report that does not need to be collected again every time.

Query over multiple data ranges

The first argument to the QUERY function can be an array of multiple data ranges. The main thing is that these ranges are tables with the same structure.

Ranges are separated by semicolons in curly braces:

= QUERY ((Range 1; Range 2; Range 3; Range 4);…

It is important to note: in this case, the columns within the query are denoted not by letters, as in other cases (A, B, AH, CZ, and so on), but as ColN, where N is the column number.

Channel "Google Sheets is easy" in Telegram

Thanks to Evgeny Namokonov for his help in preparing the cases for this article. Eugene and I run a Telegram channel on Google Sheets.

In this article we will learn send database queries via PHP... This article is very important and must be understood by you. However, I will reassure you - the material is very simple, so there should be no difficulties.

Before moving on to the topic of the article, I warn you in advance that I will not analyze in detail SQL language... All the necessary information is sorted out in the category dedicated, and here we are only working with MySQL via PHP.

Now let's move on to sending database queries in PHP:

}
$ mysqli-> query ("INSERT INTO mytable (name, email) VALUES (" MyName "," [email protected]")");
$ mysqli-> close ();
?>

V this example we connected to the database, checked if the connection was successful, sent a request using the method query () and then closed the connection. As you can see, everything is very simple. To send any SQL queries just one method is enough - query (), so in this case everything is insanely simple.

Now let's complicate the task a little. Let's analyze with you the requests that return result_set- result. The most popular query returning result_set is a selection of data from a table. In the following example, we will make a selection of data with you, and then display the result:

To begin with, I will explain a little what it is. result_set. Result_set is a table with the result. This table has a set of records (table rows). And to display all records, you need to iterate over each row of the table and display it. And now I will explain an example: after sending a request, we have formed result_set... Then, in a loop, we assign to the variable row the value of the next row, that is, a one-dimensional array that the method returns fetch_assoc ()... When all lines run out, the method fetch_assoc () will return false, and the loop will be exited. Inside the loop while we just output the array using the debug function print_r () although it certainly could have been inferred using foreach but this is not needed now.

Let's briefly summarize how to work with result_set:

Receive result_set by sending an appropriate query to the database.
In the loop, at each iteration, assign the next line (record) from result_set using method fetch_assoc () some variable row... Then you can work with this variable as with a one-dimensional associative array, in which the keys are the names of the fields in the table, and the values correspond to the current record.
Be sure to close result_set method close () to master resources.

As you can see, the method fetch_assoc () always returns the next record. That is, first the 1st, then the 2nd, then the 3rd, and so on. If you have some good programming experience, then you will immediately guess that this is due to the internal pointer, which you, of course, can move. Where is it used? For example, this can be used when you need to work with result_set not 1 , a 2 and more times. In order not to form the same request again, you can simply move the pointer to the beginning. And then you can brute force again result_set using the fetch_assoc () method.

To change the position of the pointer, there is a method data_seek () which takes an integer from 0 before " number of records - 1", respectively, the pointer goes to the entry corresponding to the parameter:

$ mysqli = @new mysqli ("localhost", "Admin", "pass", "mybase");
if (mysqli_connect_errno ()) (
echo "Unable to connect:" .mysqli_connect_error ();
}
$ result_set = $ mysqli-> query ("SELECT * FROM mytable");
$ result_set-> num_rows;
while ($ row = $ result_set-> fetch_assoc ()) (
print_r ($ row);
echo "
";
}
$ result_set-> data_seek (0);
while ($ row = $ result_set-> fetch_assoc ()) (
print_r ($ row);
echo "
";
}
$ result_set-> close ();
$ mysqli-> close ();
?>

In this example, we have deduced number of records in result_set using the property num_rows... And also got acquainted with the method data_seek ()... That is, we iterated over the entire result, then returned a pointer to 0th record and iterate over the result again.

In this article, we repeated connecting to the database and closing the connection. And also learned how to send database queries via PHP... Learned, how to get result_set and how to work with it. That's all you need to know to be successful. working with MySQL in PHP.

Note: Superstructure Power Query offered in Excel 2016 as a group of commands Download and convert... The information in this article applies to both Power Query and new group... For more information, see Download and Convert in Excel 2016.

In Power Query, an operation Add creates new request containing all rows from the first query and then all rows from the second query.

Safety note: Privacy levels prevent accidental aggregation of data from multiple sources, which may be private or organizational. With some queries, the user may inadvertently send data from a private data source to another source. Attackers can take advantage of this opportunity. Power Query analyzes each data source and determines the privacy level for it: public, organizational, and private. For more information on privacy levels, see Privacy Levels.

Adding in progress

You can perform two types of add operation: intermediate addition or inline append... Using intermediate addition a new query is created for each add operation. At inline append you add data to an existing query until you reach the final result. The result is a new step at the end of the current query.

Inline add

Intermediate addition

Note: Query editor only displayed when loading, editing, or creating a new request with Power Query... The video shows a window query editor that appears after you change a query in an Excel workbook. To view query editor without downloading or modifying an existing query in the workbook, in the section Getting external data on the ribbon tab Power Query choose From other sources> Empty request ... The video shows one of the display methods query editor.

".
Perhaps the guys from the Moscow office have no time, so there will be a free translation of Query Language Reference (Version 0.7).
Google Visualization API. Query Language (Version 0.7)
The Google Visualization API query language allows you to manipulate data by querying a data source.

Typically, for visualization, you need to provide data in some specific form. For example, a pie chart requires two columns: a text label and numerical value... The data source, however, may not correspond to this structure. It can contain large quantity columns, or their order will be different from that required for the diagram.
The query language allows us to structure data in the way that we need to achieve a result.
Query language syntax is similar to SQL. Those who are proficient in using SQL will be able to quickly master the query language. The syntax section identifies the differences in these languages.
Note that it is not always necessary to use a query language for rendering. A base is required to use it. To the content.

Using the query language

You can add a query string to a data source in two ways: from JavaScript code or as a parameter to a data source link. If your link does not contain query parameters, then by default the entire dataset is returned. You can change this set by adjusting the query to the data source itself.

If you are using the QUERY function in Google Sheets, then you can skip the next steps and move on to the next part.

Setting a request from JavaScript

To set a query string from JavaScript code, you need to call the method setQuery google.visualization.Query class.

var query = new google.visualization.Query (DATA_SOURCE_URL);
query.setQuery ("select dept, sum (salary) group by dept");
query.send (handleQueryResponse);

The query string to the data source can be added using the parameter tq... Thus, it is possible to use visualizations that were written by other developers, simply by adjusting their dataset.
The query string must be correctly URL encoded. You can use JavaScript function encodeURIComponent for encoding or do it manually via the tool at the end of the section.

Example

Consider an example query for Google Sheets (Remember that column identifiers are always letters. Column names are not identifiers when publishing. Column identifiers must be used in the query)
After coding, the request will look like this:

select% 20A% 2C% 20sum (B)% 20group% 20by% 20A

Suppose that the address of the Data Table is as follows:

http://spreadsheets.google.com/a/google.com/tq?key=ABCDE

Add a request parameter & tq = YOUR_QUERY_STRING to the link to the Table and get the ready query string

http://spreadsheets.google.com/a/google.com/tq?key=ABCDE&tq=select%A%2C%20sum(B)%20group%20by%20A

Language syntax

Overview

My query language is very similar to SQL. However, there are some specifics that need to be taken into account.
Data table
This document contains the term "Data Table" to refer to the results of a query. The table consists of rows and columns. Each column has the following properties:

ID (or column ID)... Used to refer to columns of a Table. Please note that you only need to refer to a column by identifier. Trick: try not to use spaces in identifiers, this can cause difficulties in editing the request. In addition, all identifiers with spaces must be enclosed in backticks.
Label... This is the string that is typically displayed to end users. For example, a legend in a pie chart or the title of a table chart. Not applicable for QUERY () function in Google Sheets.
Data types... The following data types are supported: string, number, boolean type(boolean), date (date), date and time (datetime), time (timeofday). All values in a column will be of that column's data type or null. These data types are very similar to JavaScript types, not identical. These are described in the Literals section.
Formatting templates... Data tables can support formatting patterns for all or some of the columns. These are described in the Formatting section.

Data table used in all examples
V this document the examples use a table with the following data and column types:

name string	dept string	lunchTime timeofday	hireDate date	age number	isSenior boolean	seniorityStartTime datetime
John	Eng	12:00:00	2005-03-19	35	true	2007-12-02 15:56:00
Dave	Eng	12:00:00	2006-04-19	27	false	null
Sally	Eng	13:00:00	2005-10-10	30	false	null
Ben	Sales	12:00:00	2002-10-10	32	true	2005-03-09 12:30:00
Dana	Sales	12:00:00	2004-09-08	25	false	null
Mike	Marketing	13:00:00	2005-01-10	24	true	2007-12-30 14:40:00

Language Operators

The query language consists of the following operators, consisting of one or two words. Operators must follow each other in the order shown in the table:

Operator	Usage
select	Selects which columns and in what order will be returned. If the operator is omitted, then all columns of the table will be returned.
	Returns only those table rows that match the condition. If the operator is omitted, then all rows in the table will be returned.
group by	Groups values by row.
pivot	Converts unique values in columns to new columns, similar to a pivot table.
order by	Sorts strings based on values.
limit	Sets the limit of rows that will be returned.
offset	Skips the specified number of lines from the output.
label	Sets column labels.
format	Formats the values in the specified columns according to the formatting patterns.
options	Sets additional parameters.
from	The from operator is not used.

Select

The select statement specifies which columns should be returned and in what order. If no operator is specified, or select * is specified, all table columns will be returned in their original order. Columns can be referenced through identifiers, but not through labels. Identifiers in Google Sheets are one or two letter column names (A, B, C, ..., AC, ..., DC, ...).
Operator parameters can be column identifiers, aggregate functions, scalar functions, or arithmetic operators.

Examples:

select *
select dept, salary
select max (salary)

email addressdate):

We get the result:

lunchTime	name
12:00:00	John
12:00:00	Dave
13:00:00	Sally
12:00:00	Ben
12:00:00	Dana
13:00:00	Mike

The where clause is used to return only those rows that match the conditions.
To describe the conditions are used simple operators comparisons<=, <, >, >=, =, !=, <>... Both operators! =,<>mean not equal... The strings are compared for their lexicographic meaning. Note, equality is checked through the = operator, not through == as in many languages... Comparison with null is performed through is null and is not null. When using QUERY () in Google Sheets, usually the string field has empty value"", not null.
You can combine multiple conditions using logical operators and, or, not. Parentheses are used to define explicit precedence.

The where clause also uses additional operators for more complex string comparisons. These operators take two arguments, which must be strings, any non-string argument (date, number) will be converted to a string before comparison. The comparison is case-sensitive (to smooth this feature, use the scalar functions upper (), lower ()).
contains - matches a substring. The expression will return true if part of the string completely matches the given argument - substring. For example, where name contains "John" will return "John", "John Adams", "Long John Silver" but not "john adams".

Examples:

select *
select dept, salary
select max (salary)

The following example shows the use of referencing identifiers with spaces ( email address) and identifiers that match the names of reserved words ( date):

select `email address`, name,` date`

Let's apply the following query to the table from the example:
We get the result:

lunchTime	name
12:00:00	John
12:00:00	Dave
13:00:00	Sally
12:00:00	Ben
12:00:00	Dana
13:00:00	Mike

We learned to connect MySQL server, choose a database to work with, learned the PHP function of sending queries to the MySQL server, learned two simple queries (creating and deleting a table), and learned how to close a connection.

We will now delve deeper into MySQL queries. So let's get started!

Creating a table - CREATE TABLE

Now we have an empty database, there are no tables in it. So let's create a table first. We already know how to do this from the first part.

Here is the script code that will create the plate we need:

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

There are only two fields in our table: login and password. For now, we don't need it anymore, let's not complicate the process.

So, the table has been created.

Adding rows (records) to a table - INSERT

You can add a new row to a table using the insert SQL command. Here's an example:

$ link = mysqli_connect ("localhost", "root", "", "tester"); if (! $ link) die ("Error"); $ query = "INSERT INTO users (login, password) VALUE (" zeus "," pass123 ")"; if (mysqli_query ($ link, $ query)) echo "The user has been added."; else echo "No user added:". mysqli_error (); mysqli_close ($ link);

The SQL query consists of the INSERT INTO command, the database name users, then the field names in parentheses, then the word VALUE, followed by the added values in parentheses. Values are quoted.

The syntax for the request looks like this:

INSERT INTO table_name (column1, column2) VALUE ("x1", "x2")

The quotes in the second brackets are required.

Variables can be in place of values. Here's an example:

$ link = mysqli_connect ("localhost", "root", "", "tester"); if (! $ link) die ("Error"); $ login = "zeus"; $ password = "pass123"; $ query = "INSERT INTO users (login, password) VALUE (" $ login "," $ password ")"; if (mysqli_query ($ link, $ query)) echo "The user has been added."; else echo "No user added:". mysqli_error (); mysqli_close ($ link);

Of course, this example makes little sense. It may be useful for beginners to hear that this is how logins and passwords are recorded in the database that users provide when registering. This data is stored in variables, then, after checking, is written to the database.

Exists quick way inserting multiple rows with one INSERT query:

INSERT INTO users (login, password) VALUE ("bob", "eee333"), ("Rooki", "12345"), ("magy", "olol88e8")

As you can see, the enumerated data are simply separated by commas.

So, using the INSERT command, we learned how to add records to a table. Move on.

Table View: SELECT Command

We now have a users table that has rows. The previous script can be run multiple times, and each time it will add a row to the table. Now we may not know how many rows we have in the table. And I want to know what we have written in it.

To retrieve data from a table, use the SQL SELECT command. The * sign means that we are requesting all data, then after the word FROM we write the name of the table from which we want to receive data.

Let's query all the data from the users table:

$ link = mysqli_connect ("localhost", "root", "", "tester"); if (! $ link) die ("Error"); $ query = "SELECT * FROM users"; $ result = mysqli_query ($ link, $ query); if (! $ result) echo "An error occured:". mysqli_error (); else echo "Data received"; mysqli_close ($ link);

The mysqli_query () function returned the query result identifier to us - we put it in a variable and in the future we will work with it using other PHP functions.

Number of records in the request

Let's figure out how many rows are in our query? I've run the script for adding a record to the table myself, I don't remember how many times, and now I don't know how many rows are in my table.

The mysqli_num_rows () function is used to determine the number of rows in the query result. This function is passed the identifier of the query result, and it will return the number of records.

If we need to find out the number of records in the table, then the given method is not the most suitable one. Here we found out the number of records found in the query, but the number of records in the table is searched differently.

Number of records in table SELECT COUNT (*)

To find out the number of records in a table, you can use the SELECT COUNT (*) FROM table_name command.

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); $ query = "SELECT * FROM users"; $ result = mysqli_query ($ link, $ query); if (! $ result) echo "An error occured:". mysqli_error (); else echo "Data received."; $ count = mysqli_fetch_row ($ result); echo "Total rows in the table: $ count."; mysqli_close ($ link);

Please note, here we used new function PHP mysqli_fetch_row () to fetch data. This function returns a row of the query result in the form of a simple array, in our case there is one field in the row and it has index 0.

Viewing the result of a query in a loop

After executing an SQL query with a SELECT command and obtaining the identifier of the query result, PHP creates an internal pointer in the result recordset. This pointer automatically moves to the next record after referring to the current record. This mechanism makes it very easy to loop through the result set of a SELECT query.

PHP has several functions that can be used to get an array of fields for each line of the resulting query. Let's take the mysqli_fetch_row () function as an example. This function is passed the request ID and returns an array. So in the loop the entire query result is scanned; upon reaching the end of the query result, the function will return false.

So, we query all the data from the users table (SELECT * FROM users).

"; while ($ row = mysqli_fetch_row ($ result)) (echo" Login: $ row. Password: $ row.
";) mysqli_close ($ link);

Mysqli_fetch_row () returns a simple array. In each iteration of the loop, we will receive an array with a row from the table, the fields of which we can access by specifying a numeric index.

The same can be done using the mysql_fetch_assoc () function, it returns an associative array.

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); $ result = mysqli_query ($ link, "SELECT * FROM users"); if (! $ result) echo "An error occured:". mysqli_error (); else echo "Data received.
"; while ($ row = mysqli_fetch_assoc ($ result)) (echo" Login: $ row. Password: $ row.
";) mysqli_close ($ link);

There are also functions mysqli_fetch_array () - returns any type of array, and mysqli_fetch_object () - returns an object.

SELECT DISTINCT Query - Unique Field Values

Let's create a new table:

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); // drop the existing table mysqli_query ($ link, "DROP TABLE users"); // create a new table $ query = "CREATE TABLE users (name VARCHAR (20), surname VARCHAR (20), age TINYINT UNSIGNED)"; if (mysqli_query ($ link, $ query)) echo "The table has been created.
"; else echo" Table not created: ". mysqli_error (); // function for adding records to the table function add_new_line ($ link, $ query) (if (! mysqli_query ($ link, $ query)) echo" User not added : ". mysqli_error ();) // add records add_new_line ($ link," INSERT INTO users (name, surname, age) VALUE ("Max", "Jayson", "33") "); add_new_line ($ link, "INSERT INTO users (name, surname, age) VALUE (" Bob "," Freeman "," 26 ")"); add_new_line ($ link, "INSERT INTO users (name, surname, age) VALUE (" Sara ", "Lopes", "65") "); add_new_line ($ link," INSERT INTO users (name, surname, age) VALUE ("Serg", "Pupin", "29") "); add_new_line ($ link," INSERT INTO users (name, surname, age) VALUE ("Serg", "Borman", "43") "); add_new_line ($ link," INSERT INTO users (name, surname, age) VALUE ("Max", " Lopes "," 21 ")"); // display the contents of the table in the browser $ result = mysqli_query ($ link, "SELECT * FROM users"); if (! $ Result) echo "An error occurred:". Mysqli_error (); else echo "Data received.
"; while ($ row = mysqli_fetch_assoc ($ result)) (echo" First name: $ row. Last name: $ row. Age: $ row.
";) mysqli_close ($ link);

So, we have a new, more complex table with unique records. Now let's see how many names we have in the database.

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); $ result = mysqli_query ($ link, "SELECT DISTINCT name FROM users"); echo "Total names:". mysqli_num_rows ($ result). "
"; echo" List of names:
"; while ($ name = mysqli_fetch_row ($ result)) (echo" $ name
";) mysqli_close ($ link);

The SQL query "SELECT DISTINCT name FROM users" returned a result with all unique names in our table. Each unique name on a new line of the query result.

Sorting the result - ORDER BY

By adding the ORDER BY command to the SQL query, we sort the query result in ascending order (numbers and letters alphabetically). Here's an example where you can compare a regular query and sorted by age (age field).

";) echo" Sort by age:
"; $ result = mysqli_query ($ link," SELECT * FROM users ORDER BY age "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line.
";) mysqli_close ($ link);

You can replace the age field in the ORDER BY command with the name field and see the result.

To sort the query result into reverse order use the ORDER BY age DESC command.

Matching condition - WHERE

By adding the WHERE command to the SQL query, we will query only those records that match the condition. For example, let's make a request for people under 30 years old.

To do this, use the SQL query "SELECT * FROM users WHERE age

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); echo "People under 30:
"; $ result = mysqli_query ($ link," SELECT * FROM users WHERE age<30"); while ($line = mysqli_fetch_row($result)) { echo "Имя: $line. Фамилия: $line. Возраст: $line.
";) mysqli_close ($ link);

We can also immediately sort the result in ascending order by age:
"SELECT * FROM users WHERE age<30 ORDER BY age ".

If we make the query "SELECT name FROM users WHERE age<30 ORDER BY age ", то в результате нам вернут только значения поля "name", но они также будут отсортированы по age.

We can query the values of two fields: "SELECT name, age FROM users WHERE age

Now ask for all users named "Max".

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); echo "All Max:
"; $ result = mysqli_query ($ link," SELECT * FROM users WHERE name = "Max" "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line.
";) mysqli_close ($ link);

And another example of a query - will only select names from the users table, everything except Max.

SELECT name FROM users WHERE name! = "Max"

That's all with the WHERE clause.

Limiting records - LIMIT

By adding the LIMIT command to the SQL query, we will limit the size of the result.

A query that prints the first three records: "SELECT * FROM users LIMIT 3". Let's see how it works:

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); echo "Table contents:
"; $ result = mysqli_query ($ link," SELECT * FROM users "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line.
";) echo"

The first three entries:
"; $ result = mysqli_query ($ link," SELECT * FROM users LIMIT 3 "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line.
";) echo"

Second three entries:
"; $ result = mysqli_query ($ link," SELECT * FROM users LIMIT 3, 3 "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line ...
";) mysqli_close ($ link);

Also here we used the query: "SELECT * FROM users LIMIT 3, 3". The second triple indicates the offset in the query result.

Pattern matching - LIKE

The SQL language supports simple patterns. For this, the LIKE command is used and the pattern is specified using the% character.

Here is an example query that will return all records with names starting with the letter S.

SELECT * FROM users WHERE name LIKE "S%"

Testing the request:

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); echo "Table contents:
"; $ result = mysqli_query ($ link," SELECT * FROM users "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line.
";) echo"

Names starting with S:
"; $ result = mysqli_query ($ link," SELECT * FROM users WHERE name LIKE "S%" "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age : $ line.
";) mysqli_close ($ link);

Here is an example query that will return all records with last names ending with the letter s.

SELECT * FROM users WHERE name LIKE "% s"

Matching the condition - IN

This query using the IN command will return only those rows that strictly match the condition.

For example, we are interested in people aged 21, 26 and 33.

SELECT * FROM users WHERE age IN (21,26,33)

Testing the request:

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); echo "Table contents:
"; $ result = mysqli_query ($ link," SELECT * FROM users "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line.
";) echo"

People with the required ages (21, 26, 33):
"; $ result = mysqli_query ($ link," SELECT * FROM users WHERE age IN (21, 26, 33) "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line.
";) mysqli_close ($ link);

Maximum and minimum value in a column

Selects the maximum value of age in the users table.

SELECT max (age) FROM users

The following query selects data from the users table using the name and age fields, where age is the minimum value.

SELECT name, min (age) FROM users

Updating a record - UPDATE

Let's set Max Lopes to 15 years old. This is done with a MySQL query:

UPDATE users SET age = "15" WHERE name = "Max" AND surname = "Lopes"

Notice the new AND command (and - in English means "and") in the query. If we do not specify the surname, then the age of 15 years will be set for all Max in the table.

One query can update two or more fields in one line. This is done as follows:

UPDATE users SET age = "18", surname = "Coocker" WHERE id = "3"

Our table does not have an id field, so this query will not work on it. But we will definitely learn this field containing unique line numbers.

Delete record - DELETE

MySQL database query to delete a record:

DELETE FROM users WHERE id = "10"

Again, there is no id field in our table. But we can remove from it all people under the age of 18.

DELETE FROM users WHERE age< "18"

DROP TABLE

MySQL database query that deletes the entire users table:

DROP TABLE users

Dropping a Column - ALTER TABLE ... DROP ...

Sometimes you may need to delete a column from a table, let's for example remove the age column from users:

ALTER TABLE users DROP age

This MySQL query deleted the column permanently and irrevocably.

Add Column - ALTER TABLE ... ADD ...

Sometimes you might want to add a column to an existing table, let's for example add the age column to the users table again:

ALTER TABLE users ADD age TINYINT UNSIGNED

Column rename - ALTER TABLE ... CHANGE ...

Sometimes you may need to rename a column, for example rename the age column to vozrast. We do it like this:

ALTER TABLE users CHANGE age vozrast TINYINT UNSIGNED

This MySQL query renamed the age column to vozrast with the TINYINT UNSIGNED data type.

Renaming a table - RENAME TABLE ... TO ...

Sometimes it may be necessary to rename a table:

RENAME TABLE users TO peoples

Dropping a Database - DROP DATABASE

This query can drop the database with the name tester:

DROP DATABASE tester

Creating a database - CREATE DATABASE

This query creates a database named tester:

CREATE DATABASE tester

This request works for me on Denver, but on the hosting it may not work if the database user does not have permission to perform the deletion.

Outcomes

So, in this part we got acquainted with queries to MySQL. Many of the requests we have considered are not often useful to us in the process of work, but you need to know them, since they will definitely come in handy in the process of developing scripts.

Some requests are usually made only from phpMyAdmin (creating and deleting databases for example).

In the work of sites, you usually need to add a record to a table, edit a record, or delete a record from a table.

The next step is to explore MySQL data types.

Categories

Popular

MySQL queries from php mysqli query. Sending database queries in PHP. Using the query language

A simple example: we select books of a certain subject from the table

Grouping data using GROUP BY and PIVOT

Building a pivot table with average / maximum values ​​by subject

Case "We calculate the average bill, choosing data from a certain date"

Case "QUERY and drop-down list"

Query over multiple data ranges

Channel "Google Sheets is easy" in Telegram

Adding in progress

See also:

Using the query language

Setting a request from JavaScript

Example

Language syntax

Overview

Language Operators

Select

Examples:

Examples:

Creating a table - CREATE TABLE

Adding rows (records) to a table - INSERT

Table View: SELECT Command

Number of records in the request

Number of records in table SELECT COUNT (*)

Viewing the result of a query in a loop

SELECT DISTINCT Query - Unique Field Values

Sorting the result - ORDER BY

Matching condition - WHERE

Limiting records - LIMIT

Pattern matching - LIKE

Matching the condition - IN

Maximum and minimum value in a column

Updating a record - UPDATE

Delete record - DELETE

DROP TABLE

Dropping a Column - ALTER TABLE ... DROP ...

Add Column - ALTER TABLE ... ADD ...

Column rename - ALTER TABLE ... CHANGE ...

Renaming a table - RENAME TABLE ... TO ...

Dropping a Database - DROP DATABASE

Creating a database - CREATE DATABASE

Outcomes

Building a pivot table with average / maximum values by subject