Location: PHPKode > projects > Atom PhotoBlog > apbClasses/MySQL.class.php
<?php
   /*
   MySQL.class.php - Distributed with Atom PhotoBlog
   Copyright (C) 2007 by Sascha Tayefeh

   This program is free software; you can redistribute it and/or
   modify it under the terms of the GNU General Public License
   as published by the Free Software Foundation; either version 2
   of the License, or (at your option) any later version.

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License for more details.

   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software
   Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.

   There is an stand-alone version of this class. Visit my homepage at
   http://www.tayefeh.de to find a detailed description of this class.

   PARAMETERS (alphabetically sorted):
      public $mysqlDb;			// The mysql-database-string
      public $mysqlLogin;		// The mysql-login-name-string
      public $mysqlPasswd;		// The mysql-password-string
      public $mysqlServer;		// The mysql-server-string

      protected $mysqlDbPointer;
      protected $mysqlPointer;

 
   METHODS:
      public function mysqlCheckConnection()  		// Print some information about the server.
      public function mysqlConnect()			// Connect. You MUST du this before calling any other member
      public function mysqlDisconnect()			// Disconnect manually
      public function mysqlEditTable($table)		// This is a pending method. Currently, it just prints the mysql-$table to html.
      public function mysqlQuery($query) 			// A general purpose member to perform all sort of queries
      public function mysqlStoreRow($rowData,$table)	// Proceeds an assoc. array and stores the $row into $table
      public function mysqlUpdateRow($rowData,$table,$where="") // Proceeds an assoc. array and updates the $row of $table
      public function mysqlViewProperties()		// Print the properties of this class.
      private function mysqlCheckDb($database="")		// Check connection to the database and print some stuff
      private function mysqlInitDb($database="")		// Create (or delete and create) the database
      private function mysqlSelectDb($database="")		// if (!$database) $database=$this->mysqlDb;
   */

   class MySQL
   {
      public $mysqlServer;
      public $mysqlDb;
      public $mysqlLogin;
      public $mysqlPasswd;

      protected $mysqlPointer;
      protected $mysqlDbPointer;

      public function mysqlConnect()
      {
	 if (isset($this->mysqlServer) 
	 && isset($this->mysqlLogin) 
	 && isset($this->mysqlPasswd) 
	 && isset ($this->mysqlDb))
	 {
	    $this->mysqlPointer = mysql_connect(
	       $this->mysqlServer,
	       $this->mysqlLogin,
	       $this->mysqlPasswd
	    )
	    or die("<p class=\"error\">*** ERROR: Connection to MySQL-Server failed, because: " . mysql_error($this->mysqlPointer))."</p>";
	 } else die("<p class=\"error\">*** ERROR: Connection to MySQL-Server failed, because some parameters were not set! You must initialize this classes properties mysqlServer, mysqlDb, mysqlLogin, and mysqlPasswd </p>");
	 $this->mysqlSelectDb();
      }

      public function mysqlDisconnect()
      {
	 mysql_close($this->mysqlPointer);
      }

      public function mysqlCheckConnection()
      {
	 echo "<ul><li>Connection to ".$this->mysqlServer." successful (".$this->mysqlPointer.")</li>";

	 if(!$this->mysqlCheckDb()) {
	    echo "<li>Database ".$this->mysqlDb." does not exist. You're encouraged to perform initialization.</li>";
	 } else {
	    echo"<li>Database ".$this->mysqlDb." exists.</li>";
	 }
	 echo"</ul>";

	 echo "<hr><h4>Information</h4><ul>";
	 echo "<li>Client Version: ".mysql_get_client_info()."</li>";
	 echo "<li>Server Version: ".mysql_get_server_info($this->mysqlPointer)."</li>";
	 echo "<li>Protocol Version: ".mysql_get_proto_info($this->mysqlPointer)."</li>";
	 echo "<li>Host: ".mysql_get_host_info($this->mysqlPointer)."</li>";
	 foreach ( explode('  ', mysql_stat($this->mysqlPointer))  as $buffer) {
	    echo "<li>".$buffer."</li>";
	 }
	 echo "</ul>";
      }

      public function mysqlViewProperties()
      {
	 echo "<p>Current MySQL-Server data:</p>";
	 echo "<ul>";
	 echo "<li><strong>Host: </strong>".$this->mysqlServer."</li>";
	 echo "<li><strong>Database: </strong>".$this->mysqlDb."</li>";
	 echo "<li><strong>Login: </strong>".$this->mysqlLogin."</li>";
	 echo "<li><strong>Password: </strong>".$this->mysqlPasswd."</li>";
	 echo "</ul>";
      }

      public function mysqlQuery($query)
      {
	 $buffer = mysql_query($query,$this->mysqlPointer);
	 if (!$buffer) {
	    echo '<p class="warning">*** WARNING: '.mysql_error($this->mysqlPointer).'</p>';
	    return 0;
	 }
	 else {
	    return $buffer;
	 }
	 mysql_free_result($buffer);
      }

      private function mysqlCheckDb($database="") // If db exists, return TRUE
      {
	 if (!$database) $database=$this->mysqlDb;
	 return mysql_select_db($database,$this->mysqlPointer);
      }

      private function mysqlSelectDb() // Actually SELECT the Database or die
      {
	 $this->mysqlDbPointer = mysql_select_db($this->mysqlDb,$this->mysqlPointer)
	 or die('<p class=\"error\">*** ERROR: Could not select database: '.mysql_error($this->mysqlPointer).'</p>');
      }

      // If database does not exist, initialize (but do not drop, if already exists). Returns 1, if created
      // Must have an connection opened
      private function mysqlInitDb()
      {
	 $done=0;
	 if(!$this->mysqlCheckDb($this->mysqlDb)) {
	    $this->mysqlQuery("CREATE DATABASE IF NOT EXISTS ".$this->mysqlDb);
	    $done=1;
	 }
	 return $done;
      }

      public function printColumns($tableName)
      {
	 $result=$this->mysqlQuery("SHOW COLUMNS FROM ".$tableName.";");
	 echo "<table cellspacing=\"0\" cellpadding=\"0\" border=1>";
	 $line = mysql_fetch_array($result, MYSQL_ASSOC);
	 $keys=array_keys($line);
	 echo "<tr>";
	 foreach ($keys as $key) {
	    echo "<th>$key</th>";
	 }
	 echo "</tr>";
	 while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
	    echo "<tr>";
	    foreach ($line as $col_value)
	    {
	       echo "<td>$col_value</td>";
	    }
	    echo "</tr>";
	 }
	 echo "</table>\n";
      }

      public function mysqlUpdateRow($rowData,$table,$where="")
      {
	 $querystring  = "UPDATE";
	 $querystring .= " `".$table."`  set ";
	 foreach($rowData as $key=>$value)
	 {
	    $querystring .= " $key=\"$value\", ";
	 }

	 $querystring=substr_replace($querystring,"",strrpos($querystring,","));
	 $querystring .= " WHERE $where;";
	 //      echo "<p>".$querystring."</p>";

	 $buffer = $this->mysqlQuery($querystring);
	 return;
      }

      public function mysqlStoreRow($rowData,$table)
      {
	 $keys= array_keys($rowData);
	 $j=sizeof($keys);
	 $querystring  = "INSERT INTO ";
	 $querystring .= "`".$table."` ";
	 $querystring .= "( ";
	 $i=1;
	 foreach($keys as $key)
	 {
	    $querystring .= $key;
	    /*
	    mySQL-Syntax prohibits terminal "," 
	    */
	    if($i<$j) $querystring .= ", ";
	    $i++;
	 }
	 $querystring .= " ) VALUES (";
	 $i=1;
	 foreach($rowData as $data)
	 {
	    $querystring .= "\"".$data."\"";
	    if($i<$j) $querystring .= ", ";
	    $i++;
	 }
	 $querystring .= ");";
	 //echo "<p>".$querystring."</p>";

	 $buffer = $this->mysqlQuery($querystring);
      }

      // This member is pending :-(
	 public function mysqlEditTable($table)
	 {
	    $header=TRUE;
	    $buffer=$this->mysqlQuery("SELECT * FROM  `".$table."`;")
	    or die("<p class=\"error\">*** ERROR: Reading User-Table failed</p>");
	    $n_entries = mysql_num_rows($buffer);
	    echo "<p>Number of entries: ".$n_entries."</p>";
	    echo "<table class=\"standard\">";
	    while ($row = mysql_fetch_array($buffer,MYSQL_ASSOC))
	    {
	       // Print header once
	       if ($header)
	       {
		  $header=FALSE;
		  echo "<tr class=\"standard\">";
		  $keys = array_keys($row);
		  foreach ($keys as $key)
		  {
		     echo "<th class=\"standard\">".$key."</td>";
		  }
		  echo "</tr>";
	       }

	       // Print the contents in any case
	       echo "<tr class=\"standard\">";
	       foreach ($row as $column)
	       {
		  echo "<td class=\"standard\">".$column."</td>";
	       }
	       echo "</tr>";
	    }
	    echo "</table>";
	    mysql_free_result($buffer);
	 }
   }
Return current item: Atom PhotoBlog