Join Bill Weinman for an in-depth discussion in this video The multi-platform PDO interface, part of MySQL Essential Training.
- View Offline
This is the CRUD application. CRUD stands for, create, read, update, and delete, the four basic functions of any database application. I've written CRUD in PHP, using the PDO interface for database operations. PDO is PHP data objects, a cross platform database interface for PHP. PDO provides a consistent interface for different database engines, allowing application like CRUD to support SQLite.
Post and SQL, and MYSQL with minimal changes. Scrolling down here a little bit, you'll notice at the top, there are three different blocks of constants, and one of them is in commented, and the other ones are commented out. If I were would, comment this one and uncomment one of the other ones, this same. Code would work with a different database engine. Post SQL, SQL white three or MySQL as in the case here.
Here in text wrangler i can simply select a PHP function and it'll, jump to it in the code. This is the emit function you'll notice at the top of the code, after I initialize all of theses constants and variables, I call emit first and then I call main. And main is simply a jumping off point for, whatever is requested of the function, which is based on web buttons like, I push anywhere on the page. The emit function is called, first though.
And it sets up the database connections. It sets up the, various variables, it initializes things. It gets the application ready to run. And you'll notice down here in init, I have this switch statement, based on DB-Engine. And DB-Engine is defined up at the top here as MySQL. So, in the case of MYSQL, this block of code here will be, executed and this is a tri block, which is how, exceptions are handled in PHP.
And, the first thing it does, is it creates a new PDO object. The PDO interface is object oriented. So, a connection is made by creating a new PDO object. The, $dbh variable, is a traditional is a traditional name for this. It stands for database handle. It's archaic, but it's very common. And it will hold, the PDO object. The connection string, which is this string right here between these quotes. This is called a DSN which stands for data source name.
Its database specific. Notice that the format, is different for each of the different. Database systems. Here you can see for postgress it's different. It includes a port number and, a database name. And, you notice for SQLite it just includes the, database file. The DSN is followed by the user name and password, which is followed by driver options. ATTR persistent, which is the persistent attribute option, provide support for persistent connection.
This means that an instance of this script can remain connected with the server for multiple passes, eliminating some of the overhead of, reestablishing a new connection every time the script is called. PHP offers a number of different interfaces to support MySQL. For a new code, I strong recommend the PDO interface. It's well-maintained. It's efficient. It's object oriented. And it supports MySQL well, as well as most of the, major database management systems.
- Writing queries
- Creating and updating databases and tables
- Using MySQL built-in functions
- Sorting and filtering data
- Updating tables with triggers
- Working with subselects and views
- Creating and using a stored function