CSC220 Lab 7 MySQL -- 2010

--D. Thiebaut 16:39, 27 October 2010 (UTC)

---

Reference Program

• Use the following program as reference for your own program.

<?php
// mysqlSkel.php
// D. Thiebaut

include 'accessinfo.php';

//--- connect to mysql server ---
$link = mysql_connect( $hostName, $userName, $password );
if ( ! $link ) 
  die( "Could not connect to server: " . $mysql_error() );

//--- select database ---
$db = mysql_select_db( $database, $link );
if ( ! $db ) 
  die( "Could not connect to database " . $database . ": "
       . mysql_error() );

//--- insert new user in table users ---
$newUser = "Joanna";
$query = sprintf( "INSERT INTO `users` (`userName`) VALUE ('%s')", $newUser );
$result = mysql_query( $query, $link );
if ( ! $result ) 
  printf( "Insertion of user %s unsuccessful\n", $newUser );

//--- list first row of table ---
$query = sprintf( "SELECT * from `users` LIMIT 1" );
$result = mysql_query( $query, $link );
if ( ! $result ) 
  die( "Invalid query (" . $query . "): " . mysql_error() );

if ( $row = mysql_fetch_array( $result, MYSQL_NUM ) ) {
  for ( $i=0; $i<count( $row ); $i++) 
    printf( "%s\t", $row[$i] );
  print "\n";
}

//--- close database ---
mysql_close( $link );

?>

Part 1

• create a file in your directory called accessinfo.php with the following contents (where you will replace the xxxx fields with appropriate values for you):

<?php

$hostName='xgridmac.xxxx.xxx';
$userName='220a-xx';
$password='xxxxxx';
$database='220a-xx';

?>

Question 1

• Write a php program that displays the whole contents of the table users in your database.

Question 2

• Once you have the code working, put the reading of the table in a function that can be called as follows:

  displayTable( 'users' );

i.e. where you pass the name of the table as a parameter to the function. When the function works, make it display all 3 tables

  displayTable( 'users' );
  displayTable( 'items' );
  displayTable( 'orders' );

Question 3

• Write a function that will insert new users into the table users. You do not need to make the table a parameter of the function.