How to connect to the database via php. MySQL and PHP: Class for creating a database connection. A more progressive procedural style - connecting to the database using mysqli

The question is quite popular among beginners and it would be wrong not to devote a separate article to this. How to create a database connection using PHP? I’ll say right away that PHP tools are quite sufficient for full-fledged work with databases and interaction with MySQL. Now let's try to connect!

What do you need to connect PHP to MySQL?

If you do not complete one of these steps, you will not be able to connect.

Algorithm for interaction between PHP and MySQL

Now we connect to the database using PHP:

PHP code

$db = mysql_connect("localhost", "Admin", "pass"); // user data
mysql_select_db("baseName",$db); // select which database to connect to
?>
As you can see, four parameters are specified for connection:

1. Hostname. In many cases, it is enough to specify localhost (on the same hosting).
2. The name of the user you registered to use MySQL.
3. The password of the user you registered to use MySQL.
4. The name of the Database we want to connect to. Accordingly, the user we specified must have rights to work in this database.

As a result, if something is indicated incorrectly, then nothing criminal will happen, but most likely you will see an error. But let's be optimistic, let's say you entered everything correctly.

Another type of connection:

PHP code

$host="localhost"; /*host*/
$user="admin"; /*Username*/
$password="12345"; /*User password*/
$db="baseName"; /*Database name*/

Mysql_connect($host, $user, $password); /*Connect to server*/
mysql_select_db($db); /*Connect to a database on the server*/
?>
Here I visually create variables with example data for the user and host, and then create a connection.

How to interrupt (close) a connection to a database in PHP?

PHP code

$user="admin";
$password="12345";
$db="baseName";

// if an error occurred
mysql_connect($host, $user, $password) or die("MySQL server is unavailable!".mysql_error());
mysql_select_db($db) or die("No connection to the database."mysql_error());
?>
It is also very important that the development of the scenario will stop, which will save you from a lot of incorrect data.

Congratulations! Now you know how to connect to a database in PHP and close the connection!

Thank you for your attention!

Using php...

Creating a database connection in PHP in different ways:

1) the old-fashioned way of connecting to MySQL:

$conn=mysql_connect($db_hostname, $db_username, $db_password) or die ("No connection to the server");
mysql_select_db($db_database,$conn) or die ("No, it was not possible to connect to the database");

Explanations of the variables below.

The following functions are used:

• mysql_connect()- to connect to the server;
• mysql_select_db()- to connect to the database;

At the same time, we constantly check for errors in this way: or die (“The error is such and such”); - translated as or die with such and such an error - to immediately find where the error is.

config.php

// variables for connecting to the database
$host = "localhost"; /host
$username = "root"; // password for connecting to the database
$password = ""; // password for connecting to the database - on local computer it may have an empty value.
$database_name = "my-dolgi"; // database name

// old way database connections
mysql_connect($host, $username, $password) or die("Can't connect create connection");

// select the database. If there is an error, output
mysql_select_db($database_name) or die(mysql_error());

index.php

require_once "config.php";

$result = mysql_query("SELECT Name, Money FROM Dolg ORDER BY Money DESC LIMIT 5") or die(mysql_error());

";

while ($row = mysql_fetch_assoc($result)) (
";
}

mysql_free_result($result);

// Close the connection
mysql_close();

2) A more progressive procedural style - connecting to the database using mysqli:

This method:

1. convenient;
2. up to 40 times faster;
3. increased security;
4. there are new features and functions;

An example of connecting to a database in PHP with a selection from a table

config.php

// connections to the database
$link = mysqli_connect("localhost", "username", "password", "name-database"); // here we enter your data directly: user name, password and database name, the first field is usually localhost

// output connection error
if (!$link) (
echo "Error connecting to the database. Error code: " . mysqli_connect_error();
exit;
}

Please note - mysqli is used everywhere, not mysql!!!

index.php

require_once "config.php";

// Execute the request. If there is an error, we display it
if ($result = mysqli_query($link,"SELECT Name, Money FROM Debt ORDER BY Money DESC LIMIT 5")) (

Echo "To whom do I owe in descending order:

";

// Fetching query results
while ($row = mysqli_fetch_assoc($result)) (
echo $row["Name"] . "with debt". $row["Money"] . " rubles.
";
}

// freeing used memory
mysqli_free_result($result);

// Close the connection
mysqli_close($link);
}

As you can see, some points have changed (in italics).

3) Object-oriented method of connecting to a MySQL database - using methods and classes:

Cons: More complex and less susceptible to errors.

Pros: brevity and convenience for experienced programmers.

$conn = new mysqli($db_hostname, $db_username, $db_password, $db_database);
if($conn->connect_errno)(
die($conn->connect_error);
) else (echo "The connection to the database was successfully established";)

here, in principle, everything is intuitive:

• $db_hostname is host(mostly localhost),
• $db_database - db name;
• $db_username and $db_password - username and password respectively!

An example of connecting to a database in php OOP style with sampling from a table

config.php

// connections to the database
$mysqli = new mysqli("localhost", "username", "password", "name-database"); // here we enter your data directly: user name, password and database name, the first field is usually localhost

// output connection error
if ($mysqli->connect_error) (
die ("DB connection error: (" . $mysqli->connect_errno . ") " . mysqli_connect_error) ;
}

Please note - mysqli is used everywhere, not mysql!!! and unlike the previous method, arrows “->” appear, which indicate that this is an OOP style.

index.php

require_once "config.php";

