Php file connect to mysql server

How to make a connection with MySQL server using PHP ?

MySQL is a widely used database management system that may be used to power a wide range of projects. One of its main selling features is its capacity to manage large amounts of data without breaking a sweat. There are two approaches that can be used to connect MySQL and PHP code, which are mentioned below.

Let’s get to know both of these options a bit more.

A Database Abstraction Layer is the PHP Data Objects (PDO) extension. It functions as a user interface for the backend to interact with the MySQL database and make changes without altering the PHP code. It also allows you to deal with numerous databases at the same time. The main benefit of using PDO is that it keeps your code simple and portable.

MySQLi is a connector function that connects the PHP app’s backend to the MySQL database. It performs in the same way as the previous version, but it is safer and faster, with a more comprehensive collection of functions and extensions. With PHP 5.0.0, MySQLi was launched, and the drivers were installed with PHP 5.3.0. The API was created to work with MySQL versions 4.1.13 and higher.

Connect with MySQL: Before one can access data in the MySQL database, one needs to be able to connect to the server. Use the mysqli_connect() function according to your connecting way to establish a database connection. This function returns a database connection pointer also known as a database handle. This handle will be utilized later in the code. Remember to add your database credentials after you get the handle.

1. Connection via PDO – A database is also supplied in the PDO example below (“geeksDatabase”). To connect to a database, PDO requires a valid database else an exception is thrown.

Источник

PHP Connect to MySQL

PHP 5 and later can work with a MySQL database using:

• MySQLi extension (the «i» stands for improved)
• PDO (PHP Data Objects)

Earlier versions of PHP used the MySQL extension. However, this extension was deprecated in 2012.

Should I Use MySQLi or PDO?

If you need a short answer, it would be «Whatever you like».

Both MySQLi and PDO have their advantages:

PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases.

So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code — queries included.

Both are object-oriented, but MySQLi also offers a procedural API.

Both support Prepared Statements. Prepared Statements protect from SQL injection, and are very important for web application security.

MySQL Examples in Both MySQLi and PDO Syntax

In this, and in the following chapters we demonstrate three ways of working with PHP and MySQL:

MySQLi Installation

For Linux and Windows: The MySQLi extension is automatically installed in most cases, when php5 mysql package is installed.

PDO Installation

Open a Connection to MySQL

Before we can access data in the MySQL database, we need to be able to connect to the server:

Example (MySQLi Object-Oriented)

$servername = «localhost»;
$username = «username»;
$password = «password»;

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>
echo «Connected successfully»;
?>

Note on the object-oriented example above:

$connect_error was broken until PHP 5.2.9 and 5.3.0. If you need to ensure compatibility with PHP versions prior to 5.2.9 and 5.3.0, use the following code instead:

// Check connection
if (mysqli_connect_error()) die(«Database connection failed: » . mysqli_connect_error());
>

Example (MySQLi Procedural)

$servername = «localhost»;
$username = «username»;
$password = «password»;

// Create connection
$conn = mysqli_connect($servername, $username, $password);

// Check connection
if (!$conn) die(«Connection failed: » . mysqli_connect_error());
>
echo «Connected successfully»;
?>

Example (PDO)

$servername = «localhost»;
$username = «username»;
$password = «password»;

try $conn = new PDO(«mysql:host=$servername;dbname=myDB», $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo «Connected successfully»;
> catch(PDOException $e) echo «Connection failed: » . $e->getMessage();
>
?>

Note: In the PDO example above we have also specified a database (myDB). PDO require a valid database to connect to. If no database is specified, an exception is thrown.

Tip: A great benefit of PDO is that it has an exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try < >block, the script stops executing and flows directly to the first catch() < >block.

Close the Connection

The connection will be closed automatically when the script ends. To close the connection before, use the following:

Источник

How to Connect to MySQL Using PHP

To access and add content to a MySQL database, you must first establish a connection between the database and a PHP script.

In this tutorial, learn how to use MySQLi Extension and PHP Data Objects to connect to MySQL. Traditional legacy mysql_ functions are deprecated and we will not cover them in this guide.

2 Ways to Connect to MySQL database using PHP

There are two popular ways to connect to a MySQL database using PHP:

The guide also includes explanations for the credentials used in the PHP scripts and potential errors you may come across using MySQLi and PDO.

Option 1: Connect to MySQL with MySQL Improved extension

MySQLi is an extension that only supports MySQL databases. It allows access to new functionalities found in MySQL systems (version 4.1. and above), providing both an object-oriented and procedural interface. It supports server-side prepared statements, but not client-side prepared statements.

The MySQLi extension is included PHP version 5 and newer.

The PHP script for connecting to a MySQL database using the MySQLi procedural approach is the following:

connect_error) < die("Connection failed: " . $conn->connect_error); > echo “Connected successfully”; mysqli_close($conn); ?>

