Page 1 of 1

Building MySQL UPDATE query with PHP settings class

Posted: Fri Jul 10, 2009 12:33 pm
by JAB Creations
I've rewritten the PHP settings class members helped me originally construct about two or three years ago when I was still learning a lot of fundamentals. The class works perfectly fine (the older version would "randomly" lose the visitor's preferences) however I'm having a little trouble getting the class to construct a MySQL UPDATE query so if a registered user is signed in and they update their preferences I need to update the table where their preferences are stored.

In general this isn't difficult though I have hit a couple snags. What I'm trying to do is create the column/value pairs first (that I will concatenate in to the $query string to be used in the MySQL query), the rest of the work should be cake.

Here is a shortened example of what the column/pairs variable would look like (only using six variable/value sets for the sake of the example) if I set a static variable (versus pulling the data from the array/function dynamically (from $_GET, $_POST, etc))...

Code: Select all

$preferences = 'audio='2', checkboxes='1', connection='0', css3='0', css3columns='4', csspatch='1'';
...and $preferences would end up being used as so...

Code: Select all

$query = "UPDATE preferences_table SET ".$preferences." WHERE id='1'";
mysql_query($query);
I first thought, hey why not create the variable inside the $_SESSION (which is currently commented out below on lines 55-57) however I thought I could execute this with more grace. I then proceeded to try running a second foreach loop on the $default array however apparently it was no longer an array (hence the is_array function on line 43) so I'm not sure why $default is not an array at that point? I'm not really sure how else I could approach the situation. I could get creative though I am pretty sure peer input would be way better at this point.

Code: Select all

<?php
class settings
{
 public function set($name,$value) {$this->$name = $value;}
 public function get($name){return $this->$name;}
}
 
$default = array(
'audio'=>'0',
'broadbandimages'=>'0',
'checkboxes'=>'0',
'connection'=>'0',
'css3'=>'0',
'css3columns'=>'1',
'csspatch'=>'1',
'cursors'=>'0',
'dhtml'=>'0',
'dtd'=>'1',
'initialfocus'=>'search_query',
'keyboardlayout'=>'developer',
'mime'=>'ns',
'pagination'=>'form',
'personality'=>'0',
'powerkeys'=>'0',
'scrollbars'=>'0',
'sidebar'=>'20',
'sounds'=>'0',
'theme'=>'classic',
'user'=>''
);
 
$c = count($default);
$i = 0;
foreach($default as $type=>$default)
{
 $$type = client($type, $default);
 
 $i++;
 if (isset($cookie_value)) {$cookie_value .= $type.'.'.$$type; if ($c!=$i) {$cookie_value .='_';}}
 else {$cookie_value = $type.'.'.$$type.'_';}
}
 
if (is_array($default))
{
 $sql_update;
 foreach($default as $key => $value)
 {
  $sql_update .= $key;
 }
 $_SESSION['11111111111111111111111111'] = $sql_update;
}
 
function client($type,$default)
{
 if (isset($_GET[$type])) {/*$_SESSION['11111111111111111111111111'] .= $type.'=\''.$_GET[$type].'\', '; */return $_GET[$type];}
 else if (isset($_POST[$type])) {/*$_SESSION['11111111111111111111111111'] .= $type.'=\''.$_POST[$type].'\', '; */return $_POST[$type];}
 else if (isset($_SESSION[$type])) {/*$_SESSION['11111111111111111111111111'] .= $type.'=\''.$_SESSION[$type].'\', '; */return $_SESSION[$type];}
 else if (isset($_COOKIE['settings']))
 {
  $pieces = explode('_', $_COOKIE['settings']);
  $c = count($pieces);
  $i = 0;
  foreach($pieces as $value)
  {
   $value = explode('.', $value);
   $i++;
   if ($value[0]==$type && $value[1] != '') {return $value[1];}
   else if ($c==$i) {return $default;}
  }
 }
 else {return $default;}
}
 
if (!headers_sent())
{
 if (isset($_SESSION['member'])) {$cookie_value .= 'user.'.$_SESSION['member'];}
 setcookie('settings',$cookie_value,time()+2592000,'/');
}
 
// Assign subclasses to true values.
$settings = new settings();
$settings->set('audio',$audio);
//$_SESSION['11111111111111111111111111'] = $sql_update;
$_SESSION['audio'] = $audio;
$settings->set('broadbandimages',$broadbandimages);
$_SESSION['broadbandimages'] = $broadbandimages;
$settings->set('checkboxes',$checkboxes);
$_SESSION['checkboxes'] = $checkboxes;
$settings->set('connection',$connection);
$_SESSION['connection'] = $connection;
$settings->set('css3',$css3);
$_SESSION['css3'] = $css3;
$settings->set('css3columns',$css3columns);
$_SESSION['css3columns'] = $css3columns;
$settings->set('csspatch',$csspatch);
$_SESSION['csspatch'] = $csspatch;
$settings->set('cursors',$cursors);
$_SESSION['cursors'] = $cursors;
$settings->set('dhtml',$dhtml);
$_SESSION['dhtml'] = $dhtml;
$settings->set('dtd',$dtd);
$_SESSION['dtd'] = $dtd;
$settings->set('initialfocus',$initialfocus);
$_SESSION['initialfocus'] = $initialfocus;
$settings->set('keyboardlayout',$keyboardlayout);
$_SESSION['keyboardlayout'] = $keyboardlayout;
$settings->set('mime',$mime);
$_SESSION['mime'] = $mime;
$settings->set('pagination',$pagination);
$_SESSION['pagination'] = $pagination;
$settings->set('personality',$personality);
$_SESSION['personality'] = $personality;
$settings->set('powerkeys',$powerkeys);
$_SESSION['powerkeys'] = $powerkeys;
$settings->set('scrollbars',$scrollbars);
$_SESSION['scrollbars'] = $scrollbars;
$settings->set('sidebar',$sidebar);
$_SESSION['sidebar'] = $sidebar;
$settings->set('sounds',$sounds);
$_SESSION['sounds'] = $sounds;
$settings->set('theme',$theme);
$_SESSION['theme'] = $theme;
$settings->set('user',$user);
$_SESSION['user'] = $user;
?>

Re: Building MySQL UPDATE query with PHP settings class

Posted: Fri Jul 10, 2009 1:21 pm
by andyhoneycutt

Code: Select all

// ... ln 34 below:
foreach($default as $type=>$default)
 
You're overwriting the value of $default with your foreach.

-Andy

Re: Building MySQL UPDATE query with PHP settings class

Posted: Fri Jul 10, 2009 1:24 pm
by JAB Creations
Ah! Thanks Andy! :lol:

Re: Building MySQL UPDATE query with PHP settings class

Posted: Fri Jul 10, 2009 1:31 pm
by andyhoneycutt
no problemo! got it working right now?

-Andy

Re: Building MySQL UPDATE query with PHP settings class

Posted: Fri Jul 10, 2009 1:38 pm
by JAB Creations
Yes, here it is with my amendments thanks again to your third person perspective! :)

*Edit* - Just adding it to the $_SESSION as I am viewing all the variables in the browser. :mrgreen:

Code: Select all

<?php
class settings
{
 public function set($name,$value) {$this->$name = $value;}
 public function get($name){return $this->$name;}
}
 
$default = array(
'audio'=>'0',
'broadbandimages'=>'0',
'checkboxes'=>'0',
'connection'=>'0',
'css3'=>'0',
'css3columns'=>'1',
'csspatch'=>'1',
'cursors'=>'0',
'dhtml'=>'0',
'dtd'=>'1',
'initialfocus'=>'search_query',
'keyboardlayout'=>'developer',
'mime'=>'ns',
'pagination'=>'form',
'personality'=>'0',
'powerkeys'=>'0',
'scrollbars'=>'0',
'sidebar'=>'20',
'sounds'=>'0',
'theme'=>'classic',
'user'=>''
);
 
 
$c = count($default);
$i = 0;
foreach($default as $key=>$value)
{
 $$key = client($key,$value);
 
 $i++;
 if (isset($cookie_value)) {$cookie_value .= $key.'.'.$$key; if ($c!=$i) {$cookie_value .='_';}}
 else {$cookie_value = $key.'.'.$$key.'_';}
}
 
$sql_update = '';
if (is_array($default))
{
 $sql_update;
 $i = 1;
 foreach($default as $key => $value)
 {
  $sql_update .= $key."='".$value."'";
  if ($c!=$i) {$sql_update .= ", ";}
  $i++;
 }
 $_SESSION['1'] = $sql_update;
}
 
function client($type,$default)
{
 if (isset($_GET[$type])) {/*$_SESSION['1'] .= $type.'=\''.$_GET[$type].'\', '; */return $_GET[$type];}
 else if (isset($_POST[$type])) {/*$_SESSION['1'] .= $type.'=\''.$_POST[$type].'\', '; */return $_POST[$type];}
 else if (isset($_SESSION[$type])) {/*$_SESSION['1'] .= $type.'=\''.$_SESSION[$type].'\', '; */return $_SESSION[$type];}
 else if (isset($_COOKIE['settings']))
 {
  $pieces = explode('_', $_COOKIE['settings']);
  $c = count($pieces);
  $i = 0;
  foreach($pieces as $value)
  {
   $value = explode('.', $value);
   $i++;
   if ($value[0]==$type && $value[1] != '') {return $value[1];}
   else if ($c==$i) {return $default;}
  }
 }
 else {return $default;}
}
 
if (!headers_sent())
{
 if (isset($_SESSION['member'])) {$cookie_value .= 'user.'.$_SESSION['member'];}
 setcookie('settings',$cookie_value,time()+2592000,'/');
}
 
// Assign subclasses to true values.
$settings = new settings();
$settings->set('audio',$audio);
$_SESSION['audio'] = $audio;
$settings->set('broadbandimages',$broadbandimages);
$_SESSION['broadbandimages'] = $broadbandimages;
$settings->set('checkboxes',$checkboxes);
$_SESSION['checkboxes'] = $checkboxes;
$settings->set('connection',$connection);
$_SESSION['connection'] = $connection;
$settings->set('css3',$css3);
$_SESSION['css3'] = $css3;
$settings->set('css3columns',$css3columns);
$_SESSION['css3columns'] = $css3columns;
$settings->set('csspatch',$csspatch);
$_SESSION['csspatch'] = $csspatch;
$settings->set('cursors',$cursors);
$_SESSION['cursors'] = $cursors;
$settings->set('dhtml',$dhtml);
$_SESSION['dhtml'] = $dhtml;
$settings->set('dtd',$dtd);
$_SESSION['dtd'] = $dtd;
$settings->set('initialfocus',$initialfocus);
$_SESSION['initialfocus'] = $initialfocus;
$settings->set('keyboardlayout',$keyboardlayout);
$_SESSION['keyboardlayout'] = $keyboardlayout;
$settings->set('mime',$mime);
$_SESSION['mime'] = $mime;
$settings->set('pagination',$pagination);
$_SESSION['pagination'] = $pagination;
$settings->set('personality',$personality);
$_SESSION['personality'] = $personality;
$settings->set('powerkeys',$powerkeys);
$_SESSION['powerkeys'] = $powerkeys;
$settings->set('scrollbars',$scrollbars);
$_SESSION['scrollbars'] = $scrollbars;
$settings->set('sidebar',$sidebar);
$_SESSION['sidebar'] = $sidebar;
$settings->set('sounds',$sounds);
$_SESSION['sounds'] = $sounds;
$settings->set('theme',$theme);
$_SESSION['theme'] = $theme;
$settings->set('user',$user);
$_SESSION['user'] = $user;
?>

