CSC220 testSql.php

--D. Thiebaut 21:28, 13 October 2010 (UTC)

Edited by/on --D. Thiebaut 11:50, 18 October 2010 (UTC)
Edited by/on --D. Thiebaut 23:44, 28 October 2010 (UTC)

---

// testSql.php
// D. Thiebaut
// A simple php program that reads a file containing sql queries
// and issues them, one at a time to the database server on 
// xgridmac.
// The queries are executed, their status displayed (successful,
// unsuccessful), and the result of their output (if any) is also
// displayed on the screen.
// Modification 10/18/10: Julia Burch
// $result = mysql_select_db($database);


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

//----------------------------------------------------------
// display the result of the query
//----------------------------------------------------------
function displayQueryResult( $caption, $result ) {
  //print "\n---------------------------------------------------------\n";
  print "===> Result of query: ". $caption . "\n";
  //var_dump( $result );
  if ( $result === True ) {
    print "query successful\n";
    return;
  }
  if ( $result ===False ) {
    print "query unsuccessful\n" ;
    return;
  }

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

//----------------------------------------------------------
// removeComments:
//----------------------------------------------------------
function removeComments( $s ) {
   $good = explode( "--", $s );
   if ( count( $good )==0 )
      return "";
   return $good[0];
}

//----------------------------------------------------------
// Run file of SQL queries on MySQL host and report results
//----------------------------------------------------------

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

//--- select database ---
// Modification J. Burch 10/18/10
$result = mysql_select_db($database);


//--- get mysql file ---
$oneLine = file_get_contents( $sqlFile );

//--- remove comments.  Comments start a line with '--' ---
$lines = explode( "\n", $oneLine );
//$newLines = array();
//foreach ( $lines as $line ) {
//  if ( strpos( $line, "--" )===False || strpos( $line, "--" )!=0 )
//    $newLines[] = $line;
//}

//--- split at ; ---
//$oneLine = implode( " ", $newLines );
//$lines = explode( ";", $oneLine );

//--- run each query on the SQL server ---
$currentQuery = "";
foreach( $lines as $line ) {
  printf( $line . "\n" );
  $line = trim( $line );
  $queryPart = removeComments( $line );
  if ( strlen( $queryPart )!= 0 )
     $currentQuery .= $queryPart;
     if ( strchr( $queryPart, ";" )!== False ) {
       //print "\n\n=========================================================\n";
       $result = mysql_query( $currentQuery );
       displayQueryResult( "", $result );
       $currentQuery = "";
       printf( "\n" );
     }
  
}

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

?>