Location: PHPKode > scripts > Dynamic Configuration > class_mysql.php
<?php
class mySQL_Config {
	//----------------------------------------------------------------------------
	//
	// mysql.php
	// rev. 2010-08-05, Mark Kintigh
	//
	//----------------------------------------------------------------------------
	//
	// This file contains functions used to access the configurations that are
	// stored within a table within a mySQL database table.
	//
	// FUNCTIONS
	// ---------
	// FixQuotes($src, $fromSQL = false)
	//      * Changes quotes and other characters within a given string to / from
	//        a SQL friendly version.
	//
	// setConnection($host="127.0.0.1", $user="root", $pswd="", $dbname="",
	//		$cfgtable="config", $setting="cfg_setting", $value="cfg_value",
	//		$openit=1)
	//		* This function will allow all of the database connection information
	//        to be set at once.  If the last optional parameter is true (non-
	//        zero) it will automatically try to open the connection to the
	//        database.
	//
	// checkConfigTable()
	//		* This function will check to see of the configuration table exists.
	//		  If the table was not found it attempts to create it.  This will
	//		  return true if the table exists or false if it did not exist.
	//		* This can be used in after having the class connect to the database.
	//		  If this function returns false you can have default values added
	//		  into the system.  For instance:
	//
	//			$myconn = new mySQL_Config();
	//			$myconn->setConnection("host", "user", "password", "database");
	//			if(!$myconn->checkConfigTable()) {
	//				$myconn->config_set("appname","Test Site");
	//				$myconn->config_set("version","1.23");
	//				  :
	//			}
	//
	// createConfigTable()
	//		* This fuction will attempt to CREATE the configuration table using
	//		  the defined table and field names within the currently open
	//        database.
	//
	// dropConfigTable()
	//		* This function will attempt to DROP the configuration table from
	//        the currently open database.
	//
	// open()
	//      * Opens a connection to the mySQL server defined within this class.
	//        It also selects the database defined within this class.
	//
	// close()
	//      * Closes the connection to the mySQL server.
	//
	// config_get($setting, $default="")
	//      * Returns the given value from the configuration table.  If the given
	//        setting does not exist, an optionally given default value will be
	//        returned.
	//
	// config_exists($setting)
	//      * Returns true / false after checking the configuration table for the
	//        existance of a given setting.
	//
	// config_set($setting, $value = "")
	//      * Adds or updates a setting within the configuration table.
	//
	// config_remove($setting)
	//      * Removes a setting from the configuration table.
	//
	// config_get_names()
	//      * Returns an array that contains all of the setting names within the
	//        table.
	// config_reset_table($recreate = 1)
	//      * Removes ALL of the entries within the table.  This will be done by
	//		  either dropping the table and recreating it (optional parameter is
	//		  true) or using the SQL DELETE command (optional parameter is false).
	//
	// config_get_all()
	//      * Returns an array of arrays containing all of the settings and values
	//        that are stored within the table.
	//
	//----------------------------------------------------------------------------
	//
	// The next 4 variables contain the base information.
	//
	//----------------------------------------------------------------------------
	var $mysql_host = "127.0.0.1";
	var $mysql_user = "root";
	var $mysql_password = "";
	var $mysql_database = "";
	/*
	 * The configuration table that is expected should follow this example:
	 *
	 	CREATE TABLE config (
	 	  cfg_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	 	  cfg_setting VARCHAR(128),
	 	  cfg_value TEXT,
	 	  PRIMARY KEY (`cfg_id`)
	 	)
	 *
	 * As you can see, this table consists of a primary key, which is basically
	 * ignored, the name of the setting and its value, stored as a text field.  I
	 * know, text fields can take up more space; however, this will allow you to
	 * hold more information and prevent you from being limited to what can or
	 * cannot be stored within the individual configurations.
	 */
	//
	// This defines the name of the table within your database that contains the
	// configurations.
	//
	var $mysql_config_table = "config";
	//
	// The next 2 setting relate to the configuration table.  These define the
	// field names that you used to store the setting name and the value for the
	// setting.  By doing this it makes the system more dynamic.
	//
	var $mysql_config_table_setting = "cfg_setting";
	var $mysql_config_table_value = "cfg_value";
	//----------------------------------------------------------------------------
	//
	// Miscellaneous configurations, see each one for details
	//
	//----------------------------------------------------------------------------
	//
	// This definition will tell the system if the user is allowed to add new
	// configuration settings into the table (true = yes they can; false = no).
	//
	var $mysql_config_allow_new = true;
	//
	// Internal handle to the open database
	//
	var $my_dbconnection;
	//----------------------------------------------------------------------------
	//
	// Utility function that will replace single quotes, double quotes, carriage
	// returns, and carriage return / line feeds with "&#__;" ASCII values so that
	// there will be not SQL errors.
	//
	// If the second, optional parameter is FALSE (default) this function will
	// return the replaced string.  If the second parameter is TRUE then it
	// reverses the replacement procedure.
	//
	//----------------------------------------------------------------------------
	function FixQuotes($src, $fromSQL = false) {
		if($fromSQL) {
		   $s = str_replace("'","&#39;",trim($src));
		   $s = str_replace('"',"&#34;",$s);
		   $s = str_replace("\n","&#13;",$s);
		   $s = str_replace("\r","&#12;",$s);
		} else {
		   $s = str_replace("\\'","'",trim($src));
		   $s = str_replace("&#39;","'",$s);
		   $s = str_replace("&#34;","\"",$s);
		   $s = str_replace("&#13;","\n",$s);
		   $s = str_replace("&#12;","\r",$s);
		}
	   return $s;
	}
	//----------------------------------------------------------------------------
	//
	// Single function used to set all of the database information for the class.
	// If $openit is true (non-zero) then this function will attempt to open the
	// connection to the database.
	//
	//----------------------------------------------------------------------------
	function setConnection($host="127.0.0.1", $user="root", $pswd="", $dbname="",
		$cfgtable="config", $setting="cfg_setting", $value="cfg_value",
		$openit=1) {
		//
		// Make sure the internal connection is closed
		//
		$this->close();
		//
		// Set the connection information and the database name
		//
		$this->mysql_host = trim($host);
		$this->mysql_user = trim($user);
		$this->mysql_password = trim($pswd);
		$this->mysql_database = trim($dbname);
		//
		// Define the configuration table's information
		//
		$this->mysql_config_table = trim($cfgtable);
		$this->mysql_config_table_setting = trim($setting);
		$this->mysql_config_table_value = trim($value);
		//
		// Open the connection?
		//
		if($openit) {
			//
			// Yes, return the open attempt's result
			//
			return $this->open();
		}
	}

