Communication with MySQL databases ~ Tech Blog

Monday 27 August 2018

Communication with MySQL databases

MySQL DBMS is one of the many databases supported in PHP. The MySQL system is free and has enough power to solve real problems.

A Brief Introduction to MySQL

SQL is an abbreviation for the words Structured Query Language , which means a structured query language. This language is the standard tool for accessing various databases.

The MySQL system is a server to which users of remote computers can connect.

To work with databases, it is convenient to use the tool included in the Web developer package: Denwer phpMyAdmin . Here you can create a new database, create a new table in the selected database, populate the table with data, and add, delete and edit data.

MySQL defines three basic data types: numeric, date and time, and lowercase. Each of these categories is divided into many types. The main ones are:

A type	Description
INT	Integer
TINYINT	A small integer (-127 to 128 or from 0 to 255)
FLOAT	Floating floating-point number
On DATE	Date. Displayed as YYYY-MM-DD
TIME	Time. It is displayed as HH: MM: SS
DATETIME	Date and time. Displayed as YYYY-MM-DPDM: MM: SS
YEAR [(2 \| 4)]	Year. You can define a two- or four-digit format
CHAR (M)	A string of fixed length M (M <= 255)
VARCHAR (M)	A string of arbitrary length up to M (M <= 255)
TEXT	Long text fragments (<= 65535)
Blob	Large binary objects (images, sounds)

Each column after its data type contains other specifiers:

A type	Description
NOT NULL	All rows of the table must have a value in this attribute. If not specified, the field can be empty ( `NULL` )
AUTO_INCREMENT	A special MySQL feature that can be used in numeric columns. If you leave this field blank when you insert rows into a table, MySQL automatically generates a unique identifier value.This value will be one greater than the maximum value that already exists in the column. In each table there can be no more than one such field. Columns with `AUTO_INCREMENT` must be indexed
PRIMARY KEY	The column is the primary key for the table. The data in this column must be unique. MySQL automatically indexes this column
UNSIGNED	After an integer type, it means that its value can be either positive or zero
COMMENT	Table column name

Creating a new MySQL database is done using the SQL command CREATE DATABASE .

CREATE DATABASE IF NOT EXISTS `base`
 DEFAULT CHARACTER SET cp1251 COLLATE cp1251_bin

Creating a new table is done using the SQL command CREATE TABLE . For example, the books table for a bookstore will contain five fields: ISBN, author, title, price and number of copies:

CREATE TABLE books (ISBN CHAR (13) NOT NULL,
                    PRIMARY KEY (ISBN),
                    author VARCHAR (30),
                    title VARCHAR (60),
                    price FLOAT (4,2),
                    quantity TINYINT UNSIGNED);

To avoid an error message, if the table already exists, you must change the first line by adding the phrase "IF NOT EXISTS":

CREATE TABLE IF NOT EXISTS books ...

To create an auto-renewed field with the current date of TIMESTAMP or DATETIME type, use the following construct:

CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Adding data to this table is done using the SQL command INSERT . For example:

INSERT INTO books (ISBN, author, title, price, quantity)
           VALUES ('5-8459-0184-7', 'Zandstra Mat',
                   'Do it yourself independently of PHP4 in 24 hours', '129', '5');

To retrieve data from a table, use the SELECT statement . It extracts data from the database, selecting the rows that match the specified search criteria. The SELECT statement accompanies a large number of options and use cases.

The * indicates that all fields are required. For example:

SELECT * FROM books;

To access only a certain field, you must specify its name in the SELECT statement . For example:

SELECT author, title, price FROM books;

To access a subset of rows in a table, you must specify a selection criterion that sets the WHERE clause . For example, to select available inexpensive books about PHP, you need to compose a query:

SELECT * FROM books WHERE
 price <200 AND title LIKE '% PHP%' AND quantity! = 0;

% Matches any number of characters, even zero
_ Match exactly one character

To ensure that strings extracted on request are listed in a certain order, the ORDER BY clause is used . For example:

SELECT * FROM books ORDER BY price;

By default, the sort order is in ascending order. You can change the sort order to reverse by using the DESCkeyword :

SELECT * FROM books ORDER BY price DESC;

You can also sort by several columns. Instead of column names, you can use their sequence numbers:

SELECT * FROM books ORDER BY 4, 2, 3;

