Get AMP’d: Building PHP/MySQL Web Databases Under OS X Scot Hacker

1. Get AMP’d: Building PHP/MySQL Web Databases Under OS X Scot Hacker

2. Dynamic publishing • Making the jump from static HTML to dynamic / database-driven. • Pages assembled on the fly • Instantaneous changes site-wide • Single template drives entire site • No more propagation of errors • Maintain “state” easily • How can Amazon recommend “other books you might like?” • Assembly required • J-School jobs database

3. AMPIdeal partnership • Apache (web server) • MySQL (database server) • PHP (scripting language) ((glue)) Independent / Interdependent

4. Marketshare • PHP competes with Allaire's ColdFusion and Microsoft's ASP • Difficult to assess market size but Apache is the most popular web server and is seldom coupled w/ASP. CF seems to be losing ground. ASP is being subsumed into .NET. • PHP rapidly growing on Apache and even on Windows web servers. • Many low-cost providers springing up to support Web application development at low cost, almost always via PHP/MySQL. e.g. www.phpwebhosting.com • Content created on a Mac can easily be moved to a commercial or 3rd-partyAMP host.

5. Unix power • Before OS X, only FileMaker and a few other obscure solutions available. Mac users no longer take a back seat. • Unix power is at your fingertips - use it! • No need for commercial tools - all free, secure, and well-supported.

6. The AMP request process • Straight HTML requests are returned directly to requesting agent. • If request includes PHP commands, they’re sent to Apache’s PHP module for parsing. • If PHP commands include database requests, those are passed to database server (which may or may not be on the same machine). • Query results are passed back to PHP module for inclusion. • PHP module can now return a “file” back to Apache, which hands it back to requesting agent.

7. Manual vs. Automatic • DreamWeaver can take a lot of PHP-programming responsibility off your hands. • As with HTML and JavaScript, auto-generated PHP may not be as clean as hand-generated, but it’s sure easier. • DW still requires you to do the initial database setup yourself. • It pays to learn some of the “guts” -- get some mud on your hands so you know how things work.

8. Fire Up Apache • Installed but not enabled by default - turn on Web Sharing in prefs • Test your site at http://localhost/ or http://localhost/~yourname/

9. PHP • A server-interpreted language – you never see PHP code when you view source. • Runs as an Apache module – interpreter embedded in Apache run-time for speed. • Thousands of built-in functions, or create your own • Use php.net as a reference • Need to learn PHP basics before MySQL is useful to us

10. PHP, cont. • Quick example of dynamic things you can do without database - calc.php • It's all about variables - moving them around, tracking them... • Lots of database-specific functions built in.

11. Enable PHP • 1,000 Apache tweaks and configs you can do, but only one we have to do: • Edit /etc/httpd/httpd.conf (hidden from Tracker) and uncomment two PHP lines • sudo pico httpd.conf • Add addtype application/x-httpd-php .php • Restart Apache • Test with phpinfo();

12. MySQL • What is a relational database server? Hosts data sets and responds to queries from other apps. Relational:

13. What is SQL? • Structured Query Language • Cross-database industry standard way to communicate with databases • select * from contacts • select ID, name from contacts where state=CA • delete from contacts where ID > 497 • update contacts set lname=“Hacker” where ID=312

14. Installing MySQL • www.entropy.ch - Mark Liyanage • Run vanilla installer • Initialize database • cd /usr/local/mysql • sudo ls • sudo ./scripts/mysql_install_db • Run it • sudo chown -R /usr/local/mysql/* • sudo ./bin/safe_mysqld --user=mysql & • Install mysqlstartupitem.pkg from entropy.ch

15. MySQL Security • Set the root user password: • ./bin/mysqladmin/ -u root password mypass

16. Try it • ./bin/mysql -u root -p • > show databases; • test • mysql Enough brain surgery!

17. phpMyAdmin • Done with the command line • All-purpose, free, open source database administration tool: www.phpmyadmin.net • Not a replacement for custom apps • Can do anything you can do from the mysql console • Remote control

18. Install phpMyAdmin • Download from www.phpmyadmin.net in tar.gz format • Decompress on desktop, rename folder • Move phpmyadmin folder into ~/Sites • Very important: Password-protect! Open config.inc.php and set auth_type to http, enter root user and root password.

19. Database creation and admin

20. Create a database and tables • From Home, enter a name for db • Create new table on database foobar w/5 fields • Set first column to int, auto_increment, primary. This is your ID field, your key. • Set other columns to appropriate data types - varchar 16 • In your 2nd table, you’ll have a lookup column that will ref values in this table’s ID column.

21. Set permissions • Not root perms, but perms script will connect as • Click Home, Users • host=localhost • user=public (or similar), pass=youchoose • privs=select, update, insert, delete • Reload MySQL server! • Return and set grants for this user for this database

22. Connect to database from PHP • Create a dbconnect() function in a central locale. • Invoke from every script with: include ”/include/connect.php"; dbconnect(); • Try by itself - page should not error. • Now learn either PHP or DreamWeaver ;)

23. Moving Your Database • View dump schema • Select All, structure and data, save as File. Save with .sql extension • On remote host, use phpMyAdmin to create database with same name • Enter database, Browse to .sql file, click Go. • The important bit is to pay attention to permissions in scripts and in MySQL

24. More Information • Definitive book: Rasmus Lerdorf’s “Programming PHP” (O’Reilly) • www.php.net for function descriptions • www.phpbuilder.com - great forums • www.hotscripts.com - giant script library • www.mysql.com - Very detailed documentation • Once set up, you probably need more info on learning SQL than on MySQL itself. Google “sql tutorial” - there are lots.