Re: Building MySQL UPDATE query with PHP settings class

Posted: Fri Jul 10, 2009 2:00 pm
by andyhoneycutt
Just out of sheer curiosity, why do you not just store the settings object in the $_SESSION? It appears to me you're doing twice the work you need do, that or I'm missing something. Also, were you to do that, you could set the defaults in the class. Again, I'm likely missing something.

-Andy

Re: Building MySQL UPDATE query with PHP settings class

Posted: Fri Jul 10, 2009 2:18 pm
by alex.barylski
^^^ What he/she said :)

Re: Building MySQL UPDATE query with PHP settings class

Posted: Fri Jul 10, 2009 2:18 pm
by JAB Creations
This rewritten version of the original code was intended to work reliably...so optimizing it hasn't yet crossed my mind.

I think what you're asking me is why bother creating the class when I could simply use the session variables instead?

If that is the correct context of your question...then I have to admit offhand that I'm not exactly sure. I only recently decided to store the settings data in the session in Version 2.9 (in development) where as in Version 2.8 I only stored it in the class.

I'm going to have to play with the concept in my head for a bit though I think I see your point.

Re: Building MySQL UPDATE query with PHP settings class

Posted: Fri Jul 10, 2009 2:23 pm
by andyhoneycutt
@PCSpectra: He =]

@JAB: I'm saying I think you should be storing the class object in the $_SESSION (at least once you have a chance to, as you say, refactor the code). This will streamline things and take out redundancies in your code. I typically store objects in the session and access them as needed, but in this case, you could do one or the other; I think if you work it out during the refactoring process you will find it's more effecient to store the class in your $_SESSION than just adding a list of variables to it.

-Andy

Re: Building MySQL UPDATE query with PHP settings class

Posted: Fri Jul 10, 2009 2:40 pm
by JAB Creations
Fair enough...but I no longer see the need for a class. Why would you recommend keeping and then storing the class in to the session versus simply adding the variables to it?

Re: Building MySQL UPDATE query with PHP settings class

Posted: Fri Jul 10, 2009 3:03 pm
by JAB Creations
I think the only benefit off hand in trying to find some logic to keep it would be to run another foreach loop if I wanted to create another array and store the array in the session...though I think with more time to work on the concept I might possibly come up with a way to still do that without a class. Hm...

Re: Building MySQL UPDATE query with PHP settings class

Posted: Fri Jul 10, 2009 3:18 pm
by andyhoneycutt
Honestly, keeping what you're accomplishing with your code in mind, I must say the only justification I have for storing a class in the session as opposed to scalars is because I much prefer OO code. Not much justification. I can see if you were to extend the logic of your procedure a bit, and were you wanting to do more than default values when the class [would be] instantiated, then a class might be warranted. As it is, I'm sure you'll end up with a smaller footprint by just stuffing scalars into the session.

An example:

Code: Select all

class Settings
{
  public function __construct($args = array())
  {
    foreach($args as $key=>$value)
    {
      $this->$key = $value;
    }
  }
}
 
$s = new Settings(array("something" => "1", "anotherthing" => "2"));
$_SESSION['settings'] = $s;
Truly, not much different than what you're doing in terms of efficiency.

-Andy

Re: Building MySQL UPDATE query with PHP settings class

Posted: Fri Jul 10, 2009 4:37 pm
by JAB Creations
It took only about an hour though I've completely replaced the $settings class on today's build of my site and it still runs as well as it did last night.

I decided to keep it simple for now since what role this fulfills is simple right now.

The only thing I am cleft about is not being able to use variable variable for setting the session variables...that is instead of defining a variable variable on line 31 I would rather do something along the lines of...

Code: Select all

