Build Your Own Database Driven Web Site Using PHP & MySQL

Build Your own Database Driven Web Site Using PHP & MySQL is a practical hands-on guide to learning all the tools, principles and techniques needed to build a fully functional database-driven web site using PHP & MySQL. This book covers everything from installing PHP & MySQL under Windows, Linux, and Mac through to building a live web-based content management system. While this is essentially a beginners book, it also covers more advanced topics such as the storage of binary data in MySQL, and cookies and sessions in PHP. It comes complete with a set of handy reference guides for PHP & MySQL that include: MySQL syntax MySQL functions MysQL column types PHP functions for working with MySQL Build Your Own Database Driven Web Site Using PHP & MySQL......Page 1 Table of Contents......Page 9 Preface......Page 19 Who Should Read This Book......Page 20 What’s in This Book......Page 21 The Code Archive......Page 24 Your Feedback......Page 25 Code Samples......Page 26 Tips, Notes, and Warnings......Page 27 Installation......Page 29 Your Own Web Server......Page 30 All-in-one Installation......Page 31 Installing MySQL......Page 37 Installing PHP......Page 40 All-in-one Installation......Page 48 Installing MySQL......Page 52 Installing PHP......Page 56 Linux Installation......Page 60 Installing MySQL......Page 61 Installing PHP......Page 65 Post-Installation Set-up Tasks......Page 72 What to Ask Your Web Host......Page 75 Your First PHP Script......Page 76 Full Toolbox, Dirty Hands......Page 80 An Introduction to Databases......Page 81 Logging On to MySQL......Page 83 Structured Query Language......Page 88 Creating a Table......Page 89 Inserting Data into a Table......Page 92 Viewing Stored Data......Page 94 Modifying Stored Data......Page 97 Let PHP Do the Typing......Page 98 Introducing PHP......Page 101 Basic Syntax and Statements......Page 103 Variables, Operators, and Comments......Page 106 Arrays......Page 107 User Interaction and Forms......Page 109 Control Structures......Page 122 Avoid Advertising Your Technology Choices......Page 132 Use PHP Templates......Page 134 Many Templates, One Controller......Page 137 Bring On the Database......Page 141 The Big Picture......Page 143 Connecting to MySQL with PHP......Page 145 Sending SQL Queries with PHP......Page 151 Handling SELECT Result Sets......Page 154 Inserting Data into the Database......Page 160 Deleting Data from the Database......Page 170 Mission Accomplished......Page 177 Relational Database Design......Page 179 Giving Credit Where Credit is Due......Page 180 Rule of Thumb: Keep Entities Separate......Page 181 SELECT with Multiple Tables......Page 186 Simple Relationships......Page 191 Many-to-Many Relationships......Page 194 One for Many, and Many for One......Page 197 Structured PHP Programming......Page 199 Including HTML Content......Page 200 Including PHP Code......Page 202 Types of Includes......Page 208 Shared Include Files......Page 209 Custom Functions and Function Libraries......Page 212 Variable Scope and Global Access......Page 215 Structure in Practice: Template Helpers......Page 219 The Best Way......Page 223 A Content Management System......Page 225 The Front Page......Page 226 Managing Authors......Page 230 Deleting Authors......Page 232 Adding and Editing Authors......Page 235 Managing Categories......Page 240 Searching for Jokes......Page 246 Adding and Editing Jokes......Page 253 Deleting Jokes......Page 265 Summary......Page 266 Content Formatting with Regular Expressions......Page 269 Regular Expressions......Page 270 String Replacement with Regular Expressions......Page 275 Boldface and Italic Text......Page 276 Paragraphs......Page 277 Hyperlinks......Page 280 Matching Tags......Page 283 Putting It All Together......Page 285 Real World Content Submission......Page 288 Cookies......Page 289 PHP Sessions......Page 295 A Simple Shopping Cart......Page 297 Database Design......Page 307 Controller Code......Page 311 Function Library......Page 318 Managing Passwords and Roles......Page 328 A Challenge: Joke Moderation......Page 337 The Sky’s the Limit......Page 339 MySQL Administration......Page 341 phpMyAdmin......Page 342 Database Backups Using mysqldump......Page 347 Incremental Backups Using Binary Logs......Page 349 Granting Privileges......Page 352 Revoking Privileges......Page 356 Access Control Tips......Page 357 Locked Out?......Page 359 Checking and Repairing MySQL Data Files......Page 360 Better Safe than Sorry......Page 364 Sorting SELECT Query Results......Page 365 Setting LIMITs......Page 368 LOCKing TABLES......Page 369 Column and Table Name Aliases......Page 372 GROUPing SELECT Results......Page 375 LEFT JOINs......Page 377 Limiting Results with HAVING......Page 381 Further Reading......Page 382 Binary Data......Page 385 Semi-dynamic Pages......Page 386 Handling File Uploads......Page 392 Assigning Unique Filenames......Page 395 Recording Uploaded Files in the Database......Page 397 Binary Column Types......Page 398 Storing Files......Page 400 Viewing Stored Files......Page 402 Putting It All Together......Page 407 PHP Script Timeout......Page 414 The End......Page 415 ALTER TABLE......Page 417 ANALYZE TABLE......Page 420 CREATE TABLE......Page 421 DELETE......Page 423 DESCRIBE/DESC......Page 424 EXPLAIN......Page 425 INSERT......Page 426 LOCK/UNLOCK TABLES......Page 428 OPTIMIZE TABLE......Page 429 REPLACE......Page 430 SELECT......Page 431 Joins......Page 435 Unions......Page 437 SET......Page 438 SHOW......Page 439 UNLOCK TABLES......Page 440 UPDATE......Page 441 USE......Page 442 Control Flow Functions......Page 443 Mathematical Functions......Page 444 String Functions......Page 447 Date and Time Functions......Page 451 Miscellaneous Functions......Page 458 Functions for Use with GROUP BY Clauses......Page 461 Appendix C: MySQL Column Types......Page 463 Numerical Types......Page 464 Character Types......Page 468 Date/Time Types......Page 473 mysqli_character_set_name......Page 477 mysqli_connect......Page 478 mysqli_data_seek......Page 479 mysqli_fetch_all......Page 480 mysqli_fetch_field......Page 481 mysqli_fetch_fields......Page 482 mysqli_field_count......Page 483 mysqli_get_client_version......Page 484 mysqli_info......Page 485 mysqli_query......Page 486 mysqli_real_query......Page 487 mysqli_store_result......Page 488 mysqli_use_result......Page 489 Index......Page 491 __Build Your own Database Driven Web Site Using PHP & MySQL__This book covers everything from installing PHP & MySQL under Windows, Linux, and Mac through to building a live web-based content management system.While this is essentially a beginners book, it also covers more advanced topics such as the storage of binary data in MySQL, and cookies and sessions in PHP. It comes complete with a set of handy reference guides for PHP & MySQL that include:MySQL syntax MySQL functions MysQL column types PHP functions for working with MySQL Build Your Own Database Driven Web Site Using PHP & MySQL, 4th Edition is a practical, hands-on guide to learning all the tools, principles, and techniques needed to build a fully functional database driven web site using PHP & MySQL. This book covers everything from installing PHP and MySQL on Windows, Linux, and Mac computers, through to building a live, web-based content management system. Preface Who Should Read This Book What’s in This Book Where to Find Help The SitePoint Forums The Book’s Web Site The SitePoint Newsletters Your Feedback Conventions Used in This Book Code Samples Tips, Notes, and Warnings