Introduction to PHP and MySQL

Installation

To manually install MySQL® use these web sites:

• http://dev.mysql.com/doc/mysql-linuxunix-excerpt/5.1/en/index.html
• http://dev.mysql.com/doc/mysql-macosx-excerpt/5.1/en/index.html
• http://dev.mysql.com/doc/mysql-windows-excerpt/5.1/en/index.html

Or use this tool:

• http://www.apachefriends.org/en/xampp.html

Connecting to a database

To connect to a database use the following code.

$link = mysql_connect('localhost','username','password');
mysql_select_db('database', $link);

// or alternatively

mysql_connect('localhost','username','password');
mysql_select_db('database');

The first function mysql_connect connects to the database, the arguments needed are the host address, the username and the password for the database. Secondly the database to be queried is selected.

Querying data

The following code will get all the rows from the table mytable.

$query = mysql_query('SELECT * FROM mytable');
while ($output = mysql_fetch_assoc($query))
   echo $output['name'];

The mysql_fetch_assoc function returns a map with the keys being the name of the fields and the value begin the current value of that field. Every time it is called a new map is returned with the next row contents until no more are left and will return FALSE when no more rows are left.

Updating data

The UPDATE command can be used to update database rows.

$query = mysql_query("UPDATE mytable SET news='$news', date='$date' WHERE id = '1'");

This example changes the value of the fields news and date in the row that has the id value of 1.

Deleting data

Deleting data is done again using simple queries.

$query = mysql_query("DELETE FROM mytable WHERE id = '1'");

All the rows in the table mytable that have an id of 1 are deleted in this example.

Database errors

Many of these database functions return false if there is an error, using conditionals you can alert the user if there has been a database error.

mysql_connect('localhost','username','password');
if(!mysql_select_db('database'))
   echo mysql_error().' ('.mysql_errno().')';

The example above prints the error message and error number if a database cannot be selected.