Home » PHP » PHP Connect To Mysql Tutorial

About Olayemi Odunayo

Olayemi Odunayo
I am a programmer and web developer, who has experience in developing websites and writing desktop and mobile applications. I have worked with both schematic and schemaless databases. I am also familiar with third party API and working with cloud servers. I do programming on the client side with Java, JavaScript, html, Ajax and CSS while I use PHP for server side programming.

PHP Connect To Mysql Tutorial

In this example we will learn about database systems, mysql and how PHP scripts can talk to a Mysql database.
For this example we will use:

  • A computer with PHP >= 5.5 installed
  • Mysql server
  • notepad++
  • Phpmyadmin: This is not compulsory. (All the codes in this example will work correctly even without it). If you want a graphical user interface for manipulating your datbase, then you should install it.

 

1. Getting Started

If you have ever uploaded a picture to instagram, viewed a twitter user timeline, uploaded a post to facebook or even used your mobile browser to surf the internet (some mobile browser store users history item in a database), you have indirectly connected to a database.
Most of these services store your data in a database, so they are readily available when you log off, login from another device or generally when you need them.

A database is simply an organized collection of data. In the simplest terms, a relational database is one that presents information in tables with rows and columns. Mysql database is a relational database. Mysql is actually pronounced My Ess Que Ell, not My Sequel.

Mysql is a free and open source database system and arguably the most popularly used database among web apps. Mysql is very popular with PHP developers. You can download mysql and install it from its website, but it is usually more convenient to download xampp or wamp ( it comes with other useful tools ). To learn how to install wamp or xampp and read about great PHP tools visit PHP Web Development Tools List

1.1 PDO AND MSQLI

From PHP 5 upwards, PHP can connect or talk to a MySQL database using:

  • MySQLi extension (the “i” stands for improved)
  • PDO (PHP Data Objects)

Earlier versions of PHP used the MySQL extension. However, this extension was deprecated in 2012. At the basic level both PDO and MySQLi do the same thing – talk to a database – the difference between those two, lies in the method of communication with the database. You can use them to update, delete, insert create and perform other operations on the database.

Mysqli improves on mysql by adding parameterized queries and a few other things into the mix ( you can do more with mysqli than you can do with mysql ). In other words mysqli is more advanced than mysql.

PDO is an extension that abstracts several database drivers into one package. PDO will work on 12 different database systems i.e PDO will work with MySQL, Oracle, MS SQL Server and a number of other databases without needing to use database specific drivers or rewrite your code when you switch databases while mysqli will work with only Mysql(so you will need to re-write your database manipulation class or code if you switch or change database ).

Both mysqli and PDO are object oriented, but mysqli provides procedural API. Mysqli and PDO support Prepared Statements. Prepared Statements protect web app and in extension app users from SQL injection, and they are very important for web application security (SQL Injection can compromise a database which can lead to severe consequences if not properly checked).

1.2 PDO Connection Example

To further explain php – mysql – connection we are going to develop a trivial web app that connects to a database and mimmics user registration. We are going to connect to our database with both PDO and mysqli.

index.php

 
<?php
<!DOCTYPE html> 
 <html lang="en"> 
 <head>    
 <title>Membership Form</title>     
