Build Your Own Database Driven Website using PHP and MySQL - Kevin Yank

Build Your Own Database Driven Website using PHP and MySQL - Kevin Yank - 2003.
   This book is aimed at intermediate or advanced Web designers looking to make the leap into server-side programming. You'll be expected to be comfortable with simple HTML, as I'll make use of it without much in the way of explanation. No knowledge of JavaScript is assumed or required, but if you do know JavaScript, you'll find it will make learning PHP a breeze.
   By the end of this book, you can expect to have a grasp of what's involved in setting up and building a database-driven Website. If you follow the examples, you'll also learn the basics of PHP (a server-side scripting language that gives you easy access to a database, and a lot more) and Structured Query Language (SQL - the standard language for interacting with relational databases) as supported by MySQL, one of the most popular free database engines available today. Most importantly, you'll come away with everything you need to get started on your very own database-driven site in no time!


Table of Contents
Introduction . 1
Who Should Read This Book  2
What's In This Book . 2
The Book's Web Site . 5
The Code Archive  5
Updates and Errata . 5
The SitePoint Forums .. 5
The SitePoint Tech Times .. 6
Your Feedback . 6
1. Installation .. 7
Welcome to the Show .. 7
Windows Installation  8
Installing MySQL  8
Installing PHP  11
Linux Installation . 16
Installing MySQL . 17
Installing PHP  21
Mac OS X Installation .. 24
Installing MySQL . 24
Installing PHP  26
Mac OS X and Unix . 27
Post-Installation Setup Tasks . 27
If Your Web Host Provides PHP and MySQL  29
Your First PHP Script  30
Summary . 32
2. Getting Started with MySQL .. 35
An Introduction to Databases  35
Logging On to MySQL .. 37
So what's SQL? . 40
Creating a Database  40
Creating a Table .. 41
Inserting Data into a Table . 43
Viewing Stored Data .. 44
Modifying Stored Data . 46
Deleting Stored Data . 47
Summary  47
3. Getting Started with PHP  49
Introducing PHP .. 49
Basic Syntax and Commands . 51
Variables and Operators  52
Arrays  54
User Interaction and Forms . 55
Control Structures .. 59
Multipurpose Pages . 63
Summary . 67
4. Publishing MySQL Data on the Web .. 69
A Look Back at First Principles .. 69
Connecting to MySQL with PHP . 71
Sending SQL Queries with PHP  73
Handling SELECT Result Sets .. 74
Inserting Data into the Database .. 77
A Challenge  80
Summary . 80
“Homework” Solution  80
5. Relational Database Design .. 85
Giving Credit where Credit is Due  85
Rule of Thumb: Keep Things Separate  87
Dealing with Multiple Tables . 89
Simple Data Relationships .. 93
Many-to-Many Relationships . 95
Summary . 98
6. A Content Management System .. 99
The Front Page .. 100
Managing Authors  102
Deleting Authors  103
Adding Authors .. 104
Editing Authors .. 105
Magic Quotes  107
Managing Categories  108
Managing Jokes .. 109
Searching for Jokes . 109
Adding Jokes . 113
Editing and Deleting Jokes . 119
Summary .. 119
Build Your Own Database Driven Website using PHP & MySQL
7. Content Formatting and Submission  121
Out with the Old .. 122
Regular Expressions .. 123
String Replacement with Regular Expressions .. 126
Boldface and Italic Text .. 126
Paragraphs . 127
Hyperlinks . 128
Matching Tags . 130
Splitting Text into Pages  131
Putting it all Together . 133
Automatic Content Submission .. 135
Summary .. 136
8. MySQL Administration .. 137
Backing up MySQL Databases  138
Standard Backups Aren't Enough  138
Database Backups using mysqldump . 139
Incremental Backups using Update Logs .. 140
MySQL Access Control .. 142
Using GRANT . 143
Using REVOKE .. 145
Access Control Tips .. 146
Locked Out? . 148
Checking and Repairing MySQL Data Files .. 149
Summary .. 152
9. Advanced SQL  153
Sorting SELECT Query Results .. 153
Setting LIMITs .. 155
LOCKing TABLES  156
Column and Table Name Aliases  158
GROUPing SELECT Results  161
LEFT JOINs . 163
Limiting Results with HAVING . 165
Summary .. 166
10. Advanced PHP  169
Server-Side Includes with PHP  170
Increasing Security with Includes  171
Semi-Dynamic Pages  173
Handling File Uploads  177
Assigning Unique File Names  180
Recording Uploaded Files in the Database .. 181
Build Your Own Database Driven Website using PHP & MySQL
Email in PHP .. 182
Summary .. 184
11. Storing Binary Data in MySQL . 185
Binary Column Types . 186
Storing Files . 187
Viewing Stored Files  188
The Complete Script  191
Advanced Considerations .. 195
MySQL Packet Size .. 195
PHP Script Timeout . 195
Summary .. 196
12. Cookies and Sessions in PHP  197
Cookies .. 197
PHP Sessions .. 201
A Simple Shopping Cart . 204
Summary .. 209
A. MySQL Syntax .. 211
ALTER TABLE .. 211
ANALYZE TABLE  214
CREATE DATABASE . 214
CREATE INDEX .. 214
CREATE TABLE  215
DELETE  217
DESCRIBE .. 217
DROP DATABASE .. 217
DROP INDEX  217
DROP TABLE  218
EXPLAIN . 218
GRANT . 218
INSERT . 219
LOAD DATA INFILE . 220
LOCK/UNLOCK TABLES  221
OPTIMIZE TABLE .. 221
RENAME TABLE . 222
REPLACE . 222
REVOKE .. 223
SELECT  223
Joins . 227
SET . 228
SHOW .. 229
Build Your Own Database Driven Website using PHP & MySQL
UPDATE .. 231
USE . 231
B. MySQL Functions . 233
Control Flow Functions .. 233
Mathematical Functions . 233
String Functions  236
Date and Time Functions .. 241
Miscellaneous Functions  246
Functions for Use with GROUP BY Clauses . 249
C. MySQL Column Types  251
Numerical Types  252
Character Types . 254
Date/Time Types .. 257
D. PHP Functions for Working with MySQL  261
mysql_affected_rows  261
mysql_close .. 261
mysql_connect  262
mysql_create_db  262
mysql_data_seek  262
mysql_db_name . 263
mysql_db_query . 263
mysql_drop_db .. 263
mysql_errno . 264
mysql_error .. 264
mysql_escape_string . 264
mysql_fetch_array . 264
mysql_fetch_assoc . 265
mysql_fetch_field .. 265
mysql_fetch_lengths . 266
mysql_fetch_object  266
mysql_fetch_row  266
mysql_field_flags  266
mysql_field_len .. 266
mysql_field_name . 267
mysql_field_seek  267
mysql_field_table .. 267
mysql_field_type  267
mysql_free_result .. 267
mysql_get_client_info .. 268
mysql_get_host_info . 268
Build Your Own Database Driven Website using PHP & MySQL
mysql_get_proto_info .. 268
mysql_get_server_info . 268
mysql_insert_id .. 268
mysql_list_dbs . 269
mysql_list_fields . 269
mysql_list_tables  269
mysql_num_fields . 269
mysql_num_rows .. 270
mysql_pconnect . 270
mysql_query  270
mysql_result . 270
mysql_select_db . 271
mysql_tablename .. 271
mysql_unbuffered_query  271
Index . 273