PHP with MySQL Connectivity – Detailed Tutorial (CRUD)

One of the defining features of PHP(Hypertext Preprocessor) is the versatility it offers for connection to, and manipulation with databases. In this article, we look at some features of the PHP with MySQL combination which includes:

Connect to MySQL DB Server (Testing of MySQL Connection)
Create new Database (New Database)
Select MySQL Database (Selection of Database)
Add data to table (Insertion)
Retrieve data (Show/Print Data)
Error Handling

1. Connect to MySQL Database Server

To work with the MySQL database, we first need to connect to MySQL Database Server. PHP provides mysql_connect() function to do this, and requires three strings as input: ‘hostname‘, ‘username‘ and ‘password‘.

Code 1: test.php (Here you don’t need to include DB name because we are just testing the mysql connection.)

<html>
<head>
<title>Server test Page </title>
</head>
<body>
Testing working of MySQL.

<?php
 /* Connecting to MySQL */
 $link = mysql_connect(“localhost”, “user”, “password”) or die(“Could not connect : ” . mysql_error());
 print “Connected successfully”;
?>

</body>
</html>

If your script has no error, then it gives output as:

Output 1: test.php

2. Create new Database

In the next example, we are creating a database user and under that, we are creating a table visitor. The table visitor will be created with four columns: a primary key called ‘id‘ that will be auto incremented as data is added to the table, and the remaining three columns are the character (VARCHAR) fields: ‘name’, ‘address’ and ’email’. This will help you to create the table successfully.

Code 2 :- db.php

<html>
<head>
<title>Create Database </title>
</head>
<body>
Creating Database & table in MySQL.

<?php
 // Connecting to MySQL
 $link = mysql_connect(“localhost”, “user”, “password”) or die(“Could not connect : ” . mysql_error());
 print “Connected successfully”;
 $DB = “yeahhub2”;
 $table = “visitor”;
 $query = “CREATE DATABASE $DB”;
 $result = mysql_query($query) or die(“ERROR while creating database”.mysql_error());
 print(“OK, database made, name of DB : $DB ”);
 mysql_select_db($DB, $link);
 $query2 = “CREATE TABLE $table (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(25), address varchar(50), email varchar(25))”;
 $result2 = mysql_query($query2) or die(“ERROR while creating table”.mysql_error());
 print(“OK, table made, name of table : visitor ”);
?>

</body>
</html>

If db.php runs without any error, it generates the output as:

Output 2: db.php

3. Select MySQL Database

Now, we have established the connection with MySQL, and creates database user and table visitor. To work with the database, it must select. To select any MySQL database, PHP provides the mysql_select_db() function. It requires the database name which is to be selected and the link to database.

Link is optional and if that is omitted, then the identifier returned from the last connection to server will be assumed as link.

Code 3: dbselect.php

<html>
<head>
<title>Server test Page </title>
</head>
<body>
Selecting MySQL Database.

<?php
 $link = mysql_connect(“localhost”, “user”, “password”) or die(“Could not connect : ” . mysql_error());
 print “Connected successfully”;
 $DB = “yeahhub2”;
 mysql_select_db($DB) or die (“Database $DB not select..” . mysql_error());
 print “Successfully select the Database: $DB “;
?>

</body>
</html>

The dbselect.php produces output as:

Output 3 :- dbselect.php

4. Adding data to table

To add data to table, we need to build and execute a SQL query. PHP provides the mysql_query() function for that purpose. mysql_query() requires two inputs: first, the SQL query and second, a link identifier. Identifier is optional. If omited, then query is sent to the database server to which you last connected.

mysql_query() returns true if the query executes successfully. If there are any syntax errors or if you do not have permission to access database, then it return false.

Code 4 :- adddata.php

<html>
<head>
<title>Server test Page </title>
</head>
<body>
Display data from MySQL Database.

<?php
 $link = mysql_connect(“localhost”, “user”, “password”) or die(“Could not connect : ” . mysql_error());
 print “Connected successfully”;
 $DB = “yeahhub2”;
 $table = “visitor”;
 mysql_select_db($DB) or die (“Database $DB not select..” . mysql_error());
 print “Successfully select the Database: $DB “;
 $query = “INSERT INTO $table(name,address,email) values(‘yeahhub’,’Chandigarh’,’yeahhub@gmail.com’)”;
 if ( ! mysql_query( $query, $link) )
 die ( “MySQL error…..” .mysql_error() );
 print “Successfully data added to table : $table”;
?>

</body>
</html>

The adddata.php produces output as shown:

Output 4: adddata.php

5. Retrieve data

The mysql_query() function of PHP also allow us to get data from table. The following code gives you an idea about that…

$result = mysql_query( “SELECT * FROM $table”);
$total_rows = mysql_num_rows( $result );

After perfoming SELECT operation using mysql_query() function, result is stored in the identifier $result. Now with the mysql_num_rows() function, we get the total number of rows of table ‘visitor’.

Now, to display data of table, we use the PHP function mysql_fetch_row() which gives us all data of the table. mysql_fetch_row() returns false when it finds end-of-data in the table. We use it with the while condition to display the content of table ‘visitor’.

Code 5: printdata.php

<html>
<head>
<title>Server test Page </title>
</head>
<body>
Display table content of MySQL Database.

<?php
 $link = mysql_connect(“localhost”, “user”, “password”) or die(“Could not connect : ” . mysql_error());
 print “Connected successfully”;
 $DB = “yeahhub2”;
 $table = “visitor”;
 mysql_select_db($DB) or die (“Database $DB not select..” . mysql_error());
 print “Successfully select the Database: $DB “;
 $result = mysql_query( “SELECT * FROM $table”);
 $total_rows = mysql_num_rows( $result );
 print “There are $total_rows in table : $table Table contents are: ”;
 print “<table border=1> \n”;
 while ( $pr_row = mysql_fetch_row( $result ) )
 {
 print “<tr>”;
 foreach ( $pr_row as $data )
 print “\t <td>$data</td>”;
 print “</td>\n”;
 }
 print “</table>\n”;
 mysql_close ( $link );
?>

</body>
</html>

After executing the code, we got output as:

6. Error Handling

When we are performing any operation on MySQL using PHP, if any error occurs, our script will not work properly. A single error can cause hundreds of lines of code to not work properly. Here, PHP provides some special functions to print more informative error messages to the browser to aid debugging.

MySQL gives an error message and an error number when an operation fails. PHP provides the function mysql_error() to print error message and mysql_errno() to print error number to browser, which becomes very useful while debugging the code.

You can easily get output for mysql_error() if any error occurs while executing code given in this article. You can just replace mysql_error() with mysql_errno() to get an error number in place of error message.

Here you can also check “Complete Htaccess Guide 2017” & To learn complete PHP, you can also check “Complete PHP Tutorial Series from Chapter 1 to Chapter 8“.

To download all above files, please click here.

Post Views: 582

You may also like: