Expert PHP and MySQL®

**Best practices and expert techniques for even the most demanding MySQL-driven PHP applications** PHP is the world's most popular, general-purpose, open-source scripting language and MySQL is the world's most popular open-source database. This expert-level book begins with a brief overview of the two technologies and quickly moves on to coverage of advanced programming techniques, as well as the exciting new features of the PHP 5.3 release. Written by three of PHP and MySQL experts and veteran authors, this resource focuses on best practices and expert techniques that can be applied to the most difficult MySQL-driven PHP applications. The authors share design patterns and tools that they themselves have tested in order to save you valuable time usually spent on figuring things out via trial and error. * Reveals best practices and advanced techniques for handling challenges you may face with MySQL-driven PHP apps * Reviews practical data reporting techniques for separating business logic from presentation * Explains how to obtain improved performance and flexibility through caching, multi-tasking, PHP extensions, and MySQL user defined functions * Discusses security, optimization, and debugging * Shares author-tested design patterns and tools to save you time and effort Packed with helpful examples and indispensable advice, this book shares tips and tricks that you can immediately apply to your projects. **Note:** CD-ROM/DVD and other supplementary materials are not included as part of eBook file.Content: Chapter 1 Techniques Every Expert Programmer Needs to Know (pages 1–63): Chapter 2 Advanced PHP Concepts (pages 65–85): Chapter 3 MySQL Drivers and Storage Engines (pages 87–129): Chapter 4 Improving Performance through Caching (pages 131–189): Chapter 5 Memcached and MySQL (pages 191–220): Chapter 6 Advanced MySQL (pages 221–270): Chapter 7 Extending MySQL with User?Defined Functions (pages 271–305): Chapter 8 Writing PHP Extensions (pages 307–364): Chapter 9 Full?Text Searching (pages 365–399): Chapter 10 Multi?tasking in PHP and MySQL (pages 401–442): Chapter 11 Rewrite Rules (pages 443–456): Chapter 12 User Authentication (pages 457–475): Chapter 13 Understanding the INFORMATION\_SCHEMA (pages 477–492): Chapter 14 Security (pages 493–516): Chapter 15 Command?Line and Web Services (pages 517–542): Chapter 16 Optimization and Debugging (pages 543–566): Introduction Techniques Every Expert Programmer Needs to Know Object-Oriented PHP Instantiation and Polymorphism Interfaces Magic Methods and Constants Design Patterns Using MySQL Joins INNER JOIN OUTER JOIN Other JOIN Syntax Complex Joins MySQL Unions GROUP BY in MySQL Queries WITH ROLLUP HAVING Logical Operations and Flow Control in MySQL Logic Operators Flow Control Maintaining Relational Integrity Constraints NOT NULL UNSIGNED ENUM and SET UNIQUE KEY FOREIGN KEY Using Server SQL Modes Storage Engine Integrity What MySQL Does Not Tell You What's Missing? Subqueries in MySQL Subquery Correlated Subquery Derived Table Using Regular Expressions General Patterns Expert Regular Expressions Putting It All Together in PHP Regular Expressions in MySQL Summary Advanced PHP Concepts A Problem That Needs Solving Iterators and the SPL A Sample View for the Application The Iterator Interface The Countable Interface The SeekableIterator Interface and Pagination The ArrayAccess Interface Lambda Functions and Closures The Old Way: Lambda-Style Functions Understanding Closures Using the Query Builder for Prototyping Summary MySQL Drivers and Storage Engines MySQL Drivers About MySQL Storage Engines Obtaining Storage Engine Information Default Storage Engines MyISAM InnoDB Memory Blackhole Archive Merge CSV Federated Other MySQL Supplied Engines Falcon Maria Pluggable Engines InnoDB Plugin PBXT XtraDB Engines As Standalone Products InfiniDB TokuDB Infobright Other MySQL Offerings Storage Engine Patch Products MySQL-Related Products Other Engines Integrated Hardware Engines Other Solutions Wafflegrid Summary Improving Performance Through Caching eAccelerator and APC Installing and Configuring APC Installing and Configuring eAccelerator User Cache Checking the Cache Status When to Use APC and eAccelerator memcached What Is memcached? What memcached Does for You How Does memcached Work? How to Use memcached What Is Gearman? Caching Strategies Installing memcached Starting memcached Startup Scripts Testing Your memcached Installation memcached Clients Libmemcached Libmemcached Features Libmemcached Utility Programs Installing Libmemcached Libmemcached Utility Programs memcat memflush memcp memstat memrm memslap memerror PECL/Memcached Connecting, Instantiation Setting Client Behavior Putting and Retrieving Data Append and Prepend Delete Increment and Decrement Multi-get Multi-set Cache Locality Using byKey Methods and Multi get/set GetDelayed CAS Statistics Server List Error Handling Practical Caching Memcached Proxy: Moxi Other "Memcapable" Key-Value Stores Tokyo Tyrant Summary Memcached and MySQL The Memcached Functions for MySQL How the Memcached Functions for MySQL Work Installing the Memcached Functions for MySQL Prerequisites Configure the Source Build the Source Install the UDF Checking Installation Using the Memcached Functions for MySQL Establishing a Connection to the Memcached Server Data Setting Functions Data Fetching Functions Increment and Decrement Behavioral Functions Statistical Functions Version Functions Fun with Triggers (and UDFs) Read-Through Caching with Simple Select Statements Updates Summary Advanced MySQL Views Access Permissions Additional Information about Views Stored Procedures and Functions General Attributes Stored Routine Logic Using Stored Routines Privileges and Meta Data Extending Stored Routines Stored Routine Disadvantages User Defined Functions Triggers No Triggers Trigger Syntax Insert Triggers Update Triggers Delete Triggers Replace Triggers Trigger Permissions Transactions Atomicity Consistency Isolation Durability Implied Commit Replication Replication Purposes Replication Setup Testing MySQL Replication How Does MySQL Replication Work? Important Configuration Options Important Replication Commands Breaking Replication Using Replication Selectively The Issues with MySQL Replication The Benefits of MySQL Replication Events Creating Events Enabling the Events Scheduler Altering Events Event Privileges Event Meta Data Summary Extending MySQL with User-Defined Functions Introduction to UDFs Developing a UDF UDF Development Requirements UDF Required Functions A Practical UDF Example UDF High-Level Design Designing an Algorithm to Use for Your UDF Implementing the Program Building the UDF Installing the UDF Running Your New UDF Using a UDF with PHP Connecting and Disconnecting to MySQL Returning the Result Set Array Other UDF SQL Statements Debugging a UDF Attaching gdb to an Already Running Process Setting a BreakPoint and Stepping through Code Dealing with Literal Values Debugging Summary Summary Writing PHP Extensions Setting Up the Build Environment Creating an Extension with ext_skel Creating and Compiling Skeleton Code Using a Function Definitions File Generating Help Files Creating an Extension with CodeGen_PECL Creating the Basic XML File Defining Functions Defining Constants, INI Directives, and Globals Defining Objects, Methods, and Properties Variables in PHP Extensions Setting and Testing zvals Reading and Comparing zvals Dealing with Strings as zvals Advanced Memory Management Using Functions in Extensions Basic Definitions Using Arguments Defining Argument Information Returning Values Built-In Functions Creating and Consuming PHP API Functions More Notes and Creating Helper Functions Input/Output Standard Out Files and Streams Networking Errors and Warnings Arrays and Hash Tables Building and Accessing Arrays Accessing and Modifying Hash Tables Objects and Interfaces Creating a Class Interacting with Objects Constants, INI Directives, and Globals Constants Globals and INI Directives Describing an Extension Printing a Description Building a Table Specifying a Logo Summary Full-Text Searching MySQL Fulltext Indexes Using MySQL Fulltext Indexes MySQL Fulltext Index Issues A Better Solution: The Sphinx Full-Text Search Engine Sphinx Configuration and Installation Sphinx.conf Settings Starting Sphinx Searching Sphinx Delta Indexes Merging Indexes Developing Applications That Use Sphinx Sphinx and PHP Summary Multi-Tasking in PHP and MySQL Gearman What Is Gearman? Installing and Running Gearman Using the Gearman MySQL UDFs PHP and Gearman Narada: A Search Engine Application Obtaining Narada Narada Components Database Tables for the Search Engine Application Sphinx Setup Gearman Workers Index Page One Other Tidbit of Code The Big Picture Running Narada To-Do List for Narada Other Job Server Systems Summary Rewrite Rules Using Rewrite Rules Understanding the Purpose and Structure of Rewrite Rules Understanding and Controlling Rewrite Rule Flow Conditional Rules Logging and Optimization Rewrite Maps Built-In Maps Random and Text Lookups Using PHP and MySQL Summary User Authentication Designing the Database HTTP-Based Authentication Basic Authentication Digest Authentication Pure PHP Authentication Using PHP Sessions Building Secure Cookies Access Control Lists Summary Understanding the INFORMATION_SCHEMA Using the INFORMATION_SCHEMA Table Objects Tables Other Database Objects Tables MySQL Status Tables PROCESSLIST (5.1) SESSION_STATUS/GLOBAL_STATUS (5.1) SESSION_VARIABLES/GLOBAL_VARIABLES (5.1) MySQL Meta Data Tables CHARACTER_SETS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY MySQL ACL Permissions Tables USER_PRIVILEGES SCHEMA_PRIVILEGES TABLE_PRIVILEGES COLUMN_PRIVILEGES INFORMATION_SCHEMA Extensions SHOW Cross Reference Summary Security Hardening Your MySQL Server Installation Defaults Operating System Security MySQL Security Permissions Additional Database Security Auditing Encoding Data Bi-directional Encoding Single-Directional Encoding PHP Security Recipes Protecting against SQL Injection Protecting against Replay Attacks Protecting against XSS Protecting against CSRF Automation Attacks Summary Command-Line and Web Services Creating Command-Line Scripts Reading Command-Line Input Prompting for Input Completed Classes Setting Up Cron Jobs Bonus: Output in Color Creating Web Services RESTful Web Services SOAP Web Services Getting Started with the SOAP Library Summary Optimization and Debugging Debugging PHP Creating Stack Traces Optimizing Queries Reducing SQL Identifying SQL Statements Optimizing SELECT Optimizing UPDATE Optimizing DELETE Optimizing INSERT Optimizing REPLACE INSERT ... ON DUPLICATE KEY UPDATE Debugging MySQL Error Log Slow Query Log Processlist Other Commands Using a Debugger with MySQL Summary Index Best practices and expert techniques for today's most demanding appsAs the world's most popular, general purpose, open source scripting language, PHP is frequently used with MySQL to create high-traffic, mission-critical applications. This indispensable book shares proven, author-tested best practices and expert techniques that can be applied to even the most demanding MySQL-driven PHP apps. You'll explore ways to extend MySQL with user-defined functions, write PHP extensions, and solve problems when PHP and MySQL alone are not enough and other techniques or third-party tools are necessary.Expert PHP and MySQL:Reviews essential techniques, such as design patterns, complex queries, and advanced regular expressionAddresses advanced PHP concepts, such as iterators and closuresDemonstrates using Gearman for multitasking in your web applicationsDiscusses caching using memcached with MySQL and your PHP web applicationsDiscusses ways to create PHP Extensions and MySQL User Defined FunctionsShows how to use Sphinx for search functionality in your PHP web applicationsWrox Expert books present the wisdom accumulated by an experienced author team who is recognized as experts by the programming community. These experts challenge professional developers to examine their current practices in pursuit of better results.wrox.comProgrammer ForumsJoin our Programmer to Programmer forums to ask and answer programming questions about this book, join discussions on the hottest topics in the industry, and connect with fellow programmers from around the world.Code DownloadsTake advantage of free code samples from this book, as well as code samples from hundreds of other books, all ready to use.Read MoreFind articles, ebooks, sample chapters, and tables of contents for hundreds of books, and more reference resources on programming topics that matter to you.Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.Note: CD-ROM/DVD and other supplementary materials are not included. Best practices and expert techniques for even the most demanding MySQL-driven PHP applications