	//----------------------------------------------------------------------------
	//
	// This function wll check the current database for the configuration table.
	// If the table is not found it will attempt to create it and return false;
	// otherwise, it will return true.
	//
	//----------------------------------------------------------------------------
	function checkConfigTable() {
		$ret = true;
		//
		// Query the current database to see if the given table exists
		//
		$rs = mysql_query("SHOW TABLES LIKE '" . $this->mysql_config_table . "';",
			$this->my_dbconnection);
		//
		// If no rows were found then the table does not exist
		//
		if(mysql_num_rows($rs)==0) {
			$this->createConfigTable();
			$ret = false;
		}
		mysql_free_result($rs);
		return $ret;
	}

	//----------------------------------------------------------------------------
	//
	// Attempt to create the configuration table within the given database
	//
	//----------------------------------------------------------------------------
	function createConfigTable() {
		//
		// Dynamically create the mySQL CREATE statement for the table
		//
		$sql = "CREATE TABLE %TABLE% ("
			. "cfg_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, "
			. "%SETTING% VARCHAR(128), "
			. "%VALUE% TEXT, "
			. "PRIMARY KEY (`cfg_id`))";
		$sql = str_replace("%TABLE%", $this->mysql_config_table, $sql);
		$sql = str_replace("%SETTING%", $this->mysql_config_table_setting, $sql);
		$sql = str_replace("%VALUE%", $this->mysql_config_table_value, $sql);
		//
		// Execute the CREATE statement
		//
		mysql_query($sql, $this->my_dbconnection);
		if(mysql_errno($this->my_dbconnection)) {
			echo "<font color=\"red\">Failed to create to the table \"</font>"
				. $this->mysql_config_table
				. "<font color=\"red\">\" on the host.</font><hr>"
				. mysql_error($this->my_dbconnection);
		}
	}
	
	//----------------------------------------------------------------------------
	//
	// Attempt to drop the configuration table within the given database
	//
	//----------------------------------------------------------------------------
	function dropConfigTable() {
		//
		// Dynamically create the mySQL DROP statement for the table
		//
		$sql = "DROP TABLE " . $this->mysql_config_table . ";";
		//
		// Execute the CREATE statement
		//
		mysql_query($sql, $this->my_dbconnection);
		if(mysql_errno($this->my_dbconnection)) {
			echo "<font color=\"red\">Failed to drop to the table \"</font>"
				. $this->mysql_config_table
				. "<font color=\"red\">\" on the host.</font><hr>"
				. mysql_error($this->my_dbconnection);
		}
		
	}
	//----------------------------------------------------------------------------
	//
	// Function that will use the above information to try to connect to the mySQL
	// server.  Makes is easier to open the server connection and select the
	// database without having to know the define() names.
	//	 
	//----------------------------------------------------------------------------
	function open() {
		//
		// Try to connect to the host or die trying
		//
		$this->my_dbconnection = mysql_connect($this->mysql_host,
			$this->mysql_user, $this->mysql_password) or
			die("<font color=\"red\">Failed to connect to the server \"</font>"
				. $this->mysql_host
				. "<font color=\"red\">\".</font><hr>"
				. mysql_error($this->my_dbconnection));
		//
		// Try to select the database on the server or die trying
		//
		mysql_select_db($this->mysql_database) or
			die("<font color=\"red\">Failed to connect to the database \"</font>"
				. $this->mysql_database
				. "<font color=\"red\">\" on the host.</font><hr>"
				. mysql_error($this->my_dbconnection));
		//
		// Return the database connection's handle
		//
		return $this->my_dbconnection;
	}

