Build Your Own Database Driven Website Using PHP & MySQL

by Yank, Kevin

• ISBN13: 9780975240212
• ISBN10: 0975240218
• Edition: 3rd
• Format: Paperback
• Copyright: 2004-10-30
• Publisher: Sitepoint Pty Ltd

Summary

Build Your Own Database-Driven Website Using PHP & MySQLis a practical guide for first-time users of PHP & MySQL that teaches readers by creating a fully working Content Management System, Shopping Cart and other real-world applications. There has been a marked increase in the adoption of PHP, most notably in the beginning to intermediate levels. PHP now boasts over 30% of the server side scripting market (Source: php.weblogs.com ).The previous edition sold over 17,000 copies exclusively through Sitepoint.com alone. With the release of PHP 5, SitePoint have updated this bestseller to reflect best practice web development using PHP 5 and MySQL 4.The 3rd Edition includes more code examples and also a new bonus chapter on structured PHP Programming which introduces techniques for organizing real world PHP applications to avoid code duplication and ensure code is manageable and maintainable. The chapter introduces features like include files, user-defined function libraries and constants, which are combined to produce a fully functional access control system suitable for use on any PHP Website.

Author Biography

Kevin Yank: As Technical Director for SitePoint, Kevin Yank oversees all of its technical publications. Having written over 50 articles for SitePoint, he has dabbled in just about every Web-related language out there. Kevin also writes The SitePoint Tech Times, a newsletter that covers Web technology, reaching over 75,000 readers worldwide

Table of Contents

