Mysql connection to database. How to create a connection to a mysql database in php. Using the SQL Editor

In this article today we’ll talk about creating a connection to a database and discuss which option is better to use procedural or object-oriented. First, let's look at what level we are at, if this is the level complete newbie, then my advice without exception is to start using a procedural style of connecting to the database. Previously, I wrote an article on this topic on my blog; for more information about the procedural style of connecting to a database, read the article: “How to connect to MySQL using PHP”. If you already have some experience working with a procedural style of connecting to a database, then you probably, like me, just took my projects and forced them to use an object-oriented approach.

One way or another, we will now analyze the stages of constructing a class to create a connection to the database MySQL data on PHP language. We will need two PHP files, in one file we will “put” the class for creating a connection to the database, and in the second we will work with this class.

Let's create two files:

• index.php;
• database.class.php;

I think we are not little children anymore and we know what we need to work with PHP files. Installed web- Apache server, PHP, MySQL DBMS and knows where to put these files - (for those who don’t know or have forgotten).

I put the file in which the class is stored in separate file and I name it in the format: class name.class.php and I know what is stored in this file. When there are many classes in a project, you can get lost, so I recommend naming files with classes in the format described above.

Database.class.php file:

Let's now look at what was created at this step. Using the keyword "class", the class name - DataBase and curly braces, we created the body of the class. In the created class, we created two properties, in $mConnect - where the result of connecting to the database is stored and $mSelectDB - where the result of selecting a database is stored. You may have noticed the keywords in each property - public and static. What are they talking about? Public means that the property is accessible from outside the class, and static makes it possible to access or call the property without creating an instance of the class, which is very often convenient in work.

Let's add the Connect() method to create a connection to the database:

".mysql_error()."

"; exit(); return false; ) // Return the result return self::$mConnect; ) ) ?>

• $host - server IP address, on a local PC it is localhost;
• user - database user name;
• $pass - database user password;
• $name - the name of the database to which we connect;

The mysql_connect() function creates a connection to the database and stores the execution result in $mConnect. Next comes a check with the IF construct: If the connection was not successful, display an error message... Otherwise, PHP will ignore the IF block and continue to select the database. The mysql_select_db() function selects the database name, if the requested database does not exist in the database, in this case the program will inform the user of an error. If everything is successful, the database connection will return return.

Add the Close() method:

Sorry, we were unable to connect to the MySQL server

"; exit(); return false; ) // Try to select a database self::$mSelectDB = mysql_select_db($name, self::$mConnect); // If the database is not selected, display an error message.. if( !self::$mSelectDB) ( echo "

".mysql_error()."

"; exit(); return false; ) // Return the result return self::$mConnect; ) // The method closes the connection to the database public static function Close() ( // Returns the result return mysql_close(self::$mConnect) ; ) ) ?>

The next and last method in this class, Close(), closes the connection to the database; the mysql_close() function closes the connection to the MySQL server and returns the result.

Index.php file:

associative array$row = mysql_fetch_assoc($result); // Displays the MySQL server version echo $row["VERSION"]; // Close the connection to the database DataBase::Close(); ?>

Using the define() function, we created constants to store database connection parameters. Require_once includes our DataBase class in the index.php file.

Let's remember keyword static, which was used in the DataBase class in the properties and methods of this class. This gives us the ability to access class properties and methods using "::" (two colons). The DataBase::Connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE) method takes 4 parameters - constants created by the define() function, which store information about the connection to the database. If the connection is successful, the Connect() method will return us a database connection. Next, we will be able to work and execute queries to the database. mysql_query() function - executes a query to the database. The mysql_fetch_assoc() function processes a series of query results and returns an associative array. The echo construct displays the MySQL server version. And finally, the DataBase::Close() method will close the connection to the database.

In order to get the most out of your MySQL database, it is important to understand how to connect from user program in PHP to a MySQL database.

This tutorial describes the following three methods along with a corresponding PHP example program that will explain how to connect using PHP to a database.

• Connect using Mysqli extension (recommended)
• Connect using PDO (recommended)
• Connecting using traditional legacy mysql_ functions (obsolete)

To do this, you need to install the PHP-MySQL package.

Based on RedHat distribution including , use yum for PHP-MySQL installations as shown below.

Yum install php-mysql

Depending on your system, we will install or update the following dependencies above:

• php-cli
• php-common
• php-pdo
• php-pgsql

Once everything is installed, the phpinfo page will display the MySQL module as shown below:

For all of the examples below, we will be connecting to a MySQL database that already exists. If you're new to MySQL, this is a good place to start: .

Note: Everything described here will also work with MariaDB, just as it works with MySQL.

1. Connection in PHP using the Mysqli extension

MySQLi stands for MySQL Improved.

Please note that on most distributions (eg: CentOS), PHP-MySQLi is already part of the PHP-MySQL package. This way you don't have to search for and install the PHP-MySQLi package. All you need to do is install the PHP-MySQL package to get a working Mysqli extension on your system.

Create the following mysqli.php file in DocumentRoot in Apache:

connect_error) ( die("Error: Unable to connect: " . $conn->connect_error); ) echo "Connecting to the database.
"; $result = $conn->query("SELECT name FROM employee"); echo "Number of rows: $result->num_rows"; $result->close(); $conn->close(); ?>

In the above:

• MySQLi – This function will initiate a new connection using the Mysqli extension. This function will take four arguments:
  1. Hostname where the MySQL database is running
  2. Username for MySQL connection
  3. Mysql user password
  4. MySQL database to connect to.
• Query Function – Use this to specify your MySQL query. In this example, we select the name column from the employee database.
• Finally, we display the number of rows selected using the num_rows variable. We also close the connection as shown above.

Connect to the database. Number of lines: 4

Note: If you are trying to connect to a remote MySQL database, then you can do this to avoid the host connection denied error: How to allow a MySQL client to connect to a remote MySQL server.

2. Connecting using PHP to MySQL with PDO extension

PDO stands for PHP Data Objects.

PDO_MYSQL implements the PDO interface provided by PHP for connecting a program to a MySQL database.

In the majority Linux distributions(eg CentOS and RedHat), the PHP-PDO package is already included in the PHP-MySQL package. This way you don't have to search for and install the PHP-PDO package. All you need to do is install the PHP-MySQL package to get a working PDO_MYSQL PHP extension on your system.

Create the following MySQL-pdo.php file in your Apache DocumentRoot:

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo "Connecting to the database.
"; $sql = "SELECT name FROM employee"; print "Employee name:
"; foreach ($conn->query($sql) as $row) ( print $row["name"] . "
"; ) $conn = null; ) catch(PDOException $err) ( echo "Error: Unable to connect: " . $err->getMessage(); ) ?>

In the above:

• new PDO – Create new object PDO which will take the following three arguments:
  1. MySQL connection string: will be in the format “mysql:host=$hostname;dbname=$dbname”. In the above example, the database is running on localhost and we are connecting to the andreyex database.
  2. Username to connect to MySQL.
  3. Password for mysql user.
• $sql variable – creation SQL query which you want to execute. In this example, we select the name column from the employee table.
• query($sql) – here we execute the SQL query we just created.
• foreach – This is where we run through the above commands and store them in the $string variable and then we show them using the print command.
• In MySQL PDO, to close a connection, simply reset the $conn variable to zero.

When you call mysqli.php from your browser, you will see the following output, indicating that PHP was able to connect to the MySQL database and fetch the data.

Connect to the database. Employee Name: siteslan Maria Oleg

3. Connection in PHP using mysql_ functions (outdated)

Use this method only if you are using an older one PHP version and you can't update it to new version for some reason.

This is outdated PHP extension 5.5 version. But starting from PHP 7.0 version, this will not work since it was removed.

As of PHP 5.5, when you use these functions, they will generate an E_DEPRECATED error.

Create the following MySQL-legacy.php file under Apache DocumentRoot:

"; $result = mysql_query("SELECT name FROM employee"); $row = mysql_fetch_row($result); echo "Employee 1: ", $row, "
\n"; mysql_close($conn); ?>

In the above:

• The mysql_connect function takes three arguments: 1) the hostname where the MySQL database is running, 2) the username to connect to MySQL, 3) the password for the MySQL user. Here we connect to the MySQL database, which runs on local server using the root username and password.
• mysql_select_db function – As the name suggests, will select the database you want to connect to. This is equivalent to the "use" command. In this example we are connecting to the andreyex database.
• mysql_query function – Use this to specify your MySQL query. In this example, we select the name column from the employee database.
• mysql_fetch_row - Use this function to fetch rows from the SQL query we just created.
• Finally close the connection using the mysql_close command as shown above.

When you call MySQL-legacy.php from your browser, you will see the following output, indicating that PHP was able to connect to the MySQL database and fetch the data.

