Database integration

It is possible to connect a website with a database. Information stored in the database can then be displayed on a web page. Large websites often use a database and generate all pages 'on the fly' from the contents of the database rather than from individual HTML files.

On this server, you can use a database of the type MySQL. Use the following settings to get access to the database: Username = 'student', password = 'test', database-name = 's2012'. Create a table with the name sXXXX, where XXXX is your student number. This database is for testing purposes only, please delete your table when you don't need it any more. (We may delete all tables when they take too much space.)

You need to make a server-side program to connect to the database. It is recommended to make this program in the PHP language.

Here is an example showing how to use a database in a PHP program. the filename must end in .php:

<HTML>
<HEAD>
  <TITLE>MySQL test</TITLE>
</HEAD>
<BODY>

<?
// start of PHP code

/**************************************************************************
*                                                                         *
*    This code shows how to use a database                                *
*                                                                         *
**************************************************************************/

$mysql_username  = "student";        // username for database
$mysql_password  = "test";           // password for database
$mysql_database  = "s2012";          // name of database
$mysql_tablename = "s9999";          // name of your table in database

// establish connection
$dbLink = mysql_connect("", $mysql_username, $mysql_password);
if (!$dbLink) {
  print("connection to database failed!"); exit;}

//select database
$success = mysql_select_db($mysql_database, $dbLink);
if (!$success) {
  print("Error: " . mysql_error()); exit;}

// These are examples of what you can do with the database.
// To set the value of $action, call this script like:
// sqltest.php?action=1

switch ($action) {
case 1:
  // create table in database, define name and type of each field
  $result = mysql_query("create table $mysql_tablename (
  id INT NOT NULL auto_increment,
  name varchar(80),
  age int,
  phone char(12),
  PRIMARY KEY(id))");
  if (!$result) {
    print("Error: " . mysql_error()); exit;}
  break;

case 2:
  // delete table
  $result = mysql_query("drop table $mysql_tablename");
  if (!$result) {
    print("Error: " . mysql_error()); exit;}
  break;

case 3:
  // create a record
  $result = mysql_query("insert into $mysql_tablename values(0,'Agner','45','44805167')");
  if (!$result) {
    print("Error: " . mysql_error()); exit;}
  break;

case 4:
  // modify a record
  $result = mysql_query("update $mysql_tablename set age = '46', phone = '12345678' where name = 'agner' ");
  if (!$result) {
    print("Error: " . mysql_error()); exit;}
  break;

case 5:
  // delete a record
  $result = mysql_query("delete from $mysql_tablename where name = 'agner' ");
  if (!$result) {
    print("Error: " . mysql_error()); exit;}
  break;

case 6:
  // find a record
  $result = mysql_query("select * from $mysql_tablename where name = 'agner' ");
  if (!$result) {
    print("Error: " . mysql_error()); exit;}
  $row = mysql_fetch_array($result);
  print ( $row["name"] . " " . $row["phone"] . "<br>" );
  break;

case 7:
default:
  // list all records
  $result = mysql_query("select * from $mysql_tablename");
  if (!$result) {
    print("Error: " . mysql_error()); exit;}
  while ($row = mysql_fetch_array($result)) {
    print ( $row["name"] . " " . $row["phone"] . "<br>" );}
  break;
}

// end of PHP code
?>

</BODY>
</HTML>

SQL manual.

This page was last modified 2008-Dec-08