Credentials Explained

The first part of the script is four variables (server name, database, username, and password) and their respective values. These values should correspond to your connection details.

Next is the main PHP function mysqli_connect(). It establishes a connection with the specified database.

Following is an “if statement.” It is the part of the code that shows whether the connection was established. When the connection fails, it gives the message Connection failed. The die function prints the message and then exits out of the script.

If the connection is successful, it displays “Connected successfully.”

When the script ends, the connection with the database also closes. If you want to end the code manually, use the mysqli_close function.

Option 2: Connect To MySQL With PDO

PHP Data Objects (PDO) is an extension that serves as an interface for connecting to databases. Unlike MySQLi, it can perform any database functions and is not limited to MySQL. It allows flexibility among databases and is more general than MySQL. PDO supports both server and client-side prepared statements.

Note: PDO will not run on PHP versions older than 5.0 and is included in PHP 5.1.

The PHP code for connecting to a MySQL database through the PDO extension is:

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo “Connection Okay”; return $pdo > catch (PDOException $e) < echo “Connection failed: ”. $e->getMessage(); > ?>

Credentials Syntax

First, we have five variables (server name, database, username, password, and charset) and their values. These values should correspond to your connection details.

The server name will be localhost. If connected to an online server, type in the server name of that server.

The variable charset tells the database in which encoding it will be receiving and sending data. The recommended standard is utf8mb4.

Try and Catch Blocks

PDO’s great asset is that it has an exception class to take care of any potential problems in database queries. It solves these problems by incorporating try and catch blocks.

If a problem arises while trying to connect, it stops running and attempts to catch and solve the issue. Catch blocks can be set to show error messages or run an alternative code.

The first parameter in the try and catch block is DSN, which stands for data(base) source name. It is crucial as it defines the type and name of the database, along with any other additional information.

In this example, we are using a MySQL database. However, PDO supports various types of databases. If you have a different database, replace that part of the syntax (mysql) with the database you are using.

Next is the PDO variable. This variable is going to establish a connection to the database. It has three parameters:

1. The data source name (dsn)
2. The username for your database
3. The password for your database

Following is the setAttribute method adding two parameters to the PDO:

This method instructs the PDO to run an exception in case a query fails.

Add the echo “Connection Okay.” to confirm a connection is established.

Return the PDO variable to connect to the database.

After returning the PDO variable, define the PDOException in the catch block by instructing it to display a message when the connection fails.

Potential Errors with MySQLi and PDO

Incorrect Password

The password in the PHP code needs to correspond with the one in the database. If the two do not match, a connection with the database cannot be established. You will receive an error message saying the connection has failed.

Possible solutions:

1. Check the database details to ensure the password is correct.
2. Ensure there is a user assigned to the database.

Unable to Connect to MySQL Server

PHP may not be able to connect to the MySQL server if the server name is not recognized. Make sure that the server name is set to localhost.

In case of other errors, make sure to consult the error_log file to help when trying to solve any issues. The file is located in the same folder where the script is running.

This guide detailed two ways to connect to a MySQL database using PHP.

Both MySQLi and PDO have their advantages. However, bear in mind that MySQLi is only used for MySQL databases. Therefore, if you want to change to another database, you will have to rewrite the entire code. On the other hand, PDO works with 12 different databases, which makes the migration much easier.

Источник