6: Section Using MySQL and PHP (2/29/08)
phpMyAdmin: An Overview
phpMyAdmin is a GUI front-end for administering MySQL databases via a web browser. We have phpMyAdmin installed on the 230 server; check it out at http://info230.cs.cornell.edu/phpMyAdmin. Your login information is the same as it is for the 230 site. All the common commands you might wish to run on a MySQL server can be run via phpMyAdmin. You can select databases with the pulldown at left, and then (when you've got >= 1 table in your database) select which table to work with by clicking on a link below the pulldown. Let's take a look at the top level navigation of phpMyAdmin: Structure: Lets you create and update tables in your database. Browse: Basically, a SELECT * on your table. SQL: A textarea where you can enter SQL commands, as in homework 1. Search: Run SELECT queries using a form interface for setting WHERE or LIKE clauses. Insert: Enter data into your table. Import: Upload a file full of SQL commands and run them all on the database. Export: Create backups of your database tables and their content as SQL statements. Empty: Remove all data from a given table. Drop: Delete the table (obviously also removes all its data). Operations: Ignore this for now.
Designing a Simple Schema
Together, we're all going to design a simple one-table schema to describe an entity. That entity is 'animals'. In addition to the primary key field, we need 2-4 other properties of animal. Things like num_legs (3), sound (meeeeow), and name (rhino) are good choices. Make sure you're comfortable writing the schema in the following format (* represents the primary key members):
Animals (*aid: INT, name, num_legs, sound)
Implementing the Schema in phpMyAdmin
Using what you've learned about the Structure tab, go implement the section's Animals table using phpMyAdmin. Remember to set the animal ID field to 'auto increment' and to mark it as a primary key. Note that the SQL that phpMyAdmin is using to talk to MySQL is displayed after you hit submit. Next, head over to the Insert tab and convince yourself you can add data to your animals table. Try adding duplicate rows, or adding empty elements, and see what happens. Note that the SQL is again being output for you to see.
Reviewing PHP MySQL Functions
Connecting to a MySQL Database
Okay, we're going to go ahead and create a PHP file that will connect to their brand new animals database and issue some queries to it. Have everyone follow along:
$link = mysql_connect('localhost', 'myusername', 'mypassword'); if(!$link){ die('Error connecting to database, please refresh the page.'); } mysql_select_db('name_of_table', $link);
Stress the security issues It's here. normally good practice to put your DB login info outside the webroot in a different file. On the 230 server, putting it outside the webroot is not really an option, so we'll have to settle for just putting it in a configuration file. Stress that's important not to do something like call your login include file `db_login.inc`. Although PHP will load the file just fine, `.inc` is not parsed by PHP normally. When someone views `db_login.inc` in their browser, they see your login info!
//in password.php <? define(MYSQL_PASS, 'mypassword'); ?> //where password is needed <? require('password.php'); $link = mysql_connect('localhost', 'myusername', MYSQL_PASS); if(!$link){ die('Error connecting to database, please refresh the page.'); } mysql_select_db('info230_SP08_myusername', $link); ?>
We'll leave the 'meat' of the application empty for now just to make sure the connection works. Let's make sure we go ahead and close the connection when we're finished.
mysql_close();
SELECT Queries
Of course, we can query the database for some information:
$query = "SELECT * FROM Animals" ; $resource = mysql_query($query); while($animalsarray = mysql_fetch_array($resource, MYSQL_ASSOC)){
print("The $animalsarray['name'] says $animalsarray['sound']"); }
You may want to `print_r($animalsarray)` to show what the array contains. It'd be useful to show the different fetch types too. ( mysql_fetch_array docs)
INSERT/UPDATE/DELETE Commands
And here's how we modify the database. You might want to show how mysql_query returns different things depending on if it's a SELECT or INSERT/UPDATE/DELETE.
$query = "UPDATE Animals SET sound = '".$_POST['sound']."' WHERE aid = '".$_POST['aid']."'" ; mysql_query($query);
Again, this is a good place to mention security. Doing the above in production code is **bad**. Always escape data, never interpolate _POST information, etc.
Advanced / Other Information
PHP's PDO Libraries
Dean happens to be on a [PDO] kick lately, so he thinks it'd be cool to mention it in section. PDO is a database abstraction layer for PHP. It lets you easily connect to different database types (MySQL, SQLite, Postgres, etc) as well as giving you an OO interface to work with the results. You can do prepared statements, transactions and more all very nicely. You can even define PHP classes and have PDO automatically create instances of them from the results:
class Animal { public $name; public $type; public $num_legs; } # elsewhere $stmt = $dbh->query('SELECT * FROM animals'); $animals = $stmt->fetchAll(PDO::FETCH_CLASS, 'Animal'); # animals is now an array of Animal objects!