CSC220 Lab 6 MySQL -- 2010

--D. Thiebaut 01:54, 20 October 2010 (UTC)

---

Create a New Table

• Use PhpMyAdmin on hadoop110 or hadoop101 to create a new table. Call it lab6
  • Create a integer field, auto increment, primary index, called Id
  • Create a varchar field, with 40 characters, utf8-general-ci for collation, called Name

• Create the following php program in your main directory (no need for it to be in your public_html directory)

<?php

// fillTable.php
// D. Thiebaut
// fills a table with $N random strings
// The table name is $tableName
//

//----------------------------------------------------------
// Globals.  Enter your user information here
//----------------------------------------------------------
$user        ="220a-xx";   // EDIT THIS LINE!
$passwd      = "xxxxxxxx";       // EDIT THIS LINE TOO!
$host        ="xgridmac.dyndns.org";
$database    = "220a-xx";   // EDIT THIS LINE AS WELL!

$N = 500000;
$tableName = "lab6";


//----------------------------------------------------------
// create a random word of 15 characters
//----------------------------------------------------------
function randomWord() {
  $validchars = "abcdfghjkmnpqrstvwxyz";
  $word  = "";
  for ( $i=0; $i<15; $i++ ) 
     $word .= substr( $validchars, rand(0, strlen($validchars)-1), 1 );
  return $word;
}

//----------------------------------------------------------
// Main Program
//----------------------------------------------------------
//--- connect to DB ---
mysql_connect( $host, $user, $passwd ) or die( "Unable to connect to db\n\n" );
print "==> Connected to database\n";

//--- select database ---
$result = mysql_select_db( $database );

//--- Insert  $N items in table---
for ( $i=0; $i < $N; $i++ ) {
  $qty  = rand( ); // random number
  $name = randomWord(); //. strval( rand( ) );
  $query = "INSERT INTO  `$tableName` (`Name`)  VALUES ( '$name' )";
  $result = mysql_query( $query );

  //--- give feedback to the user ---
  ( $i % 1000 != 0 )  or print $i . "\n";
}

//--- done.  close connection ---
mysql_close();

?>

• Edit the program to reflect your user name and password.
• Run the program:

  php fillTable.php

• verify that it outputs the number of items it is inserting in the table. Depending on the time of day, and how many people are inserting in the database, this may take a minute or two.