[SOLVED] Selecting Data from Database(php/mysql).

[Demonic]

Alright I've come to a point where I only want a DB for settings and nothing based on ID's on a sql table.

 

instead of say:

 

$setting = mysql_query("SELECT * FROM settings WHERE id='1' ");

 

Now i broken up all the sql into seperate settings like so:

 

settingname | value

 

boardonline | no

 

Thats basicaly how I got the sql set up instead of having it all in one table row I have them in seperate rows.

 

So how do I go about selecting data from SQL not based on ID but mainly on setting name?

 

would I do

 

$settings = mysql_query("SELECT * FROM settings");

$settings = mysql_fetch_array($settings);

$settings['boardonline'][1];//would echo true/false according to my sql table instead of the row name?

 

Thanks in advance for helping.

[trq]

SELECT value FROM settings WHERE settingname = 'bordonline';

[Demonic]

Im not selecting just one setting.

[trq]

SELECT value FROM settings WHERE settingname = 'bordonline' AND settingname = 'foo';

[Demonic]

Mate thats going to be alot of "AND"'s  will my example at the end of the first post work or not?

[trq]

Sorry, Im not being real helpfull. I see what your doing now and I've done it before, something like this will be much easier.

 

<?php

  function getsettings($settingsstring) {
    // connect to db.
    if ($result = mysql_query("
      SELECT settingname, value
      FROM settings
      WHERE settingname
      IN($settingsstring);")) {
      if (mysql_num_rows($result)) {
        while ($row = mysql_fetch_assoc($result)) {
          $return[$row['settingname'] => $row['value']];
        }
      }
    }
    return $return;
  }

  $settings = getsettings("bordonline,foo,bar,bip");

?>

 

This will return an array of keys/values. Much easier to work with.

[Demonic]

Ah now thats much simpler.  Thanks .  Ill come back if I have problems thanks again.

[Demonic]

Just wondering how could I reverse this? To update sql.

 

Ill give it a go but im still wondering.

[trq]

an update is much simpler because you can only update one record at a time. However, you could create an array to do so. eg;

 

<?php

  function updatesettings($arr) {
    // connect to db.
    foreach ($arr as $k => $v) {
      if (!mysql_query("UPDATE settings SET value = '$v' WHERE settingname = '$k'")) {
        return false;
      }
    }
    return true;
  }

  if (updatesettings(array('bordonline' => 'no','foo' => 'bar')) {
    echo "update success";
  }

?>

[Demonic]

Ah nice I learned something new everyday .

[Demonic]

Im getting a wierd error:

Parse error: syntax error, unexpected T_DOUBLE_ARROW in /home/nevux/public_html/fo****/*******/sources/wordfilter.php on line 188

  function getsettings($settingsstring) {
    if ($result = mysql_query("
      SELECT `setting`, `value`
      FROM `wf_settings`
      WHERE `setting`
      IN($settingsstring);")) {
      if (mysql_num_rows($result)) {
        while ($row = mysql_fetch_assoc($result)) {
          $return[$row['setting'] => $row['value']];//IS LINE 188
        }
      }
    }
    return $return;
  }

[fert]

$return(array($row['setting'] => $row['value']));

[Demonic]

Must be a problem then because when I test it it doesn't work:

 

sql:

CREATE TABLE `wf_settings` (
  `setting` varchar(255) NOT NULL default '',
  `value` varchar(255) NOT NULL default 'yes'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `wf_settings`
-- 

INSERT INTO `wf_settings` VALUES ('topic_title', 'yes');
INSERT INTO `wf_settings` VALUES ('topic_posts', 'yes');
INSERT INTO `wf_settings` VALUES ('signature', 'yes');
INSERT INTO `wf_settings` VALUES ('pm_subject', 'yes');
INSERT INTO `wf_settings` VALUES ('pm_body', 'yes');
INSERT INTO `wf_settings` VALUES ('customfields', 'yes');
INSERT INTO `wf_settings` VALUES ('username', 'no');
INSERT INTO `wf_settings` VALUES ('globalall', 'no');

 

 

  function getsettings($settingsstring) {
//already connecting to DB
    if ($result = mysql_query("
      SELECT `setting`, `value`
      FROM `wf_settings`
      WHERE `setting`
      IN($settingsstring);")) {
      if (mysql_num_rows($result)) {
        while ($row = mysql_fetch_array($result)) {
          $return(array($row['setting'] => $row['value']));
        }
      }
    }
    return $return;
  }
$set = getsettings("topic_title,topic_posts,signature,pm_subject,pm_body,customfields,username,globalall");
echo $set[topic_title];

[Demonic]

bump

 

 

anyone?

[trq]

print_r($set);

 

Please.

[Demonic]

I did a error check setting $return = mysql_error(); if something went wrong so I got:

 

Unknown column 'topic_title' in 'where clause'

 

my sql is above.

[trq]

Ok... I see the problem. Need to make a few adjustments. The function will need to accept an array now. Try...

 

<?php

  function getsettings($settingsarray) {
    if ($result = mysql_query("
      SELECT `setting`, `value`
      FROM `wf_settings`
      WHERE `setting`
      IN('".implode("','",$settingsarray)."');")) {
      if (mysql_num_rows($result)) {
        while ($row = mysql_fetch_array($result)) {
          $return(array($row['setting'] => $row['value']));
        }
      }
    }
    return $return;
  }

?>

 

And call it using....

 

<?php

  $set = getsettings(array('topic_title','topic_posts','signature','pm_subject','pm_body','customfields','username','globalall'));

?>

[Barand]

try

 

$set = getsettings(" 'topic_title', 'topic_posts', 'signature', 'pm_subject', 'pm_body', 'customfields', 'username', 'globalall' ");

 

[Demonic]

humm I got this :

 

Warning: implode() [function.implode]: Bad arguments. in

[trq]

Is that error when calling the array using the new method I suggested? Barands method would have meant you could leave the function as it was earlier, I thought the newer way of calling the function seemed more logical.

[Demonic]

when calling your new way I tried it then I got undeclared function on the $return array.

 

So I changed it to the following:

 

  function getsettings($settingsarray) {
    if ($result = mysql_query("
      SELECT `setting`, `value`
      FROM `wf_settings`
      WHERE `setting`
      IN('".implode("','",$settingsarray)."');")) {
      if (mysql_num_rows($result)) {
        while ($row = mysql_fetch_array($result)) {
          $return[array($row['setting'] => $row['value'])];
        }
      }
    }else{
	$return = mysql_error();
}
    return $return;
  }

 

Then I tried this:

 

	$set = getsettings(array('topic_title','topic_posts','signature','pm_subject','pm_body','customfields','username','globalall'));
	print_r($set);

 

Now im not getting any errors nor any results.

[Demonic]

Humm I made my own and it works the same I guess: btw thorpe yours didn't work.

 

function WFSet($WFset = array()){

	foreach($WFset as $WFsett){

	$ro = mysql_query("SELECT * FROM `wf_settings` WHERE `setting` = '$WFsett' ") or die(mysql_error());
	$row = mysql_fetch_array($ro);

	$result[$WFsett] = array($row[setting] => $row[value]);

	}

return $result;

}

 

Result:

 

Array
(
    [topic_title] => Array
        (
            [topic_title] => yes
        )

    [topic_posts] => Array
        (
            [topic_posts] => yes
        )

    [signature] => Array
        (
            [signature] => yes
        )

    [pm_subject] => Array
        (
            [pm_subject] => yes
        )

    [pm_body] => Array
        (
            [pm_body] => yes
        )

    [customfields] => Array
        (
            [customfields] => yes
        )

    [username] => Array
        (
            [username] => no
        )

    [globalall] => Array
        (
            [globalall] => no
        )

)

[trq]

Thats what I get for not testing code before posting. Theres just a slight syntax error in my code, should be....

 

<?php

  function getsettings($settingsarray) {
    if ($result = mysql_query("
      SELECT setting, value
      FROM wf_settings
      WHERE setting
      IN('".implode("','",$settingsarray)."');")) {
      if (mysql_num_rows($result)) {
        while ($row = mysql_fetch_array($result)) {
          $return[$row['setting']] = $row['value'];
        }
      }
    }
    return $return;
  }

?>

 

That will return a nice neat associative array.