	//----------------------------------------------------------------------------
	//
	// Function that closes the internal handle to the mySQL server
	//
	//----------------------------------------------------------------------------
	function close() {
		if($this->my_dbconnection) mysql_close($this->my_dbconnection);
	}	
	//----------------------------------------------------------------------------
	//
	// Function to make it easier to read the settings within the configuration
	// table.  By using this you do not need to know the above define() names
	// to access the settings.
	//
	//----------------------------------------------------------------------------
	function config_get($setting, $default="") {
		//
		// Build the SQL statement to execute
		//
		$sql = "SELECT * FROM " . $this->mysql_config_table
			. " WHERE " . $this->mysql_config_table_setting . " = '"
			. trim($setting) . "' LIMIT 1;";
		//
		// Try to execute the query or display an error message if it fails
		//
		$rs = mysql_query($sql, $this->my_dbconnection) or
			die("<font color=\"red\">Failed to execute query</font><hr>"
				. "$sql<hr>" . mysql_error($this->my_dbconnection));
		//
		// Try to read an associtive array with the given setting name
		//
		if($data = mysql_fetch_assoc($rs)) {
			//
			// There is an entry, so return the current value
			//
			$ret = $this->FixQuotes(
				$data[$this->mysql_config_table_value] . "",
				true);
		} else {
			//
			// No entry was found, so return the default value
			//
			$ret = $default;
		}
		//
		// Free the SQL results
		//
		mysql_free_result($rs);
		//
		// Return the value
		//
		return $ret;
	}
	
	//----------------------------------------------------------------------------
	//
	// Function used to check to see if a given setting exists within the
	// configuration table.
	//
	//----------------------------------------------------------------------------
	function config_exists($setting) {
		//
		// Build the SQL statement to execute
		//
		$sql = "SELECT * FROM " . $this->mysql_config_table
			. " WHERE " . $this->mysql_config_table_setting . " = '"
			. trim($setting) . "' LIMIT 1;";
		//
		// Try to execute the query or display an error message if it fails
		//
		$rs = mysql_query($sql, $this->my_dbconnection) or
			die("<font color=\"red\">Failed to execute query</font><hr>"
				. "$sql<hr>" . mysql_error($this->my_dbconnection));
		//
		// Try to read an associtive array with the given setting name
		//
		if($data = mysql_fetch_assoc($rs)) {
			//
			// There is an entry, so return the current value
			//
			$ret = true;
		} else {
			//
			// No entry was found, so return the default value
			//
			$ret = false;
		}
		//
		// Free the SQL results
		//
		mysql_free_result($rs);
		//
		// Return the value
		//
		return $ret;
	}
	
	//----------------------------------------------------------------------------
	//
	// Function used to INSERT / UPDATE a value within the configuration
	//
	//----------------------------------------------------------------------------
	function config_set($setting, $value = "") {
		//
		// If the value already exists....
		//
		if($this->config_exists($setting)) {
			//
			// UPDATE the key's value
			//
			$sql = "UPDATE " . $this->mysql_config_table
				. " SET " . $this->mysql_config_table_value . " = '"
					. $this->FixQuotes($value) . "'"
				. " WHERE " . $this->mysql_config_table_setting . " = '"
					. trim($setting)
				. "' LIMIT 1;";
		} else {
			//
			// Since it has not been set yet, INSERT the new key / value
			//
			$sql = "INSERT INTO " . $this->mysql_config_table . "("
					. $this->mysql_config_table_setting . ", "
				. $this->mysql_config_table_value . ") VALUES ("
					. "'" . trim($setting) . "', '"
				. $this->FixQuotes($value) . "');";
		}
		//
		// Execute the SQL statement
		//
		mysql_query($sql, $this->my_dbconnection) or
			die("<font color=\"red\">Failed to execute query</font><hr>"
				. "$sql<hr>" . mysql_error($this->my_dbconnection));
		//
		// Check how many rows were affected
		//
		if(mysql_affected_rows($this->my_dbconnection)<1) {
			//
			// <1 row means nothing was done, return FALSE
			//
			return false;
		} else {
			//
			// Something was done, so return TRUE
			//
			return true;
		}
	}