Preface	ix
Who Should Read This Book	x
What's In This Book	x
The Book's Website	xiii
The Code Archive	xiii
Updates and Errata xiii
The SitePoint Forums	xiv
The SitePoint Newsletters	xiv
Your Feedback	xiv
1. Installation	1	(28)
Windows Installation	2	(10)
Installing MySQL	2	(4)
Installing PHP	6	(6)
Linux Installation	12	(8)
Removing Packaged Software	13	(1)
Installing MySQL	14	(3)
Installing PHP	17	(3)
Mac OS X Installation	20	(2)
Installing MySQL	20	(2)
Installing PHP	22	(1)
Mac OS X and Linux	22	(1)
Post-Installation Setup Tasks	22	(3)
If Your Web Host Provides PHP and MySQL	25	(1)
Your First PHP Script	26	(2)
Summary	28	(1)
2. Getting Started with MySQL	29	(14)
An Introduction to Databases	29	(2)
Logging On to MySQL	31	(3)
So, What's SQL?	34	(1)
Creating a Database	34	(1)
Creating a Table	35	(2)
Inserting Data into a Table	37	(1)
Viewing Stored Data	38	(2)
Modifying Stored Data	40	(1)
Deleting Stored Data	41	(1)
Summary	41	(2)
3. Getting Started with PHP	43	(24)
Introducing PHP	43	(2)
Basic Syntax and Commands	45	(2)
Variables and Operators	47	(1)
Arrays	48	(2)
User Interaction and Forms	50	(6)
Control Structures	56	(5)
Multipurpose Pages	61	(5)
Summary	66	(1)
4. Publishing MySQL Data on the Web	67	(18)
A Look Back at First Principles	67	(2)
Connecting to MySQL with PHP	69	(2)
Sending SQL Queries with PHP	71	(1)
Handling SELECT Result Sets	72	(3)
Inserting Data into the Database	75	(5)
A Challenge	80	(1)
Summary	80	(1)
"Homework" Solution	80	(5)
5. Relational Database Design	85	(16)
Giving Credit where Credit is Due	85	(2)
Rule of Thumb: Keep Things Separate	87	(3)
Dealing with Multiple Tables	90	(4)
Simple Relationships	94	(2)
Many-to-Many Relationships	96	(3)
Summary	99	(2)
6. A Content Management System	101	(42)
The Front Page	102	(3)
Managing Authors	105	(2)
Deleting Authors	107	(3)
Adding Authors	110	(2)
Editing Authors	112	(5)
Magic Quotes	115	(2)
Managing Categories	117	(6)
Managing Jokes	123	(19)
Searching for Jokes	123	(6)
Adding Jokes	129	(8)
Editing and Deleting Jokes	137	(5)
Summary	142	(1)
7. Content Formatting and Submission	143	(22)
Out with the Old	144	(1)
Regular Expressions	145	(3)
String Replacement with Regular Expressions	148	(7)
Boldface and Italic Text	149	(1)
Paragraphs	149	(1)
Hyperlinks	150	(2)
Matching Tags	152	(3)
Splitting Text into Pages	155	(2)
Putting it all Together	157	(5)
Automatic Content Submission	162	(1)
Summary	163	(2)
8. MySQL Administration	165	(18)
Backing up MySQL Databases	166	(4)
Database Backups using mysgldump	167	(1)
Incremental Backups using Update Logs	168	(2)
MySQL Access Control	170	(8)
Using GRANT	171	(3)
Using REVOKE	174	(1)
Access Control Tips	174	(3)
Locked Out?	177	(1)
Checking and Repairing MySQL Data Files	178	(3)
Summary	181	(2)
9. Advanced SQL Queries	183	(16)
Sorting SELECT Query Results	183	(3)
Setting LIMITs	186	(1)
LOCKing TABLES	187	(2)
Column and Table Name Aliases	189	(3)
GROUPing SELECT Results	192	(2)
LEFT JOINs	194	(3)
Limiting Results with HAVING	197	(1)
Summary	198	(1)
10. Binary Data	199	(22)
Semi-Dynamic Pages	199	(5)
Handling File Uploads	204	(4)
Assigning Unique File Names	206	(2)
Recording Uploaded Files in the Database	208	(7)
Binary Column Types	209	(1)
Storing Files	210	(2)
Viewing Stored Files	212	(3)
The Complete Script	215	(5)
Large File Considerations	220	(1)
MySQL Packet Size	220	(1)
PHP Script Timeout	220	(1)
Summary	220	(1)
11. Cookies and Sessions in PHP	221	(14)
Cookies	221	(4)
PHP Sessions	225	(3)
A Simple Shopping Cart	228	(6)
Summary	234	(1)
12. Structured PHP Programming	235	(42)
What is Structured Code?	235	(1)
The Need for Structured Code	236	(2)
Include Files	238	(15)
Types of Includes	242	(2)
Including HTML Content	244	(2)
Locating Include Files	246	(3)
Returning from Includes	249	(4)
Custom Functions and Function Libraries	253	(10)
Variable Scope and Global Access	257	(4)
Optional and Unlimited Arguments	261	(2)
Constants	263	(2)
Structure In Practice: Access Control	265	(9)
Summary	274	(3)
A. MySQL Syntax	277	(24)
ALTER TABLE	277	(3)
ANALYZE TABLE	280	(1)
CREATE DATABASE	280	(1)
CREATE INDEX	281	(1)
CREATE TABLE	281	(2)
DELETE	283	(1)
DESCRIBE	284	(1)
DROP DATABASE	285	(1)
DROP INDEX	285	(1)
DROP TABLE	285	(1)
EXPLAIN	285	(1)
GRANT	286	(1)
INSERT	286	(1)
LOAD DATA INFILE	287	(1)
LOCK/UNLOCK TABLES	288	(1)
OPTIMIZE TABLE	289	(1)
RENAME TABLE	289	(1)
REPLACE	290	(1)
REVOKE	290	(1)
SELECT	291	(6)
Joins	295	(2)
Unions	297	(1)
SET	297	(1)
SHOW	298	(1)
UNLOCK TABLES	299	(1)
UPDATE	299	(1)
USE	300	(1)
B. MySQL Functions	301	(20)
Control Flow Functions	301	(1)
Mathematical Functions	301	(4)
String Functions	305	(4)
Date and Time Functions	309	(6)
Miscellaneous Functions	315	(3)
Functions for Use with GROUP BY Clauses	318	(3)
C. MySQL Column Types	321	(10)
Numerical Types	322	(2)
Character Types	324	(3)
Date/Time Types	327	(4)
D. PHP Functions for Working with MySQL	331	(14)
mysql_affected_rows	331	(1)
mysgl_client_encoding	331	(1)
mysql_close	332	(1)
mysql_connect	332	(1)
mysql_create_db	333	(1)
mysgl_data_seek	333	(1)
mysql_db_name	333	(1)
mysql_db_query	333	(1)
mysql_drop_db	334	(1)
mysql_errno	334	(1)
mysql_error	334	(1)
mysgl_escape_string	334	(1)
mysql_fetch_array	335	(1)
mysql_fetch_assoc	335	(1)
mysgl_fetch_field	335	(1)
mysgl_fetch_lengths	336	(1)
mysgl_fetch_object	336	(1)
mysgl_fetch_row	337	(1)
mysgl_field_flags	337	(1)
mysgl_field_len	337	(1)
mysgl_field_name	337	(1)
mysgl_field_seek	337	(1)
mysgl_field_table	338	(1)
mysgl_field_type	338	(1)
mysgl_free_result	338	(1)
mysgi_get_clientinfo	338	(1)
mysgl_get_host_info	339	(1)
mysgl_get_proto_info	339	(1)
mysgl_get_server_info	339	(1)
mysgl_info	339	(1)
mysgl_insert_id	339	(1)
mysgl_list_dbs	340	(1)
mysgl_list_fields	340	(1)
mysgi_list_processes	340	(1)
mysgl_list_tables	340	(1)
mys gl_num_fields	341	(1)
mys gl_num_rows	341	(1)
mysql_pconnect	341	(1)
mysgl_ping	341	(1)
mysql_query	342	(1)
mysgl_real_escape_string	342	(1)
mysgl_resuit	342	(1)
mysql_select_db	343	(1)
mysgl_stat	343	(1)
mysgl_tablename	343	(1)
mysgi_thread_id	343	(1)
mysgl_unbuffered_query	343	(2)
Index	345