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
