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:
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.