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 issue 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.

//----------------------------------------------------------
// Globals.  Enter your user information here
//----------------------------------------------------------
$user       ="220a-xx";   // EDIT THIS LINE!
$passwd   = "xxxxxx";       // EDIT THIS LINE TOO!
$host        ="xgridmac.dyndns.org";
$database ="220a_example1";
$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";
  }
}

//----------------------------------------------------------
// 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 ---
$result = mysql_query( "use ". $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 ---
foreach( $lines as $line ) {
  $line = trim( $line );
  //--- skip blank lines ---
  if ( strlen( $line ) < 2 )
    continue;

  //--- display result ---
  print "\n\n=========================================================\n";
  $newLine = wordwrap( $line );
  print "query = $newLine\n";
  $result = mysql_query( $line );
  displayQueryResult( "", $result );
}

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

?>