How to Connect to the MySQL Database
What is MySQL?
MySQL is one of the most popular Open Source SQL database management systems which is developed and distributed as well as supported by Oracle Corporation.
MySQL is a data management system:
Database can be defined as the collection of data. Database could be anything which can be from a simple shopping list to a picture gallery, a database can also be a vast collection of data. A database management system is required for adding, accessing as well as processing the data stored in a computer database. We might be knowing that computers are one of the best equipment to store large amounts of data, hence database management plays an important role in computing. Even we can say that dedicated servers are a form of the computer where a large amount of data is stored.
MySQL is relational:
Here in MySQL the data is stored in separate tables rather than storing it in one single storeroom. Hence the database is structured in way to optimize it for speed. Hence this type of structured model gives a flexible environment. The best part is that you can set up the rules governing the relationship between different data fields.
My SQL is Open Source
Open Source can be defined as the way by which the user can modify the software. Hence MySQL can be modified by downloading it through the internet, one can study the source code and change it according to its needs.
MySQL Server is faster, scalable, reliable, and easy to use
MySQL is easy to run on a desktop or laptop along with the applications. If an entire machine is dedicated to MySQL, one can adjust the settings so that you can utilize the memory, I/O capacity, and CPU power. MYSQL Server is offering a rich and useful set of functions which makes it best suited for accessing databases on the internet.
MySQL works in embedded systems
The MySQL Database Software consists of a multi-threaded SQL server, which supports different types of back ends, administrative tools, and a wide range of applications.
Contributed MySQL software is available
MySQL Server has features developed with close cooperation for users. Most of the applications or languages across the web support the MySQL Database Server.
If you do not know have MySQL database and MySQL user, refer to the following links to create the MySQL database and MySQL user,
Cpanel
https://help.hostingraja.in/error-troubleshoot/how-to-set-up-a-database-using-the-mysql-database-wizard
Plesk
https://help.hostingraja.in/how-tos/how-to-create-a-mysql-mssql-database-in-plesk
Ovipanel
https://www.ovipanel.in/tutorials/database/how-to-create-mysql-database
https://www.ovipanel.in/tutorials/database/mysql-database
Below are the three methods we can connect the MySQL using PHP
1. MySQLi Procedure
Local Mysql Connection
$db_username = 'username';
$db_password = 'password';
$db_name = 'database';
$connect = mysqli_connect($db_server, $db_username, $db_password, $db_name);
if (!$connect) {
die("Connection failed: " . mysqli_connect_error());
}
...
...
mysqli_close($conn);
Remote Mysql Connection
$db_username = 'username';
$db_password = 'password';
$db_name = 'database';
$db_port = 'port';
$connect = mysqli_connect("$db_server:$db_port", $db_username, $db_password, $db_name);
if (!$connect) {
die("Connection failed: " . mysqli_connect_error());
}
....
....
mysqli_close($connect);
2. PDO
Local Mysql Connection
$db_username = 'username';
$db_password = 'password';
$db_name = 'database';
try {
$connect= new PDO("mysql:host=$db_server;dbname=$db_name", $db_username, $db_password);
$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
....
...
$connect = null;
Remote Mysql Connection
$db_username = 'username';
$db_password = 'password';
$db_name = 'database';
$db_port = 'port';
try {
$connect = new PDO("mysql:host=$db_server:$db_port;dbname=$db_name", $db_username, $db_password);
$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
....
...
$connect = null;
3. MySQLi Object-Oriented
Local Mysql Connection
$db_username = 'username';
$db_password = 'password';
$db_name = 'database';
$connect = new mysqli($db_server, $db_username, $db_password, $db_name);
if ($connect->connect_error) {
die("Connection failed: " . $connect->connect_error);
}
....
....
$connect->close();
Remote Mysql Connection
$db_username = 'username';
$db_password = 'password';
$db_name = 'database';
$db_port = 'port';
$connect = new mysqli("$db_server:$db_port", $db_username, $db_password, $db_name);
if ($connect->connect_error) {
die("Connection failed: " . $connect->connect_error);
}
.....
.....
$connect->close();
Based on the CMS / Frameworks we can connect the MySQL varies method,
WordPress CMS
Locate your wp-config.php file under the public_html folder,
Local Mysql Connection
define('DB_USER', 'username');
define('DB_PASSWORD', password');
define('DB_NAME', 'database');
Remote Mysql Connection
define('DB_USER', 'username');
define('DB_PASSWORD', password');
define('DB_NAME', 'database');
Larvel Frameworks
Locate your app/config/database.php file under the public_html folder,
Local Mysql Connection
'read' => array(
'host' => 'locahost',
),
'write' => array(
'host' => 'locahost'
),
'driver' => 'mysql',
'database' => 'database',
'username' => 'username',
'password' => 'password',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
Remote Mysql Connection
'mysql' => array(
'read' => array(
'host' => env('DB_HOST', 'server_ip'),
'port' => env('DB_PORT', '3306'),
),
'write' => array(
'host' => env('DB_HOST', 'server_ip'),
'port' => env('DB_PORT', '3306'),
),
'driver' => 'mysql',
'database' => env('DB_DATABASE', 'database'),
'username' => env('DB_USERNAME', 'username'),
'password' => env('DB_PASSWORD', 'password'),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
There are three ways to connect PHP to MySQL
i) MySQL
ii) MySQLi
iii) PDO
i) MySQL
mysql_connect() function is helpful to open the connection in MySQL. Earlier PHP versions only used this function. Till 5.6 PHP version, this function working fine but PHP 7 version that function was deprecated due to security issues
<?php
$connection = mysql_connect('localhost', 'mysqluser', 'mysqlpassword');
if (!$connection ) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($connection );
?>
Update mysqluser, mysqlpassword which was created by our end using ovipanel.
ii) MySQLi
MySQLi is known as a MySQL improved extension. compared to MySQL, MySQLi has a lot of enhancements like object-oriented interface, support for Multiple Statements & prepared statements, transaction, and so on. This function will be available from PHP5
Object oriented style:
<?php
$connection = new mysqli("localhost","mysqluser","mysqlpassword","mysqldatabase");
if ($connection -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
?>
Procedural style:
<?php
$connection = mysqli_connect("localhost","mysqluser","mysqlpassword","mysqldatabase");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit();
}
?>
Update mysqluser, mysqlpassword, mysqldatabase which was created by our end using ovipanel.
iii) PDO
PHP Data Objects are helpful to connect the MySQL database via PHP. PDO enabled by default.
<?php
$servername = "localhost";
$username = "mysqluser";
$password = "mysqlpassword";
try {
$connection = new PDO("mysql:host=$servername;dbname=mysqldatabase", $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();
}
?>
Update mysqluser, mysqlpassword, mysqldatabase which was created by our end using ovipanel.