PHP Data Objects - PDO

PHP PDO & MYsQL PDO provides an abstract layer to access different databases ( including MySQL ). PDO support is available for 12 different databases.

The advantage of using PDO is portability across different database without changing functions. Only the connection string needs to be changed.
PHP Data Object PDO installation or enable and creating connection string to manage MySQL database

PHP driver MySQLi is exclusively for MySQL database, whereas PDO is an interface for accessing different databases ( including MySQL ) from PHP .

How to check the supported databases?
var_dump(PDO::getAvailableDrivers());
Output ( this will change based on your PHP installation / support )
array(2) { [0]=> string(5) "mysql" [1]=> string(6) "sqlite" }
Follow the instructions at php.ini here to enable the support. PDO will not work for versions older than PHP 5 ( Check your PHP Version )

Read the tutorials on pdo_sqlite to manage SQLite database using PDO SQLite drivers. Download Zip file with all sample scripts at end of this page

MySQL Functions (PDO_MYSQL)

Basics
Functions Description
InstallationHow to enable PDO in PHP 5 and above versions
ConnectionConnection to MySQL Database

CRUD : Create Read Update Delete

Functions Description
FetchCollecting single record
RecordsMultiple records from table
InsertAdding records to table
UpdateUpdate record
DeleteDelete records
DropDrop table

Counting rows and columns
Functions Description
RowCountNumber of rows affected by delete or update query
ColumnCountNumber of columns present in a query or table
Blob
Functions Description
BlobManaging Binary data using Blob data type
UploadUpload photo to add to Blob column of MySQL database

From a list of records to a single record

We can display a list of student names by taking data from a table. From this list user can click a single record ( say student name column ) to get full details of the record ( student details )

Transactions

Learn how to use transactions in PHP PDO for secure and efficient database operations. This post covers starting, committing, and rolling back transactions, along with error handling using try-catch. See practical examples for ensuring data integrity while working with databases like MySQL and SQLite.

Stored Procedure

PHP PDO allows us to interact with MySQL stored procedures efficiently. By using prepared statements and binding parameters, we can call stored procedures from PHP to perform complex operations like data manipulation, input/output handling, and error management.

Paging of records using stored Procedure

We can create a stored procedure that takes in parameters like limit and offset to fetch a specific set of records. This allows us to efficiently paginate large datasets.

Error handling

Learn how to use different PDO error modes, handle exceptions, and prevent common pitfalls in our PHP applications.

Injection attack

Examples of injecting SQL into the query.

Difference between PHP PDO and PHP MySQLi

Here are the key differences between PHP PDO and PHP MySQLi:

  • Database Support:
    • PDO: Supports 12 different databases like MySQL, SQLite, PostgreSQL, etc.
    • MySQLi: Only supports MySQL databases.
  • Prepared Statements:
    • Both PDO and MySQLi support prepared statements for secure query execution.
  • Object-Oriented vs Procedural:
    • PDO: Purely object-oriented.
    • MySQLi: Offers both object-oriented and procedural interfaces.
  • Named Placeholders:
    • PDO: Supports named placeholders in prepared statements.
    • MySQLi: Only supports positional placeholders.
  • Portability:
    • PDO: More portable across different database systems.
    • MySQLi: Limited to MySQL.

PHP MySQL PDO sample script installation using MySQL dump and crating tables in Workbench



Basic Php MySQL database script using PDO
You should have PHP installed 
Database MySQL
Check PDO connection from PHP to MySQL

How to Install Script 
Open sql_dump.txt file and use the sql commands to create your tables. Open config.php and enter your mysql login details. Open index.php file and check for any error messages Open any file and use the top menu to navigate different pages You can delete table or remove all records by using different pages using top menu. You can re-initiate all tables and records by visiting links at the top menu. So feel free to experiment with drop table or deleting records.

download Basic database management script using PHP PDO & MySQL ( pdo-basic.zip )
Sample Student table SQL dump

Podcast on MySQL database management using PHP PDO

PDO Installation & enable support
MYSQLI Functions PHP Code generator for PDO & mysqli
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com











    PHP video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer