<?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("'","'",trim($src));
$s = str_replace('"',""",$s);
$s = str_replace("\n"," ",$s);
$s = str_replace("\r","",$s);
} else {
$s = str_replace("\\'","'",trim($src));
$s = str_replace("'","'",$s);
$s = str_replace(""","\"",$s);
$s = str_replace(" ","\n",$s);
$s = str_replace("","\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;
}
}
?>