Last Updated:

SQL - queries and their processing using PHP

 

In this article we will look at ways to access database tables given by MySQL using the SQL query language. SQL is an abbreviation that is "revealed" - to structure the query language.


In PHP for this purpose, there are a number of functions with the prefix "mysql". We will not need many of them to consider requests. A function without which the execution of SQL queries in PHP would be simply impossible:

resource mysql_query

This function sends a query to the database and returns the resource ID if the request is successful.
In order to connect to a MySQL database, you must perform the following sequence:

$ Host = 'localhost'; Host name (specified by provider) $ database = 'db_name'; The name of the database you have to create is $user='user_name'; The username you specified, or a specific provider $ pswd = 'your_pass'; The password you set is $ dbh = mysql_connect ($ host, $ user, $ pswd) or die ("I can't connect to MySQL."); Mysql_select_db ($ database) or die ("I can't connect to the base.");

 

So mysql_connect ()- A feature to connect to the MySQL server on your hosting.
And mysql_select_db selects the database on the server to connect to.


In other words, we connect to the server, select the base and start working.
The die () function is called in case of an error and displays the message you specified in the browser window.


To finish working with databases, use the function:

mysql_close ($ dbh);

here $ dbh - Descriptor, which when connected returned by the function mysql_connect.
Having finished the initial review, we will begin to consider the actual SQL queries.
To do this, first of all you need to create a database with a specific name. And in it to create a table, also with a specific name. In our examples, we will refer to the table my_sql_table. To create this table let's run the following query in phpmyadmin of our localhost:

CREATE TABLE 'my_sql_table' ('id' INT NOT NULL, // IDENTIFIER OF FUTURE RECORDS OF TABLE'firstname 'VARCHAR (50) NOT NULL, // VARCHAR'surname text field' VARCHAR (50) NOT NULL, // max length 50 SIMVOLSPRIMARY KEY ('id') // primary key - id ID);

 

So the table is created. We will execute the first request, which we will immediately draw up in the form of PHP code:

\ n "; echo" Name: ". $ row [ 'firstname']." \ n "; echo" Last Name: ". $ row [ 'surname']."


\ n ";}? >

Let's analyze the PHP code of the firstsql.php file. Let's start with the actual query to the database tables (DB).

$ Query = "SELECT * FROM 'my_sql_table'";

This query can be decrypted as follows: select all records from all fields from the table my_sql_table database. Thus, the sign after the word SELECT means "choose absolutely everything." So, the request is formed. Now it must be done:

$ Res = mysql_query ($ query);

If the request is successfully executed, the mysql_query () function will return the resource ID $ res to us.
We must pass it as a parameter to the function mysql_fetch_array (). The name of this function speaks for itself. That is, it forms and issues an array according to a sample from the database table. In the case of our table, the array will consist of a number of elements equal to the number of records (rows) in the table and contain the values of id, firstname, surname for each row of the table. So, the following code:

while ($ row = mysql_fetch_array ($ res)) {echo "Number:". $ row [ 'id']. " \ n"; echo "Name:". $ row [ 'firstname']. "< br> \ n "; echo" Last Name: ". $ row [ 'surname']."


\ n ";}

you can comment like this: while the variable $ row entered by us receives not zero results of the function mysql_fetch_row the values of the fields $ Row [ 'id'], $ row [ 'firstname'], $ row [ 'surname'] should be given to the browser using echo.
If you run the query like this:

$ Query = "SELECT firstname FROM 'my_sql_table'";

then this will mean that only the value of the firstname field is selected from all the rows.
So the previous code should be rewritten as:

$ Res = mysql_query ($ query); while ($ row = mysql_fetch_array ($ res)) {echo "Name:". $ Row [ 'firstname']. " \ N";}

If you want to select the rows of the table with a specific value id where the surname (surname) will be Petrov, then the request will be rewritten as follows:

$ Query = "SELECT id FROM 'my_sql_table' where surname = 'Petrov'";

But if you need to find out the name of the one who is under the number, for example, 5, then the request will be as follows:

$ Query = "SELECT surname FROM 'my_sql_table' where id = 5";

In this case, you know that the result of the query will be only one row from the table. That is, it makes no sense to organize a cycle using while. And the processing of the request will be as follows

$ Res = mysql_query ($ query); $ row = mysql_fetch_row ($ res); echo "The last name of the fifth person on the list:". $ Row [0]. "\ N";

Here, instead of mysql_fetch_array () we applied the mysql_fetch_row () . That is, get the value of the field (or fields) of a particular row. Since the field is in ?? there was only one of us - surname - we can refer to a single element of the array $ row as $ Row [0];.

Further, we will not dwell in such detail on the processing of requests, but will consider the most typical examples of the actual requests.

So, consider the most typical examples of MySQL queries. Consideration will be carried out on the basis of the tablemy_sql_table:


1. Add to the table the my_sql_table field middle_name (patronymic) after the surname:

$ Query = "ALTER TABLE 'my_sql_table' ADD 'middle_name'
 VARCHAR (50) NOT NULL AFTER 'surname' ";

2. Now remove the surname field from the table my_sql_table:

$ Query = "ALTER TABLE 'my_sql_table' DROP 'surname'";

3. Delete the entries from the table my_sql_table with the surname Sidorov:

$ Query = "DELETE FROM 'my_sql_table' where surname = 'Sidorov'";

4. In addition to equal signs, also "more" or "less", in the MySQL language of queries there is the concept of "similar to". Let's choose the entries from the table my_sql_table, where the surname contains "dor":

$ Query = "SELECT * FROM 'my_sql_table' where surname like '% dor%'";

Here the presence of "%"At the beginning and end of the "dor" means that the request will look for exactly "dor", and it does not matter at the beginning, end, or middle of the surname it is located. Let's give an example
5. Select the entries from the table my_sql_table with the last name starting with P. Pay attention to the location "%":

$ Query = "SELECT * FROM 'my_sql_table' where surname like 'P%'";

6. Calculate the maximum value of id:

$ Query = "SELECT MAX (id) FROM 'my_sql_table'";

7. Calculate the number of fields in the my_sql_table with the last name starting with P.

$ Query = "SELECT COUNT (*) FROM 'my_sql_table' where surname like 'P%'";

8. Deleting the my_sql_table table:

$ Query = "DROP TABLE 'my_sql_table'";

For queries 1-3 in PHP, simply execute the query:

mysql_query ($ query);

We reviewed the most characteristic examples of requests. I believe that with their help, following elementary logic, you will be able to perform more complex queries to the MySQL database tables you have created.