Connect to the database. Employee 1: AndreyEx

Connecting to the database mysql is created using the mysql_connect() function. The variables used to establish the connection are indicated in parentheses.
Which ones exactly?

1. $location - indicates the server on which the script is located. In most cases this is localhost.
2. $user - in this variable we write the database user name
3. $password - database user password
After connecting to the database, you must select a database name. To do this, use the mysql_select_db() function. We write two parameters in parentheses:
1. $dbname - in this variable we indicate the name of your database. The name can be anything. We write everything, of course, in English.
2. $connect - database connection descriptor. If the connection to the database fails, the variable takes the argument false

The code for connecting to the database is as follows:

No connection to database

"); exit(); ) ?>

You can write this code directly inside any file where you work with the database. But in most cases they create a separate file where they write this code. There you can also specify all the variables and constants to indicate general settings the entire application.

How to create a connection to the phpmyadmin database

Most often, any web programmer begins such an operation on a local server. Because first, any new web application or website is written and edited in regular home computer. After the programmer checks all the systems and is convinced that everything works reliably and smoothly. Only after this everything is uploaded to a remote server.

Connection occurs, as described above, using the connection functions and the selection function Database. Only a small difference. If you do everything on local computer database username, in most cases root. There is no password or we enter the simplest one, two, three.

Why complicate the whole system for yourself?

Phpmyadmin This is a special web interface for managing all databases located on your local server. Because managing the database via the console is extremely inconvenient.

Let's create a database connection to the site in PHP

Now we move on to the most important work of transferring our website or application to a remote server. Now you should keep in mind that for your website to function properly, you will need paid hosting with PHP support 5 and higher, MySql is required with the Phpmyadmin interface and the entire interface file manager, to manage your website files.

When purchasing hosting, you must be given an information letter containing all the parameters of your server. And for connecting the database to yours website, you create your own database, enter a name and password.

On many hosting sites, everything happens differently. Either you enter your login, password or password yourself; the login is set automatically when the database is created.
The connection code is as follows:

No connection to the database"); exit(); ) if (! @mysql_select_db($dbname,$connect)) ( echo("

No connection to database

"); exit(); ) ?>

As you noticed, nothing complicated. You just took the same file and just changed a few variables and that's it. Just remember one rule: when transferring a site to a remote server, you must change three variables in the configuration file, namely:

1. $dbname = "base"; // database name
2. $user = "yourlogin"; // database username
3. $password = "123456789"; // database user password

Before moving on to the article, I want to apologize for the delays in writing them. Now the exam session is underway, so it’s not every day that I get to write something, but in the future I’ll definitely catch up. In this article we move on to communicating with databases through PHP. PHP contains all the features for working with databases using MySQL software, and in this article we will learn connect to database via PHP.

There are several ways working with MySQL in PHP. All these methods appeared, then became obsolete, being replaced by new ones. And on this moment most last method is an object-oriented model for communicating with MySQL. It is with the use of this most modern method that we will work with databases.

Before moving on to connecting to a database in PHP, let's look at the algorithm for working with them:

1. Connection.
2. Sending requests and receiving results.
3. Closing the connection.

Connect to database via PHP can be done as follows:

$mysqli = new mysqli("localhost", "Admin", "pass", "mybase");
?>

Everything here is intuitive, however, let me explain: we are creating an instance of an object MySQLI, passing the following parameters to the constructor:

1. Hostname, which runs MySQL.
2. Username.
3. Password.
4. Database name, which we want to work with.

If any data is incorrect, then the constructor will return an error and there will be no connection.

However, there is one tricky point here. The fact is that if there is a connection error, the execution of the script will not be stopped. As a result, it will begin to continue executing our code. In most cases, if there is a connection error, you need to stop executing the script, so write this:

}
?>

IN in this example we check: if there were any errors during connection, we display them and finish executing the script (function exit()). Also note the error suppression operator " @ ", which we insert in order to remove the message PHP about the impossibility of connection, because we then check this ourselves and display a notification.

Let's do the third and last part of the database algorithm - closing connection. In the example below we connect to the database, and after checking for successful connection, close this connection:

$mysqli = @new mysqli("localhost", "Admin", "pass", "mybase");
if (mysqli_connect_errno()) (
echo "Connection failed: ".mysqli_connect_error();
}
$mysqli->close();
?>

As you guessed, the method closes the connection close().

Let me summarize: you and I have learned open and close database connections in PHP, and in the next article we will learn how to send requests and receive answers.