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.

Important points to remember on MySQL:

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/docs/how-to-set-up-a-database-using-the-mysql-database-wizard

Plesk

https://help.hostingraja.in/docs/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_server = 'locahost';
$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_server = 'server_ip';
$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_server = 'locahost';<br />$db_username = 'username';<br />$db_password = 'password';<br />$db_name = 'database';<br /><br />try {$connect= new PDO("mysql:host=$db_server;dbname=$db_name", $db_username, $db_password), 
$connect-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo "Connected successfully";<br />} catch(PDOException $e) {<br />echo "Connection failed: " . $e-&gt;getMessage();<br />}<br />....<br />...<br />$connect = null;

Remote Mysql Connection

$db_server = 'server_ip';<br />$db_username = 'username';<br />$db_password = 'password';<br />$db_name = 'database';<br />$db_port = 'port';<br /><br />try {<br />$connect = new PDO("mysql:host=$db_server:$db_port;dbname=$db_name", $db_username, $db_password);<br />$connect-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);<br />echo "Connected successfully";<br />} catch(PDOException $e) {<br />echo "Connection failed: " . $e-&gt;getMessage();<br />}<br />....<br />...<br />$connect = null;

3. MySQLi Object-Oriented

Local Mysql Connection

$db_server = 'locahost';<br />$db_username = 'username';<br />$db_password = 'password';<br />$db_name = 'database';<br /><br />$connect = new mysqli($db_server, $db_username, $db_password, $db_name);<br /><br />if ($connect-&gt;connect_error) {<br /> `die("Connection failed: " . $connect-&gt;connect_error)`;<br />}<br />....<br />....<br />$connect-&gt;close();</div>

Remote Mysql Connection

$db_server = 'server_ip';<br />$db_username = 'username';<br />$db_password = 'password';<br />$db_name = 'database';<br />$db_port = 'port';<br /><br />$connect = new mysqli("$db_server:$db_port", $db_username, $db_password, $db_name);<br /><br />if ($connect-&gt;connect_error) {<br /> `die("Connection failed: " . $connect-&gt;connect_error)`;<br />}<br />.....<br />.....<br />$connect-&gt;close();</div>

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_HOST', 'locahost');<br />define('DB_USER', 'username');<br />define('DB_PASSWORD', password');<br />define('DB_NAME', 'database');</div>

Remote Mysql Connection

define('DB_HOST', 'server_ip:port');<br />define('DB_USER', 'username');<br />define('DB_PASSWORD', password');<br />define('DB_NAME', 'database');</div>

Larvel Frameworks

Locate your app/config/database.php file under the public_html folder,

Local Mysql Connection

'mysql' =&gt; array(<br /> 'read' =&gt; array(<br /> 'host' =&gt; 'locahost',<br /> ),<br /> 'write' =&gt; array(<br /> 'host' =&gt; 'locahost'<br /> ),<br /> 'driver' =&gt; 'mysql',<br /> 'database' =&gt; 'database',<br /> 'username' =&gt; 'username',<br /> 'password' =&gt; 'password',<br /> 'charset' =&gt; 'utf8',<br /> 'collation' =&gt; 'utf8_unicode_ci',<br /> 'prefix' =&gt; '',<br />),</div>

Remote Mysql Connection

'mysql' =&gt; array(<br /> 'read' =&gt; array(<br /> 'host' =&gt; env('DB_HOST', 'server_ip'),<br /> 'port' =&gt; env('DB_PORT', '3306'),<br /> ),<br /> 'write' =&gt; array(<br /> 'host' =&gt; env('DB_HOST', 'server_ip'),<br /> 'port' =&gt; env('DB_PORT', '3306'),<br /> ),<br /> 'driver' =&gt; 'mysql',<br /> 'database' =&gt; env('DB_DATABASE', 'database'),<br /> 'username' =&gt; env('DB_USERNAME', 'username'),<br /> 'password' =&gt; env('DB_PASSWORD', 'password'),<br /> 'charset' =&gt; 'utf8',<br /> 'collation' =&gt; 'utf8_unicode_ci',<br /> 'prefix' =&gt; '',<br />),<br /><br /><br /><br /></p>

How to connect DB in MySQL (OviPanel)

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.