	//----------------------------------------------------------------------------
	//
	// Function used to delete a given setting from the table
	//
	//----------------------------------------------------------------------------
	function config_remove($setting) {
		//
		// Check to see if the configuration exists before doing anything
		//
		if($this->config_exists($setting)) {
			//
			// Create the SQL statement to delete the entry
			//
			$sql = "DELETE FROM " . $this->mysql_config_table
				. " WHERE " . $this->mysql_config_table_setting . " = '"
				. $this->FixQuotes($setting) . "';";
			//
			// Execute the SQL statement
			//
			mysql_query($sql, $this->my_dbconnection) or
				die("<font color=\"red\">Failed to execute query</font><hr>"
					. "$sql<hr>" . mysql_error($this->my_dbconnection));
		}
	}

	//----------------------------------------------------------------------------
	//
	// Function used to return a list of all of the setting names as an array. The
	// array will have an entry 'count' that contains the number of cells, and the
	// rest of the cells will be 0, 1, ...
	//
	//----------------------------------------------------------------------------
	function config_get_names() {
		//
		// Start a counter
		//
		$x = 0;
		//
		// Build the SQL statement
		//
		$sql = "SELECT * FROM " . $this->mysql_config_table . " ORDER BY "
			. $this->mysql_config_table_setting . ";";
		//
		// Try to execute the query or display an error message if it fails
		//
		$rs = mysql_query($sql, $this->my_dbconnection) or
			die("<font color=\"red\">Failed to execute query</font><hr>"
				. "$sql<hr>" . mysql_error($this->my_dbconnection));
		//
		// While there are records being read...
		//
		while($data = mysql_fetch_assoc($rs)) {
			//
			// Add the setting's name to the array
			//		
			$ret[$x] = $this->FixQuotes(
				$data[$this->mysql_config_table_setting] . "",
				true);
			//
			// Increment the total counter
			//
			$x++;
		}
		//
		// Free the recordset
		//
		mysql_free_result($rs);
		//
		// Set the length value within the array
		//
		$ret['count'] = $x;
		//
		// Return the array
		//
		return $ret;
	}

	//----------------------------------------------------------------------------
	//
	// This function will delete ALL of the settings within the table
	//
	//----------------------------------------------------------------------------
	function config_reset_table($recreate = 1) {
		//
		// Drop and recreate the table?
		//
		if($recreate) {
			//
			// Yes, so call the internal functions then exit this function
			//
			$this->dropConfigTable();
			$this->createConfigTable();
			return;
		}
		//
		// Create the SQL statement to delete the entry
		//
		$sql = "DELETE FROM " . $this->mysql_config_table . ";";
		//
		// Execute the SQL statement
		//
		mysql_query($sql, $this->my_dbconnection) or
			die("<font color=\"red\">Failed to execute query</font><hr>"
				. "$sql<hr>" . mysql_error($this->my_dbconnection));
	}

	//----------------------------------------------------------------------------
	//
	// Returns an array of all of the configurations and their values.   The array
	// will have an entry 'count' that contains the number of cells, and the rest
	// of the cells (set as 0, 1, ...) will contain sub-arrays with two values,
	// named 'name' and 'value'.
	//
	//----------------------------------------------------------------------------
	function config_get_all() {
		//
		// Start a counter
		//
		$x = 0;
		//
		// Build the SQL statement
		//
		$sql = "SELECT * FROM " . $this->mysql_config_table . " ORDER BY "
			. $this->mysql_config_table_setting . ";";
		//
		// Try to execute the query or display an error message if it fails
		//
		$rs = mysql_query($sql, $this->my_dbconnection) or
			die("<font color=\"red\">Failed to execute query</font><hr>"
				. "$sql<hr>" . mysql_error($this->my_dbconnection));
		//
		// While there are records being read...
		//
		while($data = mysql_fetch_assoc($rs)) {
			//
			// Add the setting's name to the array
			//		
			$ret[$x]['name'] = $this->FixQuotes(
				$data[$this->mysql_config_table_setting] . "",
				true);
			$ret[$x]['value'] = $this->FixQuotes(
				$data[$this->mysql_config_table_value] . "",
				true);
			//
			// Increment the total counter
			//
			$x++;
		}
		//
		// Free the recordset
		//
		mysql_free_result($rs);
		//
		// Set the length value within the array
		//
		$ret['count'] = $x;
		//
		// Return the array
		//
		return $ret;
	}
}
?>
Return current item: Dynamic Configuration