CSC220 Lab 7 MySQL -- 2010

From dftwiki3
Jump to: navigation, search

--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', $link );
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', $link  );
  displayTable( 'items', $link  );
  displayTable( 'orders', $link  );
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.

Part 2

  • Use the program below as a starting point, and create a self-loading form that will ask the user for a name and add it to the table of users. The form, when it reloads will display the contents of the table users on the browser.
<!--
formskel.php
D. Thiebaut
A skeleton form that contains 1 text field and
loads up the information in the database 
defined in the file accessinfo.php, whose
contents are show below:

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

<html>
<head>
</head>
<body>

<?php

//----------------------------------------------------------------
function openDBAddUserClose( $name ) {
  include 'accessinfo.php';

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

  //--- select database ---
  $db = mysql_select_db( $database, $link );
  if ( ! $db ) {
    printf( "Could not connect to database " . $database . ": "
    	 . mysql_error() );
    return 0;
  }
  //--- insert new name in table users ---
  $query = sprintf( "INSERT INTO `users` (`userName`) VALUE ('%s')", $name );
  $result = mysql_query( $query, $link );
  $retVal = 1;
  if ( ! $result ) {
    printf( "Insertion of user %s unsuccessful\n", $name );
    $retVal = 0;
  }

  //--- close database ---
  mysql_close( $link );
  return $retVal;
}

//----------------------------------------------------------------
function printForm( $name ) {
  printf(  '<form action="formskel.php" method="post">
       
         Name: <input type="text" name="name" VALUE="%s" SIZE="40">
         <input type="submit" value="Send">
         </form>', $name );
}

//----------------------------------------------------------------
function success( $name ) {
  printf ( "<h2>Success!</h2><br>%s has successfully been added\n",
	   $name );
}

//----------------------------------------------------------------
function error( $name ) {
  printf( "<h3><font color=\"red\">There was an error inserting %s in database</font></h3>\n",
	  $name );
}

//----------------------------------------------------------------
//                            M A I N 
//----------------------------------------------------------------
//--- get user input ---
$name = $_REQUEST[ 'name' ];

//--- if set, add it to database ---
if ( !empty( $name ) ) {
  $retVal = openDBAddUserClose( $name );
  if ( $retVal ) 
    success( $name );
  else 
    error( $name );
}

//--- show empty form for getting next input---
unset( $name );
printForm( $name );

?>

</body>
</html>










Retrieved from "http://dominiquethiebaut.com/dftwiki3/index.php?title=CSC220_Lab_7_MySQL_--_2010&oldid=8948"