$_SESSION[$$key] = client($key,$value);
However it works perfectly for now and there doesn't seem to be any major problem...though I still need to finish constructing the MySQL query and ensure that it executes as desired.

Thanks for your input!

Code: Select all

<?php
 
$default = array(
'audio'=>'0',
'broadbandimages'=>'0',
'checkboxes'=>'0',
'connection'=>'0',
'css3'=>'0',
'css3columns'=>'1',
'csspatch'=>'1',
'cursors'=>'0',
'dhtml'=>'0',
'dtd'=>'1',
'initialfocus'=>'search_query',
'keyboardlayout'=>'developer',
'mime'=>'ns',
'pagination'=>'form',
'personality'=>'0',
'powerkeys'=>'0',
'scrollbars'=>'0',
'sidebar'=>'20',
'sounds'=>'0',
'theme'=>'classic',
'user'=>''
);
 
$c = count($default);
$i = 0;
foreach($default as $key=>$value)
{
 $$key = client($key,$value);
 
 $i++;
 if (isset($cookie_value)) {$cookie_value .= $key.'.'.$$key; if ($c!=$i) {$cookie_value .='_';}}
 else {$cookie_value = $key.'.'.$$key.'_';}
}
 
if (isset($_SESSION['member']))
{
 $sql_update = '';
 $i = 1;
 foreach($default as $key => $value)
 {
  $sql_update .= $key."='".$value."'";
  if ($c!=$i) {$sql_update .= ", ";}
  $i++;
 }
}
 
function client($type,$default)
{
 if (isset($_GET[$type])) {/*$_SESSION['1'] .= $type.'=\''.$_GET[$type].'\', '; */return $_GET[$type];}
 else if (isset($_POST[$type])) {/*$_SESSION['1'] .= $type.'=\''.$_POST[$type].'\', '; */return $_POST[$type];}
 else if (isset($_SESSION[$type])) {/*$_SESSION['1'] .= $type.'=\''.$_SESSION[$type].'\', '; */return $_SESSION[$type];}
 else if (isset($_COOKIE['settings']))
 {
  $pieces = explode('_', $_COOKIE['settings']);
  $c = count($pieces);
  $i = 0;
  foreach($pieces as $value)
  {
   $value = explode('.', $value);
   $i++;
   if ($value[0]==$type && $value[1] != '') {return $value[1];}
   else if ($c==$i) {return $default;}
  }
 }
 else {return $default;}
}
 
if (!headers_sent())
{
 if (isset($_SESSION['member'])) {$cookie_value .= 'user.'.$_SESSION['member'];}
 setcookie('settings',$cookie_value,time()+2592000,'/');
}
 
$_SESSION['audio'] = $audio;
$_SESSION['broadbandimages'] = $broadbandimages;
$_SESSION['checkboxes'] = $checkboxes;
$_SESSION['connection'] = $connection;
$_SESSION['css3'] = $css3;
$_SESSION['css3columns'] = $css3columns;
$_SESSION['csspatch'] = $csspatch;
$_SESSION['cursors'] = $cursors;
$_SESSION['dhtml'] = $dhtml;
$_SESSION['dtd'] = $dtd;
$_SESSION['initialfocus'] = $initialfocus;
$_SESSION['keyboardlayout'] = $keyboardlayout;
$_SESSION['mime'] = $mime;
$_SESSION['pagination'] = $pagination;
$_SESSION['personality'] = $personality;
$_SESSION['powerkeys'] = $powerkeys;
$_SESSION['scrollbars'] = $scrollbars;
$_SESSION['sidebar'] = $sidebar;
$_SESSION['sounds'] = $sounds;
$_SESSION['theme'] = $theme;
$_SESSION['user'] = $user;
?>

Re: Building MySQL UPDATE query with PHP settings class

Posted: Fri Jul 10, 2009 4:41 pm
by andyhoneycutt
Nice :) Simple is always better.

-Andy