Introduction
This article explains publishing MySQL data with a PDO extension in PHP, the PHP Data Object (PDO) extension in PHP. The PDO extension defines a light-weight, consistent interface for accessing databases in PHP. Every database driver that implements the PDO interface can expose database-specific options as regular extension functions. Learn more details about a PDO in my previous article PDO introduction. But this article explains basic connections with PDO, returns data with PDO, update and insert data with the PDO extension. Before using these functions, however, you will check your PDO extension setting in the php.ini file to determine whether the PDO extension is enabled.
;extension=php_pdo_mssql.dll
extension=php_pdo_mysql.dll//only require this for pdo to mysql
;extension=php_pdo_oci.dll
And for the next step you will create a table for storing, updating and retrieving your messages.
![created table]()
Example
<!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">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<p>
<?php
echo $result;
?>
</p
</body>
</html>
The following example explains how to create a PDO object and configure the MySQL connection with PDO.
<?php
try
{
$con = new PDO('mysql:host=localhost;dbname=demo', 'root','');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->exec('SET NAMES "utf8"');
}
catch (PDOException $msg)
{
$result = 'not able to connect to the database server'.$msg->getMessage();
include 'result.php';
exit();
};
$result = 'Your database connection established.';
include 'result.php';
?>
Output
![simple checking your connection with pdo]()
First of all, you will configure the connection. To do that call the PDO object's setAttribute method. If you want to set the PDO attribute that controls the error mode then use this method (PDO::ATTR_ERRMODE) to the mode that throws exceptions (PDO::ERRMODE_EXCEPTION).
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
Sending SQL Queries With PHP
The PDO object is a similar mechanism as the exec method.
<?php
try
{
$con = new PDO('mysql:host=localhost;dbname=demo', 'root','');
//without setAttribute not work your exception
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//$con->exec('SET NAMES "utf8"');
$query = 'CREATE TABLE hello (
hello_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
hello_text TEXT,
hello_date DATE NOT NULL
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB';
$con->exec($query);
}
catch (PDOException $msg)
{
$result = 'Error creating hello table: '."<br>" . $msg->getMessage()."<br>";
include 'result.php';
exit();
}
$result = 'hello table has created.';
include 'result.php';
?>
Output
![pdo query for creating table]()
![show exception error]()
Update Queries with PDO
<?php
try
{
$con = new PDO('mysql:host=localhost;dbname=demo', 'root','');
//without setAttribute not work your exception
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//$con->exec('SET NAMES "utf8"');
$query = 'UPDATE hello SET hello_date="2012-04-01"
WHERE hello_text LIKE "%good%"';
$excuterow = $con->exec($query);
}
catch (PDOException $msg)
{
$result = 'Error performing update: ' . $msg->getMessage();
include 'result.php';
exit();
}
$result = "Updated $excuterow rows.";
include 'result.php';
?>
Output
![update with pdo]()
Refresh again:
![again update query with pdo]()
When you refresh the page and run the same query again then show this message again.
Select data with PDO
First of all, you will create a new file with the name 'hello.php' and you will create a selecting data script with PDO.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>hello word</title>
</head>
<body>
<p>Here are all the hello word in the database:</p>
<?php foreach($hello as $hello): ?>
<blockquote>
<p><?php echo htmlspecialchars($hello, ENT_QUOTES, 'UTF-8'); ?>
</p>
</blockquote>
<?php endforeach; ?>
</body>
</html>
Here, we will show the complete code to pull data out of the database.
<?php
try
{
$con = new PDO('mysql:host=localhost;dbname=demo', 'root','');
//without setAttribute not work your exception
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//$con->exec('SET NAMES "utf8"');
}
catch (PDOException $msg)
{
$result = 'Unable to connect to the database server.';
include 'result.php';
exit();
}
try
{
$query = 'SELECT hello_text FROM hello';
$res = $con->query($query);
}
catch (PDOException $msg)
{
$result = 'Error fetching hello: ' . $msg->getMessage();
include 'result.php';
exit();
}
while ($data = $res->fetch())
{
$hello[] = $data['hello_text'];
}
include 'hello.php';
?>
Output
![select data with pdo]()
Insert the data with PDO
First of all you will create a Add_hello.html.php file.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Add Joke</title>
<style type="text/css">
textarea {
display: block;
width: 200px;
}
</style>
</head>
<body>
<form action="pdoConnection.php" method="post">
<div>
<label for="hello_text">Type hello word here:</label>
<textarea id="hello_text" name="hello_text" rows="3" cols="20">
</textarea>
</div>
<div><input type="submit" value="add"></div>
</form>
</body>
</html>
The following is the final code for inserting data with PDO.
<?php
try
{
$con = new PDO('mysql:host=localhost;dbname=demo', 'root','');
//without setAttribute not work your exception
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//$con->exec('SET NAMES "utf8"');
}
catch (PDOException $msg)
{
$result = 'not able to connect to the database server.';
include 'result.php';
exit();
}
if (isset($_POST['hello_text']))
{
try
{
$query = 'INSERT INTO hello SET hello_text = :hello_text, hello_date = CURDATE()';
$res = $con->prepare($query);
$res->bindValue(':hello_text', $_POST['hello_text']);
$res->execute();
}
catch (PDOException $msg)
{
$result = 'not adding proble with submitted hello: ' . $msg->getMessage();
include 'result.php';
exit();
}
header('Location:add_hello.php');
exit();
}
?>
Output
![add data with pdo]()
And the next step:
![add data with pdo1]()
Finally your data is inserted.
![saved data]()