PHP example with MySQL. Basics of working with PHP Mysqli

No doubt, MySQLI is significantly better than standard MySQL expansion PHP, although the principles of their work are quite similar. I hope the above information will be useful when creating and transferring projects in the future. For convenience, the following was implemented to download example files. This can be done by clicking on the download button.

Working with MySQL database with RNR

Lecture. Prepared Prokhorov VS

1. Connection of PNP scenarios with MysQL tables

Consider the most frequently used features that allow you to work with the MYSQL database by the RNR.

When the RNR and MySQL interacts, the program interacts with the DBMS by means of a set of functions.

1.1 Connection to the server. Function mySQL_Connect.

Before working with the database, you must install a network connection with it, as well as to authorize the user. This is the MYSQL_CONNECT () function.

resource Mysql_Connect (]])

This feature sets the network connection to the MySQL database located on the $ Server host (default is Localhost, i.e. current computer) and returns an open connection identifier. All further work is carried out with this identifier. All other functions taking this identifier (descriptor) as an argument will uniquely define the selected database. When registering, the user name is $ UserName and the $ password password (by default, the user name from which the current process is running - when debugging scripts: root, and an empty password):

$ dbpasswd \u003d ""; //Password

// Display a warning

echo ("

");

Variables $ dblocation, $ dbuser and $ DBPASSWD store server name, username and password.

1.2 Tarve the connection to the server. Function mysql_close

The connection to the MySQL server will be automatically closed at the end of the script, or when calling the MYSQL_CLOSE function

bOOL MYSQL_CLOSE ()

This feature breaks the connection to the MySQL server, and returns TRUE with successful execution of the operation and FALSE otherwise. The function accepts a database connection descriptor as an argument returned by the MySQL_Connect function.

$ dblocation \u003d "localhost"; // Server Name

$ dbuser \u003d "root"; //Username

$ dbpasswd \u003d ""; //Password

// Enter connection to the database server

// Suppress the error output symbol @ before calling the function

$ dbcnx \u003d @ mysql_connect ($ dblocation, $ dbuser, $ dbpasswd);

if (! $ dbcnx) // If the descriptor is 0, the connection is not installed

// Display a warning

echo ("

B Now the database server is not available, so the correct display of the page is impossible.");

if (mysql_close ($ dbcnx)) // break the connection

echo ("connection to the database is discontinued");

echo ("HE managed to complete the connection");

1.3 Creating a database. Create Database feature

Command - Creating a database is available only to the server administrator, and on most hosting it is impossible to perform it:

Create Database Name Based

Creates a new database with the name of the name binding.

An example of working with this feature:

@Mysql_Query ("Create Database $ dbName");

It is recommended to use apostrophes everywhere ("SQL - command") as limiters of lines containing SQL commands. This can ensure that no $ variable will accidentally be interpolated (i.e. it will not be replaced by its value), and the safety of scripts will increase.

The Create Database database crew command is available only to the superuser, and it is impossible to perform a simple user on most hosting users. It is only available to the server administrator.

For experiments, create a TestBase database by performing a SQL query from the command line. To do this, log in to the MySQL system and enter MySQL on the command prompt:

mySQL\u003e Create Database Testbase;

After that, you should dial:

mySQL\u003e Use Testbase;

The database is created:

1.4 Select database. Function mysql_select_db.

Before sending the first request to the MYSQL server, you must specify which database we are going to work. For this purpose, the mysql_select_db function is intended:

bOOL MYSQL_SELECT_DB (String $ Database_Name [, Resource $ Link_identifier])

She notifies PHP that in further operations with a $ Link_identifier connection will be used $ database_name.

Using this function is equivalent to calling a USE command in a SQL query, i.e. the MySQL_Select_DB function selects a database for further work, and all subsequent SQL queries apply to the selected database. The function takes the name of the Database_Name selected database as arguments and the Resource connection descriptor. The function returns TRUE with successful operation and FALSE - otherwise:

// Connection code with database

if (! @mysql_select_db ($ dbname, $ dbcnx))

// Display a warning

echo ("

B Now the database is not available, so the correct page display is not possible.");

1.5 Processing errors

If errors occur during work with MySQL (for example, the prompts are not balanced or there are not enough parameters), then the error message and its number can be obtained using the two functions described below.

It is important to carefully and use these functions in a timely manner, because otherwise the debugging of the scripts may become more complicated.

● Function:

iNT MYSQL_ERRNO ()

returns the number of the latest recorded error. You can not specify the $ Link_IDENTFIER connection identifier if only one connection was installed during the script running time.

● Function:

string MySQL_ERROR ()

returns not a number, but a string containing the text of the error message. It is convenient to apply in debugging purposes. Usually, MySQL_ERROR is used with the design or DIE (), for example:

@Mysql_Connect ("LocalHost", "User", "Password")

oR DIE ("Error connecting to a database:" .mysql_error ());

The operator @, as usual, is used to suppress the standard warning, which may occur in case of an error.

In the latest versions of the RNR warning in MySQL functions are not registered by default.

1.6 Connection to MySQL. File ( config.php. )

Usually on the site there are several scripts at once, which you need access to the same database.

The code responsible for connecting to MySQL is recommended to be highlighted in a separate file, and then connect using the Include function to the desired scripts.

It makes sense to place functions for connecting, selecting and creating a database to the same file (config.php), where variables are declared with the name of the $ dblocation server, the $ dbuser user name, password $ dbpasswd and the name of the $ dbname database name:

CONFIG.PHP Listing:

//config.php file code containing connection parameters with server and database selection

// Displays error messages to the browser

$ dblocation \u003d "localhost"; // Server Name

$ dbname \u003d "insert base name" // Database name: created or already existing

$ dbuser \u003d "root"; // Database User Name

$ dbpasswd \u003d ""; //Password

// Enter connection to the database server

// Suppress the error output symbol @ before calling the function

[Email Protected]_Connect ($ dblocation, $ dbuser, $ dbpasswd);

if (! $ dbcnx) // If the descriptor is 0, the connection to the database server is not installed

// Display a warning

echo ("

Currently, the database server is not available, so the correct display of the page is impossible.

// Create a $ DBName database - it can only do a superuser

// If the database already exists, there will be a non-critical error

@Mysql_Query ("Create Database IF Not Exists $ dbname ');

// Connection code with a database: we carry out a unambiguous selection of the newly created database or an existing database

// Suppress the error output symbol @ before calling the function

if ( [Email Protected]_select_db ($ dbname, $ dbcnx)) // If the descriptor is 0, the database connection is not installed

// Display a warning

echo ("

Currently, the database is not available, so the correct display of the page is impossible.

// Small auxiliary function that displays the message

// About the error in case of a database request error

fUNCTION PUTERROR ($ Message)

echo ("");

2. Perform database queries

2.1 Creating a table. Function CREATE TABLE:

Create Table Table Name (NamePole Type, NamePole Type,)

This command in the database creates a new table with columns (fields), defined by its own names (names) and specified types. After creating the table, it will be possible to add records consisting of the fields listed in this command.

Listing test_11.php. A program that creates a new table in the database:

include "config.php"; // Connect to server and database selection

mySQL_Query ("Create Table IF Not Exists People

iD INT AUTO_INCREMENT PRIMARY KEY,

oR DIE ("MySQL Error:" .mysql_error ());

This script creates a new PEOPLE table with two fields. The first field has an int type (integer) and name ID. Second - Type Text (text string) and Name name.

If the table exists, the design of the OR DIE () will work.

Optional phrase if not exceeds, if it is specified, says the MySQL server, that it should not generate an error message if the table with the specified name already exists in the database.

MySQL is one of the types of relational databases. MySQL is a server to which various users can connect.

When you connect to the Internet, you enter the login and password, as well as the name of the server to which you connect? When working with MySQL, the same system is used.

Another point: What is a relational database? Relational means based on tables. The famous EXCEL electronic table editor from Microsoft is actually editor of relational databases.

Connect to the MYSQL server

To connect to the MySQL server, the MySQLi_Connect () function is used in PHP. This feature receives three arguments: server name, username and password.

The mysqli_connect () function returns the connection identifier, it is saved in a variable and are used to work with databases.

Connection code to the MYSQL server:

$ link \u003d mysqli_connect ("Localhost", "root", "");

In this case, I work on a local computer on denwere, so the host name is Localhost, the name of the ROOT user, and the password is not.

The connection also needs to be closed, after completing work with MySQL. To close the connection, the mysqli_close () function is used. Expand example:

$ link \u003d mysqli_connect ("Localhost", "root", ""); if (! $ Link) DIE ("Error"); mysqli_close ($ Link);

Here we have verified the identifier for truth, if something is wrong with our connection, then the program will not be executed, the DIE () function will stop its execution and displays an error message to the browser.

Connection errors

To check the connection, the following functions are used:

• mysqli_connect_errno () - Returns the error code of the last connection attempt. With no error returns zero.
• mysqli_connect_error () - Returns the description of the last connection error to the MYSQL server.

The mysqli_get_host_info () function returns a string containing the type of connection used.

Also note, using the Define command, I saved all connections in constants. When you write large projects, and connect to the MySQL server will be many files, it is convenient to store the connection settings in a separate file and insert it using the Include or Require function.

Select database

The MySQL server may have several databases. First of all, we need to choose to work the base we need. In PHP, for this, in the mysqli_connect () function, there is another parameter - the name of the database.

I created on my computer via phpmyAdmin named Tester. Connect to it:

$ Link \u003d mysqli_connect ("localhost", "root", "", "Tester"); if (! $ Link) DIE ("Error"); MySQL_Close ($ Link);

So, we have chosen to work the database. But as we know, the relational database consists of tables, and in our database there are no tables yet. The database is created empty, without tables. Tables in it need to be added separately. Here let's add a table with PHP to it.

Create table

In the name of MySQL databases, the SQL part indicates the Structured Query Language, which is translated as a structured query language. In SQL language, we will write requests and from the PHP program to send them to the MYSQL server.

To create a table, we just need to specify the CREATE TABLE command. Let's create a table with the names of Users in the columns of which will be stored login (Login column) and passwords (Password column) users.

$ Query \u003d "Create Table Users (Login Varchar (20), Password Varchar (20))";

In this code, we assigned the $ Query variable to the text string, which is a SQL query. We create a table with the name of Users, which contains two columns Login and Password, in both VARCHAR data type (20). We will talk about data types later, now I will only note that Varchar (20) is a string of the maximum length of 20 characters.

To send our request to the MySQL server, we use the PHP function MySQLI_Query (). This feature returns a positive number if the operation has been successfully and false if an error occurred (the query syntax is erroneous or the program has no rights to execute the query).

$ Link \u003d mysqli_connect ("localhost", "root", "", "Tester"); if (! $ Link) DIE ("Error"); $ Query \u003d "Create Table Users (Login Varchar (20), Password Varchar (20))"; Mysqli_Query ($ Query); mysqli_close ($ Link);

The SQL query is not necessary to record in the variable, it can be immediately written as the argument of the mysql_query () function. Just so the code will send readable.

This script has one drawback - he does not withdraw anything to the browser. Let's add a message:

$ Link \u003d mysqli_connect ("localhost", "root", "", "Tester"); if (! $ Link) DIE ("Error"); $ Query \u003d "Create Table Users (Login Varchar (20), Password Varchar (20))"; If (Mysqli_Query ($ Query)) Echo "Table created."; ELSE ECHO "Table not created."; mysqli_close ($ Link);

If we restart this script for execution, we will see a message in the browser: "The table is not created." The fact is that the table was created when you first start, and it is not possible to create a table with the same name. We encountered a situation of error, it means that it is time to talk about error processing when working with MySQL.

Error processing

When debugging the program, we may need accurate error information. When an error occurs in MySQL, the database server sets the error number and string with its description. To access this data in PHP there are special functions.

• mysqli_errno () - Returns the error number.
• mysqli_error () - returns a string with an error description.

Now let's add the mysql_error () function to our script:

$ link \u003d mysql_connect ("localhost", "root", "", "tester"); if (! $ Link) DIE ("Error"); $ Query \u003d "Create Table Users (Login Varchar (20), Password Varchar (20))"; If (Mysqli_Query ($ Query)) Echo "Table created."; ELSE ECHO "The table is not created:" .Mysqli_error (); mysqli_close ($ Link);

Now our script will return to the browser string: "The table is not created: Table" Users "AlReady Exists".

Delete table

So, we now have the table you do not need. It's time to learn to delete tables from the database.

To delete the table, use the DROP TABLE command, followed by the table name.

$ Link \u003d mysqli_connect ("localhost", "root", "", "Tester"); if (! $ Link) DIE ("Error"); $ Query \u003d "Drop Table Users"; if (! Mysqli_Query ($ Query)) echo "Error deleting a table:" .mysqli_error (); ELSE ECHO "Table deleted."; mysqli_close ($ Link);

RESULTS

So, we mastered the basics of MySQL. What we learned to do:

• Connect to MySQL database using the mysqli_connect () function.
• Close the connection to the MySQL server using the mysqli_close () function.
• Send SQL requests to the MySQL server using the mysqli_query () function.
• We learned the SQL query to create a table: Create Table.
• We learned the SQL request to delete the table: Drop Table.
• We learned how to process errors using mysqli_errno () and mysqli_error () functions.

Then we will consider in detail the types of MySQL data.

We read the following lesson:

3 Methods for connecting to MySQL with PHP with code examples

To start using MySQL database, you must first understand how to connect from your user PHP program (script) to this MYSQL database.

This article describes the following three methods, as well as the corresponding examples of the PHP code, which explains how to connect to your database from PHP.

For all examples below, we will connect to an already existing MySQL database. Note: All that is explained here will also work with MariaDB, as well as with MySQL.

1. Connect to PHP using the MYSQLI extension
*mySQLI means mysql improved

Create the following MySQLI.php file

connect_error) (DIE ("Error: Unable to connect:". $ Conn-\u003e Connect_error);) Echo "connected to the database.
"; $ result \u003d $ Conn-\u003e Query (" SELECT ID FROM GORODA "); echo" number of rows: $ result-\u003e num_rows "; $ result-\u003e close (); $ Conn-\u003e Close ();?\u003e In The above code:

• mySQLI - This feature initiates a new connection using the MYSQLI extension. The function takes four arguments:
  1. localhost host name on which the MYSQL database is running
  2. name - MySQL user name for connecting
  3. pASS - Password for MySQL User
  4. dB - MySQL database for connection.
• qvery - MYSQL query function. In this example, we choose a column ID from the cities database.
• Finally, we show the number of rows selected using the NUM_ROWS variable as a result. We also close both the result and the connection variable, as shown above.

Connected to the database. Number of rows: 6 2. Connect from PHP MySQL PDO Extension
*PDO means PHP data objects

PDO_MYSQL driver implements PDO interface provided by PHP to connect from your PHP script to MySQL database.

Create the following MySQL-PDO.php file:

setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION); Echo "connected to the database.
"; $ SQL \u003d" SELECT ID FROM GORODA "; PRINT" list ID:
"; Foreach ($ Conn-\u003e Query ($ SQL) AS $ ROW) (Print $ Row [" ID]. "
";) $ Conn \u003d NULL;) Catch (ECHO" Error: Unable to connect: ". $ ERR-\u003e getMessage ();)?\u003e In the above:

• new PDO - Create a new PDO object, which will take the following three arguments:
  1. mySQL Connect String: It will be in the format "MySQL: HOST \u003d LocalHost; dbname \u003d db". In the example above, DB works on LocalHost, and we connect to the DB database.
  2. MySQL Name for Connection
  3. MySQL user password
• variable $ SQL - Create a SQL query you want to perform. In this example, we choose a column ID from the city table.
• query ($ SQL). Here we do the SQL request, which we have just created.
• foreach. Here we turn out the result from the query command above and save it in the $ ROW variable, and then output it using ECHO.
• In MySQL PDO to close the connection, simply set the $ Conn variable to NULL.

Connected to the database. List ID: 1 2 3 4 5 6 3. Connect from PHP c Use of outdated functions MySQL

Use this method only if you use the older version of PHP and for some reason you cannot update it to the new version. It is recommended to use Method No. 2 and Method No. 3, shown above, instead of this method. I turned on this method only for reference, and not as a recommendation for use.

This specific extension was outdated from PHP 5.5. But, starting with the version of PHP 7.0, it will not even work because it has been removed. Starting with PHP 5.5, when you use these functions, it will generate an E_DepRecated error.

Create MySQL.php:

"; $ result \u003d mysql_query (" SELECT ID FROM GORODA "); $ row \u003d mysql_fetch_row ($ result); echo" ID 1: ", $ row,"
\\ n "; mysql_close ($ Conn);?\u003e in the above:

• The MySQL_Connect feature takes three arguments:
  1. host name in which the MySQL database is running;
  2. mySQL user name for connecting;
  3. password for MySQL user. Here it connects to the MySQL database, which is performed on the local server using the username and password.
• The mysql_select_db function. As the name follows, it selects the database to which you want to connect. Equivalent to the "use" command. In this example, we connect to the DB database.
• The MySQL_Query function is used to specify your MYSQL query. In this example, we choose a column ID from the cities database.
mysql_fetch_row. Use this feature to extract rows from the SQL query that we have just created.
• Finally, close the connection using the MySQL_CLOSE command, as shown above.

Connected to the database. ID 1: 1 This way can be connected to MySQL. I repeat, it is better to use the first two ways; about

In this article, we will consider ways to access the base tables this MySQL using the SQL query language. SQL is an abbreviation that is so "revealed" - a structured query language.
In PHP language, there are a number of functions with the prefix "MySQL". We will need not much of them for consideration of requests. The function, without which in the PHP language, the execution of SQL queries would be simply impossible:

Resource MySQL_Query

This feature sends a database request and returns the resource identifier if successful circulation.
In order to connect to the MySQL database, you must perform the following sequence:

$ HOST \u003d "LocalHost"; // Host name (specified by the provider) $ database \u003d "db_name"; // Database name that you must create $ user \u003d "user_name"; // The username you specified, or a $ PSWD \u003d "Your_Pass" provider defined by you; // Password you specified $ dbh \u003d 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 database.");

so mySQL_Connect ()- Function for connecting to MySQL server on your hosting.
BUT mysql_select_db () Selects the database on the connection server.
In other words, you connect to the server, select the database and start working.
The DIE () function is called if an error displays the message that you specified into the browser window.
Function is used to complete with databases:

Mysql_close ($ dbh);

Here $ dbh. - descriptor, which when connected returned a function mySQL_Connect..
Having finished the starting review, let's start considering the SQL query itself.
To do this, first of all, you need to create a database with a specific name. And in it, create a table, also with a specific name. In our examples we will contact the table my_sql_table. To create this table, let's execute our localhost in phpmyadmin.

Create Table `My_SQL_Table` (Int Not Null, // Identifier Future Table Records` FirstName` Varchar (50) Not Null, // Text field VARCHAR `Surname` Varchar (50) Not NULL, // MAX 50 characters long PRIMARY KEY (`id`) // Primary key - ID identifier);

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

\\ n "; echo" Name: "$ ROW [" firstName "]."
\\ n "; Echo" Surname: "$ ROW [" SURNAME "]."

We will analyze the PHP file code firstSQL.php.. Let's start with the actual query to the database tables (database).

$ Query \u003d "Select * from` My_sql_table` ";

This request can be decrypted: Select from the table my_sql_table DB All entries from all fields. Thus sign * After the word SELECT means "choose absolutely everything". So the request is formed. Now it must be executed:

$ res \u003d mysql_query ($ Query);

In case of successful execution of the query function mySQL_Query () Returns us a resource identifier $ Res..
It should be transferred as a parameter to the function mysql_fetch_array (). The name of this function speaks for itself. Those. It forms and gives an array by 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 (strings) in the table and contain values iD, FIRStName, Surname For each row of the table. Therefore, the following code:

While ($ row \u003d mysql_fetch_array ($ Res)) (Echo "number:". $ Row ["ID"]. "
\\ n "; echo" Name: "$ ROW [" firstName "]."
\\ n "; Echo" Surname: "$ ROW [" SURNAME "]."

you can comment on this: While the $ Row variable entered by us receives no zero operation results mysql_fetch_row You should issue a field value in the browser $ row ["id"], $ row ["firstname"], $ row ["Surname"] via echo..
If the query is performed like this:

$ Query \u003d "Select FirstName from` my_sql_table ";

this will mean that only the values \u200b\u200bof the FIRStName field are selected from all lines.
Consequently, the previous code should be rewritten as:

$ res \u003d mysql_query ($ Query); While ($ row \u003d mysql_fetch_array ($ res)) (Echo "name:". $ row ["firstName"]. "
\\ n ";)

If you want to select a table lines with a specific value id Where is the surname (Surname) will PetrovThe request will rewrite as follows:

$ Query \u003d "SELECT ID from` My_sql_table` Where Surname \u003d "Petrov" ";

But if you need to know the surname of the one who is under the number, for example, 5, then the request will be like this:

$ Query \u003d "Select Surname from` My_sql_table` Where id \u003d 5 ";

In this case, you know that the result of the query will be only one line from the table. Those. It makes no sense to organize a cycle using while. And query processing will be the following

$ res \u003d mysql_query ($ Query); $ row \u003d mysql_fetch_row ($ RES); Echo "Surname of the fifth person in the list:". $ row. "\\ n";

Here instead of mysql_fetch_array () we applied mysql_fetch_row (). Those. Get the field value (or fields) of a specific string. Since we had one field - Surname - we can refer to the only element of the $ row array as $ row;.

So, consider the most typical examples of MYSQL queries. Consideration will spend on the basis of the table my_sql_table:
1. Add to the MY_SQL_Table table MIDDLE_NAME field (patronymic) after surname.:

$ Query \u003d "Alter Table` MY_SQL_TABLE` ADD` Middle_name`
Varchar (50) Not Null After `Surname`

2. Now delete the Surname field from the MY_SQL_Table table:

$ Query \u003d "Alter Table` My_SQL_Table` Drop `Surname`";

3. Delete the entries from the MY_SQL_TABLE table with the surname:

$ Query \u003d "delete from` My_sql_table` Where Surname \u003d "Sidorov" ";

4. In addition to signs of equality, also "more" or "less", in the language of MySQL requests there is a concept " similar to". Select records from the My_SQL_Table table, where in the surname is found" dor." :

$ Query \u003d "Select * from` My_sql_table` Where Surname Like "% Dor%" ";

Here is the presence " % "At the beginning and end of" DOR "and means that the request will look for exactly the" Dor ", and it does not matter at the beginning, late, or the middle of the last name it is located. Consider the following example
5. Select records from the MY_SQL_TABLE table with the surname that starts on P. Pay attention to the location " % ":

$ Query \u003d "Select * from` My_sql_table` Where Surname Like "P%" ";

6. Calculate the maximum value. id:

$ Query \u003d "SELECT MAX (ID) from` My_sql_table` ";

7. Calculate the number of fields in my_sql_table with the surname that starts on P.

$ Query \u003d "Select Count (*) from` My_sql_table` Where Surname Like "p%" ";

8. Deleting the MY_SQL_Table table:

$ Query \u003d "Drop Table` My_sql_table` ";

For queries 1-3 in the PHP language, it is sufficient to simply execute the request:

MySQL_Query ($ Query);

We reviewed the most characteristic examples of requests. I believe, with their help, following the elementary logic, you can perform more complex requests to the MYSQL database tables you create.