// Execute the request. If there is an error, we display it
if ($result = $ mysqli->query("SELECT Name, Money FROM Debt ORDER BY Money DESC LIMIT 5")) (

Echo "To whom do I owe in descending order:

";

// Fetching query results
while ($row = $result-> fetch_assoc()) {
echo $row["Name"] . "with debt". $row["Money"] . " rubles.
";
}

// freeing used memory
$result->close();

// Close the connection
$mysqli->close();
}

Your task is to find the differences.

4) Communication with the database using PDO:

When connecting to the base MySQL data prepared expressions are used (using the prepare method) and as a result there is greater security and greatly increases performance.

config file from the previous method! - same

index.php

// PDO style for communication with MySQL
if ($stmt = $mysqli->prepare("SELECT Name, Voney FROM Dolg ORDER BY Money< ? LIMIT 5")) {

$stmt->bind_param("i", $summa);
$summa = 100000;

//start execution
$stmt->execute();

// Declaring variables for prepared values
$stmt->bind_result($col1, $col2);

Echo "To whom do I owe in descending order:

";

// Fetching query results
while ($stmt->fetch()) (
echo $col1 . "with debt". $col2 . " rubles.
";
}

// freeing used memory
$stmt->close();

// Close the connection
$mysqli->close();

As you can see, it’s much more complicated here and you need to study PDO - this is a separate topic.

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 the most recent way is the object-oriented model of communication 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 perform 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.

3 methods to connect to MySQL with PHP with code examples

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

This article describes the following three methods, along with corresponding PHP code examples that explain how to connect to your database from PHP.

For all the examples below, we will be connecting to an existing MySQL database. Note: Everything explained here will also work with MariaDB, just like MySQL.

1. Connecting to PHP using the mysqli extension
*mysqli means MySQL Improved

Create the following mysqli.php file

connect_error) ( die("Error: unable to connect: " . $conn->connect_error); ) echo "Connected to the database.
"; $result = $conn->query("SELECT id FROM goroda"); echo "Number of rows: $result->num_rows"; $result->close(); $conn->close(); ?> In the above code:

• mysqli - This function initiates a new connection using the mysqli extension. The function takes four arguments:
  1. localhost is the name of the host on which the MySQL database is running
  2. name - MySQL username to connect
  3. pass - password for the mysql user
  4. db - MySQL database to connect to.
• qvery is a MySQL query function. In this example, we select the id column from the city database.
• Finally, we show the number of rows selected using the num_rows variable in the result. We also close both the result and the connection variable as shown above.

Connected to the database. Number of lines: 6 2. Connection from PHP MySQL PDO Extension
*PDO stands for PHP Data Objects

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

Create the following mysql-pdo.php file:

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected to the database.
"; $sql = "SELECT id FROM goroda"; print "List of id:
"; ) $conn = null; ) catch(PDOException $err) ( echo "Error: Unable to connect: " . $err->getMessage(); ) ?> In the above:

• new PDO - will create a new PDO object that will take the following three arguments:
  1. mysql connect string: it will be in the format “mysql:host=localhost;dbname=db”. In the above example the db is running on localhost and we are connecting to the db database.
  2. MySQL username to connect
  3. Mysql user password
• $sql variable - create the sql query you want to execute. In this example, we select the id column from the cities table.
• query($sql). Here we are executing the sql query we just created.
• foreach. Here we loop through the result from the above query command and store it in $row variable and then output it using echo.
• In MySQL PDO, to close a connection, simply set the $conn variable to null.

Connected to the database. List id: 1 2 3 4 5 6 3. Connection from PHP using outdated mysql functions

Use this method only if you are using an older one PHP version and for some reason you can't update it to new version. It is recommended to use method #2 and method #3 shown above instead of this method. I have included this method for reference only and not as a recommendation for use.

This particular extension has been deprecated since PHP 5.5. But as of PHP 7.0 this won't even work since it was removed. Since PHP 5.5, when you use these functions, it will generate an E_DEPRECATED error.

Create a mysql.php file:

"; $result = mysql_query("SELECT id FROM goroda"); $row = mysql_fetch_row($result); echo "id 1: ", $row, "
\n"; mysql_close($conn); ?> In the above:

• The mysql_connect function takes three arguments:
  1. the name of the host where the MySQL database is running;
  2. MySQL username to connect;
  3. password for mysql user. Here it connects to the MySQL database which is running on local server using the username and password.
• mysql_select_db function. As the name suggests, it selects the database you want to connect to. Equivalent to the "use" command. In this example we are connecting to a db database.
• mysql_query function - used to specify your MySQL query. In this example, we select the id column from the city database.
mysql_fetch_row. Use this function to extract rows from the SQL query we just created.
• Finally, close the connection using the mysql_close command as shown above.

Connected to the database. id 1: 1 This is how you can connect to MySQL. I repeat, it is better to use the first two methods; O

In order to receive maximum return from your MySQL database, it is important to understand how to connect from a custom PHP program 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 to install PHP-MySQL 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 are 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 remote server MySQL.

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.

On most Linux distributions (such as 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"] . "

In the above:

• "; ) $conn = null; ) catch(PDOException $err) ( echo "Error: Unable to connect: " . $err->getMessage(); ) ?>
  1. new PDO – Will create a new PDO object that will take the following three arguments:
  2. 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.
  3. Username to connect to MySQL.
• 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.
• 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 more old version PHP and can't update it to the 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 is running on the local server, using the root username and its 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