CSC220 testSql.php

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

---

 <?php
// 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);
// Version 4 (10/30/10)

//----------------------------------------------------------
// Globals.  Enter your user information here
//----------------------------------------------------------
include 'accessinfo.php';    // contains $user, $password, $host, $database, and $sqlFile


//----------------------------------------------------------
// 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: " . mysql_error() . "\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 ---
$link = mysql_connect( $host, $user, $passwd );
if ( !$link ) 
   die( "Unable to connect to db\n" . mysql_error() . "\n" );
print "==> Connected to server\n";

//--- select database ---
// Modification J. Burch 10/18/10
$result = mysql_select_db($database);
if ( ! $result ) 
   die( "unable to select database " . $database . "\n" . mysql_error() .  "\n" );

print "--> connected to " . $database . "\n";

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

//--- remove comments.  Comments start a line with '--' ---
$lines = explode( "\n", $oneLine );

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

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

?>