For accessing databases in a Web application, PHP provides two interfaces, PDO and MySQLi, to access a database in a web application. As indicated by its name, MySQLi works only with MySQL database.
PDO stands for PHP Data Objects which is a consistent object-oriented interface for accessing 12 different Databases. This means once we know how to program with PDO for accessing one Database type, the same code will work for most other database types too.
PDO API Extension
By default, PDO is enabled in PHP installations except with two extensions that must be installed: PDO and a driver for the database to be accessed.
All the current supporting PDO drivers are available at php.net-PDO Driver
To install MySQL driver, install php-mysql package along with a PHP installation.
Windows system with an xampp installation
The latest xampp (by March 2019) should include PHP 7.0. The extension, pdo_mysql, has been included in the installation.
To find whether PDO extension is enabled or not, open the PHP information page at http://locahost:80/dashboard/phpinfo.php. (You may need to replace 80 with your port number.)
On the PHP information page, search (CTRL+F) the word ‘pdo’; if exists, it may have 15 matches for pdo.
The following blocks on the page confirms that two PDO drivers have been pre-installed for MySQL and SQLite and both drivers have been enabled.
If the PDO driver is not enabled, open the PHP initialization file, php/php.ini, at the xampp directory. Uncomment (remove the starting semicolon) the line for the Database type. The following shows two PDO drivers are enabled for MySQL and SQLight, respectively.
extension=php_mysqli.dll
;extension=php_oci8_12c.dll ; Use with Oracle Database 12c Instant Client
;extension=php_openssl.dll
;extension=php_pdo_firebird.dll
extension=php_pdo_mysql.dll
;extension=php_pdo_oci.dll
;extension=php_pdo_odbc.dll
;extension=php_pdo_pgsql.dll
extension=php_pdo_sqlite.dllMacOS with AMPPS
To find whether PDO extension is enabled or not, open the PHP information page at http://localhost/cgi-bin/phpinfo.cgi. On the PHP information page, search (CTRL+F) the word ‘pdo’; if exists, it may have over 20 matches for pdo.
(AWS) EC2 instance running Ubuntu with Apache installation
You can install PHP 7.0 and PDO for MySQL by the command:
$ sudo apt-get install -y php7.0 libapache2-mod-php7.0 php7.0-mysqlIf you want to install Apache, PHP and MySQL, all three one time, run the commands:
$ sudo apt-get install -y mysql-server mysql-client
$ sudo mysql_secure_installation
$ sudo apt-get install -y php7.0 apache2 libapache2-mod-php7.0 php7.0-mysqlTo see what extensions available, run the command
sudo apt search php7.0-*.
The following part shows a Web database in MySQL by using PDO.
Creating a MySQL database
In order to show the code for Web databases by using PDO, be sure to have your database service running and a database is ready.
If you need a sample database, follow two posts below to build a MySQL database counselor in a local MySQL server from xampp.
-
[Registering MySQL Server in NetBeans IDE]( {{site.url}}{{site.baseurl}}{% post_url 2017-02-13-register-mysql-netbeans %} )
-
[Creating a New MySQL Database in NetBeans IDE]( {{site.url}}{{site.baseurl}}{% post_url 2017-02-15-create-new-database-netbeans %} )
PDO will be used to view the counselor table in the sample database.
Script 1: login.php
1<?php
2define('DB_DRIVER', 'mysql');
3define('DB_HOST', "localhost");
4define("DB_USER", "dbusername");
5define("DB_PASSWORD", "dbpassword");
6define("DB_DATABASE", "counselor");
7?>
Script 2: mysql_error.php
1<?php
2function mysql_error()
3{
4 echo 'We are sorry.<p>Fatal error</p>Please click the back button or contact the administrator at admin@site.com';
5}
6?>
Script 3: access_database.php
1<?php
2require_once('login.php');
3require_once('mysql_error.php');
4
5//$dsn = 'mysql:host=localhost:3306;dbname=counselor';
6$dsn = DB_DRIVER . ':host=' . DB_HOST . ';dbname=' . DB_DATABASE;
7
8try {
9 // 1.Make a PDO object
10 $pdo = new PDO($dsn, DB_USER, DB_PASSWORD);
11 if(!$pdo) die('Fatal Error');
12
13 // 2.Compose a SQL query
14 $tablename = 'counselor';
15 $sql = "select * from $tablename";
16
17 // 3.Run the query
18 $result = $pdo->query($sql);
19
20 // 4.Parse the query result
21 // PDO::FETCH_ASSOC: Return next row as an array indexed by column name
22 // 4.1 Get table column names
23 $colnames = array_keys($result->fetch(PDO::FETCH_ASSOC));
24 // 4.2 Print each column name
25 foreach ($colnames as $key => $name) {
26 echo "<p>$key: $name</p>";
27 }
28 // 4.3 Fetch all rows from $result
29 $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
30 // Print each row array
31 foreach ($rows as $key => $row) {
32 echo "<p>";
33 foreach ($row as $key => $value) {
34 echo "<br>";
35 echo htmlspecialchars($key)." : ".htmlspecialchars($value);
36 }
37 echo '</p><hr>';
38 }
39 // 5. Close a connection
40 $result->close();
41 $pdo->close();
42} catch (Exception $ex) {
43 echo($ex->getMessage());
44 mysql_error();
45}
Specify a Data Source Name
Line 6 tspecifies a data source name in the variable $dsn. The resulting dsn string is
mysql:host=localhost;dbname=counselorInstantiate a new PDO Object
Line 10 creates a new PDO object for a database connection associated with a particular data source name and database user credential.
<?php
$pdo = new PDO($dsn, DB_USER, DB_PASSWORD);
Compose a SQL Statement
After a PDO object is available, lines 14 and 15 create a SQL statement in a string variable $sql.
<?php
$tablename = 'counselor';
$sql = "select * from $tablename";
Execute the Statement
Line 18 shows the script that runs a SQL statement in $sql and stores the result in $result.
<?php
$result = $pdo->query($sql);
Fetch the Query Results
Lines 23 through 38 are the script for parsing $result to get the database table column names and print each row in the query result.
Line 23
<?php
$colnames = array_keys($result->fetch(PDO::FETCH_ASSOC));
$result->fetch(PDO::FETCH_ASSOC) fetches the first result row into an array; then extracts the keys of the array into $colnames. The array keys are the database table column names.
Fetch Style:
A call to the fetch method on $result with a PDO constant PDO::FETCH_ASSOC for a fetch style,
$result->fetch(PDO::FETCH_ASSOC)
will fetch the next row into a PHP array, whose elements are indexed by the database table column names. Take the counselor table as an example, the array from fetching the first row in $result is:
Array
(
[counselor_id] => 1
[first_name] => Jake
[nick_name] => The Snake
[last_name] => Roberts
[telephone] => 412 565-5656
[email] => snake@ifpwafcad.com
[member_since] => 2003-09-10
)Refer to php.net for other fetch styles.
At the lines 25 through 27, a foreach statement scans each element in the array $colnames and prints both key and element in an HTML paragraph.
<?php
foreach ($colnames as $key => $name) {
echo "<p>$key: $name</p>";
}
Print All Rows on the Page
The following scrip, at the lines 28 through 36, will print every row returned from running the query $sql, in an HTML paragraph with each column in one line.
<?php
// 4.3 Fetch all rows from $result
$rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
// Print each row array
foreach ($rows as $key => $row) {
echo "<p>";
foreach ($row as $key => $value) {
echo "<br>";
echo htmlspecialchars($key)." : ".htmlspecialchars($value);
}
echo '</p><hr>';
}
Note: Everything is displayed in plain text without styles. Custom style sheets can be added to format the output.
For example, to custom each row with the following look,
Add the following embedded style sheets after the PHP block.
<style>
p {
font-family: georgia, serif;
font-size: 1.2em;
padding: 1em;
margin: 0 auto;
width: 50%;
border: 2px solid #ff9900;
border-radius: 1em;
background-color: snow;
}
p:hover {
background-color: lightsalmon;
}
hr {
color: #ff9900;
height: 1px;
}
</style>Test the Page
Test the PHP page in a Web server that supports PHP.
Share this post
Twitter
Facebook
LinkedIn
Email