Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

How to ease the way of storing configuration parameters

mysqli bind_param

  • Please log in to reply
4 replies to this topic

#1 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 19 October 2009 - 12:49 PM

Hello everyone.


I'm been building a system of my own for a while now, and thaught of sharing some code with you. I'm building some kind of community, with users and stuff.

In most systems, you need to have configuration parameters, that you can easily change, and especially if more people will be administrating the site, so you can build a web form to handle the parameters.


Another usage of this code I will show you, is to store unlimited amount of facts of a user, no need to create another field in the user table!


First, I have an MySQL Table to store my configuration in:



CREATE TABLE `base_config` (

  `scope` bigint(20) unsigned NOT NULL DEFAULT '0',

  `module` bigint(20) unsigned NOT NULL DEFAULT '0',

  `var` varchar(50) NOT NULL DEFAULT '',

  `value` varchar(250) DEFAULT NULL,

  PRIMARY KEY (`scope`,`var`,`module`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1


This table has those 4 fields,

  • Scope - This is either a user id or the system
  • Module - as I have built a module based system, this can be used for any modules own' purposes, this is which module that has this particular information. if not used, just use 0, or remove it from everywhere.
  • Var - name of the variable you're looking for
  • Value - value of the variable stored


I have choosen to store max 250 chars in these values. this can of course be altered after own wishes.


Then, we need some PHP code to handle this table, I have choosen to do this as a class as it gets to be a solid configuration item, with no need to interfere with.


The only thing to think about is the database. I have created a special class just to keep an database connection opened, how you solve it, is up to you. I continously use the OOP version of mysqli.


This is my database class:



final class db {

protected static $_dbase = null;

public function getInstance() {

if (self::$_dbase === null) {

self::$_dbase = new mysqli('db_host', 'db_user',

'db_password', 'db_name');

if (mysqli_connect_errno()) {

echo "Database Connection Error: " .

mysqli_connect_error();

exit(1);

}

}

return self::$_dbase;

}


protected function __construct() {}

protected function __clone() {}

protected function __destruct() {

self::$_dbase->close();

}

}




First, we declare the class and it's member variables. I declare them as protected, if we want to inherit this class some time.




class Config {

protected $module;

protected $user;

protected $conf;

protected $scope;



  • module - will keep information about which module the instance will work for.
  • user - will hold all data for the logged in user
  • conf - will hold all module system data
  • scope - will keep which user we work with



Now, we start creating an constructor, that is an function (method) that is ran when we create an instance for the class, and initiates the whole thing



public function __construct($module, $scope = 0) {

$this->module = $module;

$this->scope = $scope;

$this->load();

}



We take two parameters to create the class, we need to specify which module and which user we will handle, we store that in the member variables, then we call the load function, wich comes next.




public function load() {

$db = db::getInstance();

if ($result = $db->query("SELECT * FROM base_config WHERE scope = '0' AND module='" .

$this->module . "'")) {

while ($row = $result->fetch_assoc()) {

$this->conf[$row['var']] = $row['value'];

}

$result->close();

}



Now, we load everything with scope 0, which means module system specific variables and store them in our array $conf. We let the method continue:



if ($this->scope != 0) {

if ($result = $db->query("SELECT * FROM base_config WHERE scope = '" . $this->

scope . "' AND module = '" . $this->module . "'")) {

while ($row = $result->fetch_assoc()) {

$this->user[$row['var']] = $row['value'];

}

$result->close();

}

}

}



So, if we have a user logged in, ie, scope set to something else but 0, we load as we did above, but into the user member instead.

Now, we have all configuration needed for this run in memory.


we declare a save method, to save modified variables.



public function save() {

$db = db::getInstance();

$db->autocommit(false);



We get our db connection, and turn off autocommit, to be able to not modify the database until all changes are done


we continue with saving the user variables:



if ($this->scope != 0) {

$res = $db->query("DELETE FROM base_config WHERE scope = '" . $this->scope .

"' AND module = '" . $this->module . "'");

$stat = $db->prepare("INSERT INTO base_config VALUES ('" . $this->scope . "', '" .

$this->module . "', ?, ?)");


$stat->bind_param("ss", $var, $value);


foreach ((array )$this->user as $var => $value) {


$stat->execute();

}

$db->commit();

}



First, we delete all of the data connected to this module and user, if a parameter was removed, then we reapply those in the stored variable


and we repeat it all with the system parameters



$res = $db->query("DELETE FROM base_config WHERE scope = '0' AND module = '" . $this->

module . "'");

$stat = $db->prepare("INSERT INTO base_config VALUES ('0', '" . $this->module .

"', ?, ?)");

$stat->bind_param("ss", $var, $value);

foreach ((array )$this->conf as $var => $value) {

$stat->execute();

}

$db->commit();



now, we turn autocommit back on so everything is back to normal



$db->autocommit(true);

}



So, that was that method, so we save the data. now, we go more to the usage interface of the class


Here is how we read out variables:



public function getVar($var) {

if (isset($this->user[$var])) {

return $this->user[$var];

} else {

return $this->conf[$var];

}

}



If the user has it's own variable of the same name, use that instead of the system variable. this can be used for example background colors or themes or other things


now, we want to be able to change a value:



public function setVar($var, $value, $scope = 0) {

if (!$scope) {

$this->conf[$var] = $value;

} else {

$this->user[$var] = $value;

}

}



Don't forget to do a save(); when all variables are modified!


if we wnat to remove it we use this unset, but here, we need to specify if we want to unset it from the user scope or the module scope



public function unsetVar($var, $scope = 0) {

if (!$scope) {

unset($this->conf[$var]);

} else {

unset($this->user[$var]);

}

}




If we ever wants to know which scope this is set up to, this reads it out.



public function getScope() {

return $this->scope;

}




And this reads out which module...



public function getModule() {

return $this->module;

}




At times, you might want all your variables in an array, so I built this one. I need to typecast the members to array, if someone of them are empty.



public function getArray() {

return array_merge((array )$this->conf, (array )$this->user);

}

}




end of class

Attached Files

  • Attached File  db.php   528bytes   298 downloads
  • Attached File  Config.php   2.59KB   430 downloads

  • 1

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.


#2 debtboy

debtboy

    CC Devotee

  • Just Joined
  • PipPipPipPipPipPip
  • 499 posts

Posted 19 October 2009 - 03:37 PM

Awesome +rep (when I can) :)
  • 0

#3 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 19 October 2009 - 03:48 PM

Nice looking code, Orjan! Yay for MySQLi. :)
I'm curious, why do you turn autocommit off? The only time I've ever found commit and rollback useful was when manually executing SQL in a database (IE: From SQL Plus or MySQL Console). It would make sense if you caught a database error and rolledback but I don't see where you test for DB errors.

+rep!
  • 0

#4 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 19 October 2009 - 03:50 PM

That is only because I don't want the table empty of it's contents too long time between the delete and the inserts, therefore, I hold the commits, and do all in a row later on. it might be milliseconds, but that could be when a user tries to read the database for a new page.
  • 0

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.


#5 DirectXp7

DirectXp7

    CC Lurker

  • New Member
  • Pip
  • 3 posts

Posted 20 March 2012 - 10:35 AM

Very nice, brother :thumbup1:

+Rep

PD: Post example for use?

Thanks ;)
  • 0





Also tagged with one or more of these keywords: mysqli, bind_param

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download