</head>
 <body>    
 <h1>Membership Form</h1>
    <p>Thanks for choosing to join The Programming Language Club.</p>
	
	<?php
	if(isset($_POST["submitButton"])){//if user already submitted form, process it
	//connecting to the database with PDO
$dsn = "mysql:dbname=authentication"; //name of database we are connection too
$username = "root"; //username for authentication
$password = ""; //password required fro authentication. in this case it is empty
try{
$conn = new PDO( $dsn, $username, $password );  //connect to the database
$conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
     echo "<h2>You Have Been Registered</h2>";
     echo "<p><b>First name</b> ". $_POST['firstName']."</p>";
     echo "<p><b>Last name</b> ".  $_POST['lastName']."</p>";
	 echo "<p><b>Password</b> ". $_POST['password1']."</p>";
	 echo "<p><b>Retyped password</b> ". $_POST['password2']."</p>";
	 echo "<p><b>Gender</b> ".  $_POST['gender']."</p>";
     echo "<p><b>Favorite widget</b> ".  $_POST['favoriteWidget']."</p>" ; 
	 echo"<p><b>Do you want to receive our newsletter?</b> "; 
	 if(isset($_POST['newsletter'])){
		 echo $_POST['newsletter'];
		 
		 }
		 echo "</p>";
	 echo "<p><b>Comments</b> ".   $_POST['comments']."</p>";
	 
}
catch(PDOException $pd){//catch an exception 
echo $pd->getMessage();//print out exception message
}
	}
	else{//print out form
	?>
    <form action=index.php method=post>   
	<div style="width: 30em;" >
        <label for=firstName> First name </label> <br>        <input type=text name=firstName id=firstName required /> <br> <br>
        <label for=lastName> Last name </label> <br>       <input type=text name=lastName id=lastName  required/> <br> <br>
        <label for=password1> Choose a password </label> <br>       <input type=password name=password1 id=password1 required /> <br>  <br>  
		<label for=password2> Retype password </label><br>        <input type=password name=password2 id=password2 required /> <br> <br>
        <label for=genderMale> Are you male... </label>        <input type=radio name=gender id=genderMale value=Male />       
		<label for=genderFemale>...or female?</label> <input type=radio name=gender id=genderFemale value=Female checked /> <br> <br>
        <label for=favoriteWidget> What's your favorite language? </label>        <select name=favoriteWidget id=favoriteWidget size=1>        
		<option value=java> Java </option>     
		<option value=php> PHP </option>          
		<option value=phyton> PHYTON </option>     
		</select> <br> <br>
        <label for=newsletter> Do you want to receive our newsletter? </label>        
		<input type=checkbox name=newsletter id=newsletter value=yes /> <br> <br>
        <label for=comments> Any comments? </label>       
		<textarea name=comments id=comments rows=4 cols=50> </textarea>
        <div style=”clear: both;”>         
	<input type=submit name=submitButton id=submitButton value=Send Details />       
	<input type=reset name=resetButton id=resetButton value="Reset Form" style="margin-right: 20px;" />      
	</div>     
	</div>
	<?php
	}
	
	?>
	</form>
  </body> </html>

In the above script we connected to a database named authentication with PDO.
Note: Please create a database named authentication or all scripts will fail.

1.3 Object Oriented Connection Example

The script below connects to a database with Mysqli object method.

index1.php

 
<!DOCTYPE html> 
 <html lang="en"> 
 <head>    
 <title>Membership Form</title>     
</head>
 <body>    
 <h1>Membership Form</h1>
    <p>Thanks for choosing to join The Programming Language Club.</p>
	
	<?php //mysqli objected oriented
	if(isset($_POST["submitButton"])){//if user already submitted form, process it
$dsn = "localhost"; //name of database we are connection too
$username = "root"; //username for authentication
$password = ""; //password required fro authentication. in this case it is empty
$conn = new mysqli( $dsn, $username, $password );  //connect to the database
if(mysqli_connect_error()){	
die("connection error ". mysqli_connect_error());
}
     echo "<h2>You Have Been Registered</h2>";
     echo "<p><b>First name</b> ". $_POST['firstName']."</p>";
     echo "<p><b>Last name</b> ".  $_POST['lastName']."</p>";
	 echo "<p><b>Password</b> ". $_POST['password1']."</p>";
	 echo "<p><b>Retyped password</b> ". $_POST['password2']."</p>";
	 echo "<p><b>Gender</b> ".  $_POST['gender']."</p>";
     echo "<p><b>Favorite widget</b> ".  $_POST['favoriteWidget']."</p>" ; 
	 echo"<p><b>Do you want to receive our newsletter?</b> "; 
	 if(isset($_POST['newsletter'])){
		 echo $_POST['newsletter'];
		 
		 }
		 echo "</p>";
	 echo "<p><b>Comments</b> ".   $_POST['comments']."</p>";
	}
	else{//print out form
	?>
    <form action=index1.php method=post>   
	<div style="width: 30em;" >
        <label for=firstName> First name </label> <br>        <input type=text name=firstName id=firstName required /> <br> <br>
        <label for=lastName> Last name </label> <br>       <input type=text name=lastName id=lastName  required/> <br> <br>
        <label for=password1> Choose a password </label> <br>       <input type=password name=password1 id=password1 required /> <br>  <br>  
		<label for=password2> Retype password </label><br>        <input type=password name=password2 id=password2 required /> <br> <br>
        <label for=genderMale> Are you male... </label>        <input type=radio name=gender id=genderMale value=Male />       
		<label for=genderFemale>...or female?</label> <input type=radio name=gender id=genderFemale value=Female checked /> <br> <br>
        <label for=favoriteWidget> What's your favorite language? </label>        <select name=favoriteWidget id=favoriteWidget size=1>        
		<option value=java> Java </option>     
		<option value=php> PHP </option>          
		<option value=phyton> PHYTON </option>     
		</select> <br> <br>
        <label for=newsletter> Do you want to receive our newsletter? </label>        
		<input type=checkbox name=newsletter id=newsletter value=yes /> <br> <br>
        <label for=comments> Any comments? </label>       
		<textarea name=comments id=comments rows=4 cols=50> </textarea>
        <div style=”clear: both;”>         
	<input type=submit name=submitButton id=submitButton value=Send Details />       
	<input type=reset name=resetButton id=resetButton value="Reset Form" style="margin-right: 20px;" />      
	</div>     
	</div>
	<?php
	}
	
	?>
	</form>
  </body> </html>

