In this tutorial we will see the basics of PDO(PHP Data Object) the getting started guide for beginners, first and foremost guys if you are still using old and deprecated MySQL extension then please stop using them right away and start use MySQLi or PDO(Recommended) cause MySQL extension is now completely deprecated and got removed from PHP7. Since mysql extension is deprecated it is recommended to switch over from mysql to pdo extension. PDO enables you to write neat and secure code because it has more secure ways which gives extra layer of security in web applications. PDO has it's own fundamental functions and methods which provides the easiest way to access one or more different databases. so let's get started.
connection can be closed by setting $dbCon = null
PHP PDO connection to MySQL database in try/catch block for error handling.
in this file you can get the code for insert, update and delete the rows.
this file contains HTML form to insert new records and read the data from database.
above created 'crud.php' file are included in this file.
that's it we have created here CRUD operations using PHP Data Object (PDO).
hope this was helpful for you.
What is PDO ?
PDO is improved MySQL extension which is a neat and consistent way for accessing database, not only MySQL database it also supports many different databases like Microsoft SQL Server, Oracle, ODBC, PostgreSQL, SQLite and many more.Why PDO ?
Well there are lot's of reasons to use PDO but the main is it reduces the SQL injection possibility because PDO support prepared statement with named parameters and it also has object oriented approach which makes it better performer over other extensions.PDO MySQL Connection
Following is the connection string to access MySQL database, it is also called DSN(Data Source Name) a simple connection string to connect MySQL database, you can also connect other databases like oracle, sqlite, odbc etc… to get the available drivers use this : print_r(PDO::getAvailableDrivers());
$dbPDO = new PDO("mysql:host=localhost;dbname=database_name",$dbuser,$dbpass);
what if you get error, it also easy to handle error or exceptions
$dbCon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
Wrap within try/catch
you should always wrap your PDO connection string in a try/catch to handle exceptions<?php
define("DB_DRIVER", "mysql"); // database driver, mysql
define("DB_HOST", "localhost"); // db host
define("DB_NAME", "dbpdo"); // database name
define("DB_USER", "root"); // database username
define("DB_PASS", ""); // database password
try {
$dbCon = new PDO(DB_DRIVER . ':host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASS);
$dbCon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $ex) {
echo $ex->getMessage();
die();
}
connection can be closed by setting $dbCon = null
Create Database and Table :
run the following SQL in your phpmyadmin to create database table.
CREATE DATABASE`dbpdo`;
CREATE TABLE `dbpdo`.`tbl_test` (
`id` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 25 ) NOT NULL ,
`email` VARCHAR( 25 ) NOT NULL
) ENGINE = MYISAM ;
Database connection
dbcon.phpPHP PDO connection to MySQL database in try/catch block for error handling.
<?php
$DB_host = "localhost";
$DB_user = "root";
$DB_pass = "";
$DB_name = "dbpdo";
try
{
$DBcon = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass);
$DBcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo "ERROR : ".$e->getMessage();
}
Create, Update and Delete :
crud.phpin this file you can get the code for insert, update and delete the rows.
<?php
require_once 'dbcon.php';
if(isset($_POST['save']))
{
$uname = $_POST['uname'];
$umail = $_POST['umail'];
$stmt = $DBcon->prepare("INSERT INTO tbl_test(username,email) VALUES(:uname, :umail)");
$stmt->bindparam(':uname', $uname);
$stmt->bindparam(':umail', $umail);
$stmt->execute();
}
if(isset($_GET['delete_id']))
{
$id = $_GET['delete_id'];
$stmt = $DBcon->prepare("DELETE FROM tbl_test WHERE id=:id");
$stmt->execute(array(':id' => $id));
header("Location: index.php");
}
if(isset($_GET['edit_id']))
{
$stmt = $DBcon->prepare("SELECT * FROM tbl_test WHERE id=:id");
$stmt->execute(array(':id' => $_GET['edit_id']));
$editRow=$stmt->FETCH(PDO::FETCH_ASSOC);
}
if(isset($_POST['update']))
{
$uname = $_POST['uname'];
$umail = $_POST['umail'];
$id = $_GET['edit_id'];
$stmt = $DBcon->prepare("UPDATE tbl_test SET username=:uname, email=:uemail WHERE id=:id");
$stmt->bindparam(':uname', $uname);
$stmt->bindparam(':uemail', $umail);
$stmt->bindparam(':id', $id);
$stmt->execute();
header("Location: index.php");
}
?>
Data read
index.phpthis file contains HTML form to insert new records and read the data from database.
above created 'crud.php' file are included in this file.
<?php
require_once 'crud.php';
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<style type="text/css">
input
{
width:100%;
}
</style>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PDO CRUD Tutorial - cleartuts</title>
</head>
<body>
<center>
<form method="post">
<table border="1" width="40%" cellpadding="15">
<tr>
<td><input type="text" name="uname" placeholder="Username" value="<?php if(isset($_GET['edit_id'])){ print($editRow['username']); } ?>" /></td>
</tr>
<tr>
<td><input type="text" name="umail" placeholder="Email" value="<?php if(isset($_GET['edit_id'])){ print($editRow['email']); } ?>" /></td>
</tr>
<tr>
<td>
<?php
if(isset($_GET['edit_id']))
{
?>
<button type="submit" name="update">update</button>
<?php
}
else
{
?>
<button type="submit" name="save">save</button>
<?php
}
?>
</td>
</tr>
</table>
</form>
<br />
<?php
$stmt = $DBcon->prepare("SELECT * FROM tbl_test ORDER BY id DESC");
$stmt->execute();
?>
<table border="1" cellpadding="15" width="40%">
<?php
if($stmt->rowCount() > 0)
{
while($row=$stmt->FETCH(PDO::FETCH_ASSOC))
{
?>
<tr>
<td><?php print($row['username']); ?></td>
<td><?php print($row['email']); ?></td>
<td><a onclick="return confirm('Sure to Edit ? ')" href="index.php?edit_id=<?php print($row['id']); ?>">EDIT</a></td>
<td><a onclick="return confirm('Sure to Delete ? ')" href="index.php?delete_id=<?php print($row['id']); ?>">DELETE</a></td>
</tr>
<?php
}
}
else
{
?>
<tr>
<td><?php print("nothing here..."); ?></td>
</tr>
<?php
}
?>
</table>
</center>
</body>
</html>
that's it we have created here CRUD operations using PHP Data Object (PDO).
hope this was helpful for you.
Nice tut about PDO...
ReplyDeleteit's good to use pdo or mysqli instead of old mysql extension...
What do you need with the table you're using in Create database at the top?
DeleteI ask that,
Because you used a very different table here.....
$stmt = $DBcon->prepare("INSERT INTO tbl_test(username,email) VALUES(:uname, :umail)");
I just made the correction in the table.
Deleteand thanks a lot for correcting me on it.
What is uname and umail?
ReplyDeleteI have that changed to username and email
When I click on save I get this error message
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Dubbele ingang '' voor zoeksleutel 'password'' in C:\wamp\www\pdo\crud.php on line 15
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Dubbele ingang '' voor zoeksleutel 'password' in C:\wamp\www\pdo\crud.php on line 15
Dubbele ingang '' voor zoeksleutel. = in English means that this: Double input '' for search key
But I have no password in there, only in dbcon. php is a password
Is this dead
ReplyDeleteit's working....
ReplyDeleteI have now 12 fields, with these names, foto, gebruikers_naam, naam, gebdatum, straat, postcode, plaats, telefoon, user_email, club, caramboles, hoogste.
ReplyDeletein English is that: Photo, user_name, name, date of birth, postal code, city, street, phone number. Association, carambole, highest serie.
So there are some fields with only numbers. like phone number, caramboles and highest serie and there is an date field.
Now I don't know if that influence has
but now it doesn't work,
I get an error message when entering and update at this line: $stmt >-execute ();
Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
PDOException: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
i think u have mismatch in prepare statement query and bindparam values...
Delete$stmt = $DBcon->prepare("INSERT INTO tbl_test(username,email) VALUES(:uname)");
$stmt->bindparam(':uname', $uname);
$stmt->bindparam(':umail', $umail);
$stmt->execute();
here in above code in insert query values(:uname) and stmt->bindparam(":uname",$uname) must be same , so check them again..
this type of error occurs when u have mismatch in prepare and bindparam values.
Yes that does work like you have it. But it seems that it only works with 2 fields
ReplyDeleteI have this, And at the edit and update the same
[code]
$stmt = $DBcon->prepare("INSERT INTO tbl_test(foto, user_name, naam, user_email) VALUES(:ufoto, :uname, :unaam, :ugebdatum, :ustraat , :postcode, :uplaats, :telefoon, :umail, :club, :ucaraboles, :hoogste)");
$stmt->bindparam(':ufoto', $ufoto);
$stmt->bindparam(':uname', $uname);
$stmt->bindparam(':unaam', $unaam);
$stmt->bindparam(':ugebdatum', $ugebdatum);
$stmt->bindparam(':ustraat', $ustraat);
$stmt->bindparam(':upostcode', $upostcode);
$stmt->bindparam(':uplaats', $plaats);
$stmt->bindparam(':utelefoon', $utelefoon);
$stmt->bindparam(':umail', $umail);
$stmt->bindparam(':uclub', $uclub);
$stmt->bindparam(':ucaramboles', $ucaramboles);
$stmt->bindparam(':uhoogste', $uhoogste);
$stmt->execute();
[/code]
Your elements in query in incorrect.
DeleteYes I no that works, But not with other field names
ReplyDeleteNow it works
ReplyDeleteI have put the fields one by one and each time tested.
But I have one more question: On different forums.
They told me that I not should use this * because that everything can be, But just select what I want to select
But I don't understand that,
Because it shows only what's in the database
And thanks for this tutorial.
one more question.
ReplyDeleteI want a textarea in the post, so I also can post and edit messages
with a ubb code with smileys and bold, underlined and italic.
that works with onClick="javascript:addSC
But that do not work,
Because if I click on a smiley or a button I always get the placeholder back in the textarea
How does that work in pdo?
,This is not really safe to use because it can fill in everything, and that is not intended I assume.
ReplyDeleteThere is nothing done to stop SQL injection
ya but it's good to use PDO instead of mysql queries
Deletenice tutorial, can u post a tutorial pdo class to handle your database operations
ReplyDeletenice tutorial , can u post a tutorial on oop class to handle your database operations
ReplyDeletePDO CRUD with OOP
DeleteGreat beginning php tutorials Very clear and helpful for beginners.
ReplyDeleteyou think you tasty
ReplyDeleteSimple and Nice.
ReplyDeleteThanks Prashant.
DeleteHi... nice tutorial, but i can't seem to figure out how to get the files via Box. Is there another way to download the files for this tutorial? Thanks!
ReplyDeleteHi, Nice tutorial! I'm wondering about nested sql queries with pdo.
ReplyDeleteex:
foreach($db->query('SELECT id, donor_fname, donor_lname FROM donors') as $row) {
echo $row['id'].' '.$row['donor_fname'] . '' . $row['donor_lname'].'
';
foreach($db->query('SELECT donors.id, gifts.amount, gifts.date_given FROM donors,gifts WHERE donors.id = gifts.donor_id') as $gift) {
echo "\t". $gift['id'].' '.$gift['amount'].' '.$gift['date_given'].'
';
}
}
will only print the inner (or last executed) query. A result set from a join leaves me to parse the result sets with php employing the same logic that sql queries seem better suited to... next tutorial??
downlode link not work
ReplyDeleteHow to use PDO in functions!? Thx!
ReplyDeleteYou can also also pass an array of $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,); at the time of PDO connection
ReplyDelete