Connect PHP with MySQL

Connect PHP with MySQL database, execute queries to manipulate information and retrieve data and display it using MySQL. Connecting with a MySQL database using PHP requires the use of three functions mysql_connect() mysql_select_db() and mysql_query() In this article I’ll be explaining how to connect to a MySQL server, create a database, create a table, insert values into it and retrieve it to display it in the form of HTML tables.Warning:- Some example source code might contain a die(mysql_error()) statement, if you are planning to use your web application in a production environment please REMOVE that statement from the code after development and debugging is over. It might expose your database credentials.

PHP connect to a MySQL server

The first objective is to connect to a MySQL server. So use the mysql_connect() function. The syntax is

mysql_connect("MySQL Host","Username","Password");

In most situations the MySQL host is localhost, if there is no password for your server (which is the case if you’re using WAMP or XAMPP) just use two empty double quotes WITHOUT any space in between them. You might want to use a die() function to display MySQL errors if any

mysql_connect("MySQL Host","Username","Password") or die(mysql_error());

Create a MySQL database

The second step is to create a MySQL database using PHP. In this step the mysql_query() function will be used to execute the query to create a database. So this function will be used along with the mysql_connect() function

mysql_connect("MySQL Host","Username","Password");
mysql_query("CREATE DATABASE database_name");

Now if you run this script you’ll be presented with a blank page so lets use if else statement to check whether the operation was successful

mysql_connect("MySQL Host","Username","Password");
$create_db = mysql_query("CREATE DATABASE database_name");
if ($create_db)
print "database was successfully created";
else
print mysql_error();
mysql_close();

If the database was created you see the message inside the if loop, if you refresh the page once again you’ll see an error message “Can’t create database ‘database_name’; database exists”

Connect to the MySQL database

Do not confuse with the term connect, the mysql_connect() function connects to MySQL server but to establish a connection with a database the mysql_select_db() function is used. Add the function along with mysql_connect() function

mysql_connect("MySQL Host","Username","Password");
$selectdb = mysql_select_db("database_name");
if ($selectdb)
print "Connection was successful";
else
print mysql_error();
mysql_close();

This will display a message to show whether connection was established correctly.

Create a Table

After creating a database it is now time to create a table. Creating a table is just a matter of executing SQL queries using the mysql_query(). For this example I’ll be creating a table containing fields(columns) employee name and employee id.

mysql_connect("MySQL Host","Username","Password");
mysql_select_db("database_name");
$create_table = mysql_query("CREATE TABLE employees (employee_name VARCHAR(30) NOT NULL, employee_id int(6) NOT NULL, PRIMARY KEY (employee_id))");
if($create_table)
print "Table was successfully created";
else
print mysql_error();
mysql_close();

If the table was successfully created you’ll see a confirmation else you’ll get a error message. Here PRIMARY KEY is used to ensure no identical employee Ids are inserted.

Insert data into the table

After creating a table records need to be added so the insert query will be used tinside the mysql_query() function. The HTML input form I’m using will have the text box names as eid and ename for employee ID and employee name respectively.

$id=stripslashes(trim($_POST['eid']));
$employee=stripslashes(trim($_POST['ename']));
mysql_connect("MySQL Host","Username","Password");
mysql_select_db("database_name");
$add=mysql_query("INSERT INTO employees(employee_id,employee_name) values('$id','$employee')");
if($add)
print "User successfully added";
else
print mysql_error();
mysql_close();

If you’re wondering what the two new functions stripslashes() and trim() are for, they are there to prevent SQL injection attacks. The stripslashes() function removes any quote characters entered in the text box and the trim() function removes whitespace from the starting and end of the string.

Retrieve data from the table

The final operation would be to display the data in the table in an user friendly format. Lets use the select query for this purpose.

mysql_connect("MySQL Host","Username","Password");
mysql_select_db("database_name");
$data=mysql_query("SELECT * FROM employees");
print "
<table>
<tr>
<th>Employee ID</th>
<th>Employee Name</th>
</tr>";
while($value=mysql_fetch_array($data))
{
print "
<tr>
<td>".$value['employee_id']."</td>
<td>".$value['employee_name']."</td>
</tr>";
}
print "</table>";
mysql_close();

The function mysql_fetch_array() will return a multidimensional array containing values from the select query. The values are displayed using a HTML table in the front-end.