1.4 MySQLi procedural Connection Example

The script below connects to a database with Mysqli procedural method.

index2.php

<!DOCTYPE html> 
 <html lang="en"> 
 <head>    
 <title>Membership Form</title>     
</head>
 <body>    
 <h1>Membership Form</h1>
    <p>Thanks for choosing to join The Programming Language Club.</p>
	
	<?php //mysql procdeural
	if(isset($_POST["submitButton"])){//if user already submitted form, process it
$dsn = "localhost"; //name of database we are connection too
$username = "root"; //username for authentication
$password = ""; //password required fro authentication. in this case it is empty
$conn = mysqli_connect( $dsn, $username, $password );  //connect to the database
if(!$conn){	
die("connection error ". mysqli_connect_error());
}
     echo "<h2>You Have Been Registered</h2>";
     echo "<p><b>First name</b> ". $_POST['firstName']."</p>";
     echo "<p><b>Last name</b> ".  $_POST['lastName']."</p>";
	 echo "<p><b>Password</b> ". $_POST['password1']."</p>";
	 echo "<p><b>Retyped password</b> ". $_POST['password2']."</p>";
	 echo "<p><b>Gender</b> ".  $_POST['gender']."</p>";
     echo "<p><b>Favorite widget</b> ".  $_POST['favoriteWidget']."</p>" ; 
	 echo"<p><b>Do you want to receive our newsletter?</b> "; 
	 if(isset($_POST['newsletter'])){
		 echo $_POST['newsletter'];
		 
		 }
		 echo "</p>";
	 echo "<p><b>Comments</b> ".   $_POST['comments']."</p>";
	}
	else{//print out form
	?>
    <form action=index2.php method=post>   
	<div style="width: 30em;" >
        <label for=firstName> First name </label> <br>        <input type=text name=firstName id=firstName required /> <br> <br>
        <label for=lastName> Last name </label> <br>       <input type=text name=lastName id=lastName  required/> <br> <br>
        <label for=password1> Choose a password </label> <br>       <input type=password name=password1 id=password1 required /> <br>  <br>  
		<label for=password2> Retype password </label><br>        <input type=password name=password2 id=password2 required /> <br> <br>
        <label for=genderMale> Are you male... </label>        <input type=radio name=gender id=genderMale value=Male />       
		<label for=genderFemale>...or female?</label> <input type=radio name=gender id=genderFemale value=Female checked /> <br> <br>
        <label for=favoriteWidget> What's your favorite language? </label>        <select name=favoriteWidget id=favoriteWidget size=1>        
		<option value=java> Java </option>     
		<option value=php> PHP </option>          
		<option value=phyton> PHYTON </option>     
		</select> <br> <br>
        <label for=newsletter> Do you want to receive our newsletter? </label>        
		<input type=checkbox name=newsletter id=newsletter value=yes /> <br> <br>
        <label for=comments> Any comments? </label>       
		<textarea name=comments id=comments rows=4 cols=50> </textarea>
        <div style=”clear: both;”>         
	<input type=submit name=submitButton id=submitButton value=Send Details />       
	<input type=reset name=resetButton id=resetButton value="Reset Form" style="margin-right: 20px;" />      
	</div>     
	</div>
	<?php
	}
	
	?>
	</form>
  </body> </html>

2. Summary

In this tutorial we aquired knowledge about databases. We also learnt how to connect to a mysql database with PHP, using MySQLi and PDO.

3. Download the source code

Download
You can download the full source code of this example here: phpconnecttomysqltutorial

 

(0 rating, 0 votes)
You need to be a registered member to rate this.
Start the discussion Views Tweet it!
Do you want to know how to develop your skillset to become a Web Rockstar?
Subscribe to our newsletter to start Rocking right now!
To get you started we give you our best selling eBooks for FREE!
1. Building web apps with Node.js
2. HTML5 Programming Cookbook
3. CSS Programming Cookbook
4. AngularJS Programming Cookbook
5. jQuery Programming Cookbook
6. Bootstrap Programming Cookbook
and many more ....
I agree to the Terms and Privacy Policy
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments