SQL is an essential skill in developing modern web applications. This course will teach you how to generate SQL for manipulating schemas and extracting data in an object-oriented, nearly hassle-free manner using the Zend\Db library.
Modern web applications demand that developers know how to write and maintain SQL queries. Whether you need to make database schema changes or run reports to satisfy management, SQL is the tool that is most often required. In this course, Zend/Db Deep Dive, you will learn how to generate SQL whether for manipulating schemas or searching and extracting data, in an object-oriented, low-hassle manner using the Zend\Db library. You'll start with an introduction to Zend/Db, a SQL refresher, and how to make database connections. You will go over some of the create, update, and delete operations for schemas objects and records. You will also get to see how to make record manipulation simpler and more robust by getting to know two essential data access patterns: RowGateway and TableGateway. You will also learn how to run existing, handwritten SQL queries. By the end of this course, you will be familiar with both the fundamentals as well as some of the most advanced aspects of the library, you'll know how to create easily maintainable and reusable code. Software required: PHP and a good editor or IDE.
Manipulating a Database Schema In this module, we'll build on the foundation we laid in module 1. Specifically, we're going to be working with then Zend\Db objects, which we can use to create, update, and delete schema objects. Objects which include amongst others, tables, indexes, and constraints. As we progress through this module, I hope you see just how flexible and powerful Zend\Db is as it lets us create, update, and delete schema objects, and that we can do so programmatically or in an object-oriented manner. What's more, I hope you see how it reduces the likelihood of errors when generating SQL queries, and reduces the amount of SQL knowledge we need to know, especially when working across multiple database vendors. By the end of this module, you'll know all you need to create, alter, and drop schema objects in an object- oriented and programmatic manner, and be able to explore further the classes covered in greater depth to really understand all the power that they provide. Now what I really want to do, just for a moment, is to encourage you, encourage you, encourage you strongly to make sure you that don't just take what I give you as all there is to know. What I give you is just the jumping off point, really, really it is. I try to give you a good foundation, but you really need to go further than what I give you and just explore, explore, explore.
Using RowGateway and TableGateway for Easy Record Management By this stage, you have a good understanding of how to use Zend/Db SQL to perform a range of tasks with a database. From manipulating a schema to creating, updating, deleting, and selecting records, you've seen a lot of what the library can do. But now it's time to move to some of the advanced features and bring a lot more elegance, sophistication, and dare I say class to how we work with databases. To do so, in this section we'll learn how to make record manipulation simpler and more robust by getting to know two essential data access patterns, these being RowGateway, and TableGateway. By the end of this section, you'll know how to use the RowGateway and TableGateway classes and have a good understanding of the patterns which end up in them. Let's get started with RowGateway.
Hydrating Objects and Paginating Results In the last section, we covered basic ResultSet iteration. We saw how we could use php's looping constructs, such as a foreach loop to iterate over the ResultSet object, return a ResultSet which contained an associative array of column names to column values. But if we're wanting to work with sophisticated applications, a simple associative array I'd suggest is not going to satisfy our needs for very long. We'll likely need to begin working with objects quite quickly, if not straightaway. The question, though, is how? We could, as we iterate over each result, hydrate an object from the associative array for that record. But this isn't the most flexible or scalable approach. It's also not necessary, as by using a more sophisticated ResultSet class, this can all be done transparently for us. In this section, we're going to explore the concept of object hydratation, along with the HydratingResultSet class, which comes packaged with Zend\Db. It makes initializing a new object with data from our retrieved ResultSet almost transparent, not to mention reusable and maintainable. We'll see how we can apply different hydration strategies, depending on how sophisticated the needs of our application are. We'll finish up by iterating over the hydrated objects. By the time we are finished, we'll know how to make hydrating objects from retrieved database data. Not to mention, iterating over them almost painless. So let's get started.
Writing Reusable and Flexible Queries And this brings us to the last section of the course. In this section, we're going to round out the knowledge which we've learned so far by looking at some more advanced ways of building queries. Specifically in this module we're going to look at how queries can be written in ways that make them both reusable and very flexible. It's not going to be a long module, for what it's worth, but it'll be one that improves upon what we already learned in section four, where we learned how to build SQL objects programmatically. However, say for supplying different data to the object instantiating and method calls, the objects were quite specific, which limited the use somewhat. In this section, we're going to see how to extract the where predicate of the SQL objects we've been learning how to build, so that they can be passed around and reused should the need arise. We're also going to learn how to combine multiple predicates together into what I like to call compound predicates. This ability affords us power that we couldn't have imaged that we'd have when we were building them individually. Let's get started.