Connect with MySQL Database Using PHP

PHP is a general purpose scripting language that is best suited for web development. PHP is very fast and flexible and powers most popular websites of the world. PHP stands for PHP Hypertext Preprocessor and most web developers create dynamic content that interacts with databases through PHP.

A database is an organized collection of data so that a computer program can easily gain access to the desired piece of information. Traditional databases are organized using the concept of a field, record and files where a set of fields makes the records and set of records makes a file. Note that we are going to use PHP to connect to the MySQL database.

First, we are going to open a connection with a database and after opening the database, we can perform different functions such as adding, deleting, updating and altering records.

Learn PHP programming and MySQL from scratch with an online course at Udemy.com

How to open a connection with MySQL database:

PHP has numerous different built-in functions for manipulating the database. The following are the built-in functions you’ll commonly come across when working with PHP and MySQL:

mysql_connect()
mysql_select_db()
mysql_close()

1)      Open the MySQL database connection:

As mentioned, you need to connect to the database. The function mysql_connect(), as the name suggests, is used to connect to a MySQL database.

<?PHP
$user_name = "root";
$password = "";
$database = "database123";
$host_name = "localhost";
mysql_connect($host_name, $user_name, $password);
echo ‘Connection opened’;
?>

In the first four lines of the code shown above, you are only setting variables. The first variable is $user_name while the second variable is $password with values root and blank respectively. These are the default values for MySQL. The other variables are $database that contains the name of the database you want to connect with and $host_name that contains the name of the host.

mysql_connect($host_name, $user_name, $password);

The above line is used to connect with the database and generally contains three parameters including the name of the host, database username and database password that you just set previously with your variables.

If you don’t want to use variables, then you can directly use the values as shown below:

mysql_connect(‘local’,’root’,’’);

2)      Specifying the database you want to open:

In the code shown above, you declared the variable $database that contains the name of the database. Let’s use this variable here by adding one more line to our previous code.

<?PHP
$user_name = "root";
$password = "";
$database = "database123";
$host_name = "localhost";
mysql_connect($host_name, $user_name, $password);
mysql_select_db($database);
echo ‘Connection opened’;
?>

mysql_select_db() is an built-in PHP function used to select the MySQL database to which we want to connect. mysql_select_db() then returns true if the database you’ve entered is found and returns false if database is not found.

Learn more about PHP Programming and MySQL by taking a course at Udemy.com

You can also use variables to assign the values of mysql_select_db() and mysql_connect that makes the coding easier as shown below. Here, you can test some logic to check if the database is found or not.

<?PHP
$user_name = "root";
$password = "";
$database = "database123";
$host_name = "localhost";
$connect_db=mysql_connect($host_name, $user_name, $password);
$find_db=mysql_select_db($database);
if ($find_db) {
echo "Database exist";
}
else {
echo "Database does not exist";
}
?>

What happens if you try to insert the name of the database that doesn’t exist? Instead of $database=database123 you can use $database=database789 that does not exist.

<?PHP
$user_name = "root";
$password = "";
$database = "database789";
$host_name = "localhost";
$connect_db=mysql_connect($host_name, $user_name, $password);
$find_db=mysql_select_db($database);
if ($find_db) {
echo "Database exist";
}
else {
echo "Database does not exist";
}
?>

Note that the value of the database is assigned as “database789” in the code shown above. When this code runs, it displays “Database does not exist”.

There is another option for mysql_select_db() called the link identifier. An example is shown below:

<?PHP
$user_name = "root";
$password = "";
$database = "database123";
$host_name = "localhost";
$connect_db=mysql_connect($host_name, $user_name, $password);
$find_db=mysql_select_db($database, $connect_db);
if ($find_db) {
echo "Database exist" .$connect_db;
}
else {
echo "Database does not exist" .$connect_db;
}
?>

In the line below the value from mysql_connect() is returned to variable $connect_db.

$connect_db=mysql_connect($host_name, $user_name, $password);

When PHP tries to connect with the database, you can use the file handle shown below:

$find_db=mysql_select_db($database, $connect_db);

The resource link identifier will find the database you want to open and the file handle can be used to refer to the database connection.

How to close the connection:

In PHP, mysql_close() is used for closing the non-persistent connection to MySQL server that is associated with the specified link identifier. You can use the following code to close your connection:

mysql_close($connect_db)

Usually, it’s not necessary to use mysql_close() since non-persistent links automatically close at the end script execution.

It can be used as shown below:

<?PHP
$user_name = "root";
$password = "";
$database = "database123";
$host_name = "localhost";
$connect_db=mysql_connect($host_name, $user_name, $password);
$find_db=mysql_select_db($database);
if ($find_db) {
echo "Database exist";
mysql_close($connect_db);
}
else {
echo "Database does not exist";
}
?>

Example:

Now that you know how to connect with the database and then close it, let’s analyze an example that shows how to retrieve data or how to read data from MySQL database using PHP.

<?PHP
$user_name = "root";
$password = "";
$database = "database123";
$host_name = "localhost";
$connect_db=mysql_connect($host_name, $user_name, $password);
$find_db=mysql_select_db($database);
if ($find_db) {
$query = "SELECT * FROM employee”;
$result = mysql_query($query);
while ( $field_db = mysql_fetch_assoc($result) ) {
echo$field_db['emp_ID'] . "<BR>";
echo $field_db['emp_FirstName'] . "<BR>";
echo $field_db['emp_lastname'] . "<BR>";
echo $field_db['emp_Address'] . "<BR>";
}
mysql_close($connect_db);
}
else {
echo "Database does not exist";
mysql_close($connect_db);
}
?>

Note that the initial four lines display only declared variables and you make the connection with the database later. Now, when the “if” condition is checked, the query “SELECT * FROM employee;” will run using the mysql_query() function and the result is stored in the variable $result. After this, the “while” loop’s condition containing the function mysql_fetch_assoc() is checked. mysql_fetch_assoc() is used to return an associative array that corresponds to a fetched row and an internal data pointer is moved ahead. The variable $field_db contains all the data returned from mysql_query($query).

echo$ field_db['emp_ID'] . "<BR>";
echo $field_db['emp_FirstName'] . "<BR>";
echo $field_db['emp_lastname'] . "<BR>";
echo $field_db['emp_Address'] . "<BR>";

These lines are used for printing the result where ‘emp_ID’,’emp_Firstname’,’emp_lastname’ and ‘emp_Address’ are the names of the employee table’s columns.

Take a course at Udemy.com and get a better understanding of the PHP and MySQL programming language.

Conclusion:

MySQL is an open-source relational database system and PHP is one of the most famous general purpose scripting language. The combination of both MySQL and PHP makes it easier to connect with your database and provides the flexibility to perform different operations.