PHP is the world's most popular, general-purpose, open-source scripting language and MySQL is the world's most popular open-source database. This expert-level book begins with a brief overview of the two technologies and quickly moves on to coverage of advanced programming techniques, as well as the exciting new features of the PHP 5.3 release.

Written by three of PHP and MySQL experts and veteran authors, this resource focuses on best practices and expert techniques that can be applied to the most difficult MySQL-driven PHP applications. The authors share design patterns and tools that they themselves have tested in order to save you valuable time usually spent on figuring things out via trial and error.

• Reveals best practices and advanced techniques for handling challenges you may face with MySQL-driven PHP apps
• Reviews practical data reporting techniques for separating business logic from presentation
• Explains how to obtain improved performance and flexibility through caching, multi-tasking, PHP extensions, and MySQL user defined functions
• Discusses security, optimization, and debugging
• Shares author-tested design patterns and tools to save you time and effort

Packed with helpful examples and indispensable advice, this book shares tips and tricks that you can immediately apply to your projects.

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

mysql > start review; mysql > Being a MySQL DBA, not a developer, I was mostly interested in the MySQL sections but given I have a general interest in scripting I did go through some of the PHP sections. To be honest they were quite advanced for my php knowledge and experience. The good thing about the book is that even though it assumes you have the basic knowledge, it still provides an introductory background on most of the two (PHP and MySQL) topics. This is not a "PHP and MySQL for dummies" so don't expect to learn the very basics nor will you become an expert in either topic by just reading the book. Even if you read the book thoroughly, becoming an expert requires years of hard work and experience. Having said that, his book is a good guide to make it there. If you are at least a basic developer, this book will help you increase your knowledge drastically and create much better apps. If you are already an experienced dev, the book will probably still give you a few insights on things you don't use all that often and help keep your edge. As regards DBAs, the book is worth a great deal in that quite a substantial amount of its content is MySQL, and the PHP chapters will help you understand the developer point of view. Finally, its a great addition to my library for referencing and a good read. mysql > Recommended; mysql > commit;