Transcript amp - scot hacker`s foobar blog

Get AMP’d:
Building PHP/MySQL
Web Databases
Under OS X
Scot Hacker
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
AMP
Ideal partnership
Apache (web server)
 MySQL (database server)
 PHP (scripting language) ((glue))

Independent / Interdependent
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.
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.

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.
Manual vs. Automatic




DreamWeaver can take a lot of PHPprogramming 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.
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/

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
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.

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();


MySQL

What is a relational database server? Hosts data sets
and responds to queries from other apps. Relational:
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
Installing MySQL



www.entropy.ch - Mark Liyanage
Run vanilla installer
Initialize database




Run it



cd /usr/local/mysql
sudo ls
sudo ./scripts/mysql_install_db
sudo chown -R /usr/local/mysql/*
sudo ./bin/safe_mysqld --user=mysql &
Install mysqlstartupitem.pkg from entropy.ch
MySQL Security

Set the root user password:

./bin/mysqladmin/ -u root password mypass
Try it
./bin/mysql -u root -p
 > show databases;

test
 mysql

Enough brain surgery!
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

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.

Database creation and admin
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.
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
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 ;)

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
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.