To change previously recorded values in the table, use the UPDATE command . For example, the price of all books was increased by 10%:

UPDATE books SET price = price * 1.1;

The WHERE clause will limit the UPDATE operation to specific strings. For example:

UPDATE books SET price = price * 1.05 WHERE price <= 250;

To delete rows from the database, use the DELETE statement . Unnecessary strings are specified using the WHERE clause . For example, some books are sold:

DELETE FROM books WHERE quantity = 0;

If you want to delete all records

TRUNCATE TABLE table_name

To completely delete a table, use:

DROP TABLE table_name

Linking PHP to a MySQL database

After working with phpMyAdmin on creating a database, you can begin to connect this database to an external Web-interface.

To access the database from the Web using PHP, you need to do the following basic steps:

Connecting to the MySQL server.
Selecting the database.
Executing the query to the database:
- Addition ;
- removal ;
- change ;
- search ;
- sorting .
Getting the result of the query.
Disconnect from the database.

To connect to a database server in PHP, there is a function called mysql_connect () . Its arguments are: computer name, username and password. These arguments can be omitted. By default, the computer name is localhost , then no username and password is required. If PHP is used in conjunction with the Apache server, you can use the mysql_pconnect () function . In this case, the connection to the server does not disappear after the program is terminated or mysql_close () is called. The mysql_connect () and mysql_pconnect () functions return a connection identifier if everything went well. For example:

$ link = mysql_pconnect ();
if (! $ link) die ("Unable to connect to MySQL");

After the connection to the MySQL server is established, you need to select the database. To do this, use the mysql_select_db () function . Its argument is the name of the database. The function returns true if the specified database exists and access to it is possible. For example:

$ db = "sample";
mysql_select_db ($ db) or die ("Unable to open $ db");

To add, delete, modify, and select data, you must construct and execute a SQL query. For this, there is a function called mysql_query () in PHP . Its argument is a string with a query. The function returns the query ID.

So, as the architecture of Web-databases works:

The user's Web browser issues an HTTP request for a specific Web page. For example, a user using an HTML form searches for all books about PHP. The form processing page is called search_book.php.
The Web server accepts the request for search_book.php, extracts this file and sends it to the PHP engine for processing.
PHP connects to the MySQL server and sends a request.
The server accepts the request to the database, processes it, and sends the result (the list of books) back to the PHP engine.
The PHP engine terminates the script, formats the result of the query in HTML. After that, the result in the form of HTML is returned to the Web server.
The Web server sends HTML to the browser, and the user can view the requested list of books.

Using the transaction mechanism

Using the transaction mechanism by the example of how to transfer money from one person to another

if(
    mysql_query ("BEGIN") &&
    mysql_query ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'") &&
    mysql_query ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'") &&
    mysql_query ("COMMIT")
){
    echo "Успешно";
}else{
    mysql_query ("ROLLBACK");
    echo "Не успешно";
}

SELECT ... FOR UPDATE

If you run multiple processes that make a select query on the same table, they can select the same record at the same time.

To avoid the above situation, you need to execute not just a SELECT query, but its extended version, which many do not suspect: SELECT ... FOR UPDATE.

Thus, when this query is executed, all affected records in the database will be blocked until the database session is completed or until the data of the records is updated. Another script will not be able to select blocked records until one of the above conditions occurs.

However, not all so simple. You need to fulfill a few more conditions. First, your table must be built on the basis of the InnoDB architecture. Otherwise, the lock will not work. Secondly, before performing a sample, you must disable the auto-commit request. Those. in other words, automatic execution of the request. After you specify the UPDATE query, you will need to access the database again and commit changes using the COMMIT command:

mysql_query("SET autocommit = 0");
$result = mysql_query("SELECT * FROM table WHERE locked = 0 LIMIT 1 FOR UPDATE");

$row = mysql_fetch_assoc($result);

mysql_query("UPDATE table SET locked = 1 WHERE id = 1;");
mysql_query("COMMIT;");

Imagine that we have 2 records in the table. We run two parallel scripts with this content. As a result, the first process that will be able to reach the database will select the record and immediately replace the value in the locked field by 1. As a result, the second script ignores the first record. In this case, even if the scripts accessed the database simultaneously, the second script would still not be able to select the first record, because when selected by the first process, it will be blocked.

Tech Blog

Monday 27 August 2018