Expert PHP and MySQL

INTRODUCTION XXIX

CHAPTER 1: TECHNIQUES EVERY EXPERT PROGRAMMER NEEDS TO KNOW 1

Object-Oriented PHP 2

Using MySQL Joins 20

MySQL Unions 28

GROUP BY in MySQL Queries 30

Logical Operations and Flow Control in MySQL 33

Maintaining Relational Integrity 35

Subqueries in MySQL 46

Using Regular Expressions 49

Summary 63

CHAPTER 2: ADVANCED PHP CONCEPTS 65

A Problem That Needs Solving 65

Iterators and the SPL 67

Lambda Functions and Closures 78

Summary 85

CHAPTER 3: MYSQL DRIVERS AND STORAGE ENGINES 87

MySQL Drivers 88

About MySQL Storage Engines 89

Default Storage Engines 93

Other MySQL Supplied Engines 118

Pluggable Engines 121

Engines as Standalone Products 124

Other MySQL Offerings 125

Summary 129

CHAPTER 4: IMPROVING PERFORMANCE THROUGH CACHING 131

eAccelerator and APC 132

memcached 138

Installing memcached 145

Starting memcached 148

Libmemcached 153

Libmemcached Utility Programs 155

PECL/Memcached 158

Practical Caching 180

Summary 188

CHAPTER 5: MEMCACHED AND MYSQL 191

The Memcached Functions for MySQL 191

How the Memcached Functions for MySQL Work 192

Installing the Memcached Functions for MySQL 193

Using the Memcached Functions for MySQL 196

Summary 220

CHAPTER 6: ADVANCED MYSQL 221

Views 221

Stored Procedures and Functions 226

Triggers 232

Transactions 237

Replication 250

Events 264

Summary 270

CHAPTER 7: EXTENDING MYSQL WITH USER-DEFINED FUNCTIONS 271

Introduction to UDFs 272

Developing a UDF 273

A Practical UDF Example 274

Other UDF SQL Statements 300

Debugging a UDF 300

Summary 305

CHAPTER 8: WRITING PHP EXTENSIONS 307

Setting Up the Build Environment 308

Creating an Extension with ext_skel 309

Creating an Extension with CodeGen_PECL 314

Variables in PHP Extensions 321

Using Functions in Extensions 329

Input/Output 340

Arrays and Hash Tables 347

Objects and Interfaces 349

Constants, INI Directives, and Globals 357

Describing an Extension 361

Summary 364

CHAPTER 9: FULL-TEXT SEARCHING 365

MySQL FULLTEXT Indexes 365

A Better Solution: The Sphinx Full-Text Search Engine 369

Developing Applications That Use SPHINX 395

Summary 399

CHAPTER 10: MULTI-TASKING IN PHP AND MYSQL 401

Gearman 401

What Is Gearman? 402

Narada: A Search Engine Application 412

Summary 442

CHAPTER 11: REWRITE RULES 443

Using Rewrite Rules 444

Rewrite Maps 451

Summary 456

CHAPTER 12: USER AUTHENTICATION 457

Designing the Database 458

HTTP-Based Authentication 460

Pure PHP Authentication 466

Access Control Lists 473

Summary 475

CHAPTER 13:UNDERSTANDING THE INFORMATION_SCHEMA 477

Using the INFORMATION_SCHEMA 478

Table Objects Tables 479

Other Database Objects Tables 481

MySQL Status Tables 482

MySQL Meta Data Tables 486

MySQL ACL Permissions Tables 488

INFORMATION_SCHEMA Extensions 491

SHOW Cross Reference 491

Summary 492

CHAPTER 14: SECURITY 493

Hardening Your MySQL Server 493

Encoding Data 501

PHP Security Recipes 506

Summary 516

CHAPTER 15: COMMAND-LINE AND WEB SERVICES 517

Creating Command-Line Scripts 517

Creating Web Services 524

Summary 541

CHAPTER 16: OPTIMIZATION AND DEBUGGING 543

Debugging PHP 543

Optimizing Queries 545

Debugging MySQL 560

Summary 565

INDEX 567