Okay so i have a database table with settings in.

the table structure is:

setting_id | setting_name | setting_value

i want it to create a form which has every setting taken from database and the value shows in a input field and i can edit and then click save and it updates all the fields.

How can i do this?

Thanks,
Dan

I usually charge people for stuff like this, but you've been cool on the forums, so let me give it to you as a friendly gesture. I have not tested this code, so there may be some bugs (didn't even save it to a file), but the structure is all there. Target language is PHP 5 with the MySQLi class. I have assumed that your primary key is an integer.

PHP Code:

<?php
//Assume that $mysqli is an instance of the MySQLi class
define('SETTINGS_BEFORE_SAVE_BUTTON',5); //To display the Save button every so often

if (!empty($_POST['setting'])) {
  $query_string = ''; $query_prefix = '';
  foreach ($_POST['setting'] as $setting_id=>$setting_value) {
    $setting_value_sanitized = $mysqli->real_escape_string($setting_value);
    $query_string .= $query_prefix."UPDATE settings SET setting_value='".."' WHERE setting_id=".((int)$setting_id)." LIMIT 1";
    $query_prefix = ';';
  }
  if (strlen($query_string) > 0) {
    if ($mysqli->multi_query($query_string)) {
      $result_message = '';
      $query_number = 1;
      do {
        if ($mysqli->errno > 0) {
          $result_message .= '<div class="error_message">ERROR UPDATING SETTING #'.$query_number.': '.$mysqli->error.'</div>';
        }        
        $query_number++;
      } while ($mysqli->next_result());
      if (strlen($result_message) == 0) {
        $result_message = '<div class="success_message">SETTINGS UPDATED</div>';
      }
    } else {
      $result_message = '<div class="error_message">ERROR UPDATING SETTINGS: '.$mysqli->error.'</div>';
    }
  }  
}

echo $result_message;

echo '<form method="post">';

if ($settings_query = $mysqli->query("SELECT * FROM settings")) {
  if ($settings_query->num_rows > 0) {
    $row_count=0;
    while ($a_setting = $settings_query->fetch_object()) {
      echo (++$row_count).') '.$a_setting->setting_name.': <input type="text" name="setting['.$a_setting->setting_id.']" value="'.$a_setting->setting_value.'" /><br />';
      if ($row_count%SETTINGS_BEFORE_SAVE_BUTTON == 0) {
        echo '<input type="submit" value="Save" /><br />';
      }
    }
  }
}
echo '</form>';

$mysqli->close();
?>

Thanks a bunch Jeremy will definatly be getting some green TP

will go and test now

Guh help!

i have been editing it and have got it so it almost works

PHP Code:

<?php
include('../includes/includes.php');
login();
//Assume that $mysqli is an instance of the MySQLi class
define('SETTINGS_BEFORE_SAVE_BUTTON',5); //To display the Save button every so often
if (!empty($_POST['setting'])) {
  $query_string = ''; $query_prefix = '';
  
  foreach ($_POST['setting'] as $setting_id=>$setting_value) {
    $setting_value_sanitized = $db->escape($setting_value);
    $query_string .= $query_prefix."UPDATE temp_settings SET setting_value='".$setting_value_sanitized."' WHERE setting_id=".((int)$setting_id)." LIMIT 1";
    $query_prefix = ';';
  }
  if (strlen($query_string) > 0) {
    if ($db->multi_query($query_string)) {
      $result_message = '';
      $query_number = 1;
      do {
        if ($db->errno > 0) {
          $result_message .= '<div class="error_message">ERROR UPDATING SETTING #'.$query_number.': '.$db->error.'</div>';
        }        
        $query_number++;
      } while ($db->next_result());
      if (strlen($result_message) == 0) {
        $result_message = '<div class="success_message">SETTINGS UPDATED</div>';
      }
    } else {
      $result_message = '<div class="error_message">ERROR UPDATING SETTINGS: '.$db->error.'</div>';
    }
  }  
}
echo $result_message;
echo '<form method="post">';
$settings_query = $db->query("SELECT * FROM temp_settings") or $query_error();
#print_r($db->fetch_row($settings_query));
 # if ($settings_query->num_rows > 0) {
    $row_count=0;
    while ($a_setting = $db->fetch_object($settings_query)) {
      echo ''.($row_count++).') '.$a_setting->setting_name.': <input type="text" name="setting['.$a_setting->setting_id.']" value="'.$a_setting->setting_value.'" /><br />';
      if ($row_count%SETTINGS_BEFORE_SAVE_BUTTON == 0) {
        echo '<input type="submit" value="Save" /><br />';
      }
    }
#  }
echo '</form>';

?>

only problem is the class im using doesnt have multi_query()

and i cant find anywhere what i need to add to the class to craete the multi_query func.

PLEASE HELP!

My code adjusted to the much slower version w/o multi_query

PHP Code:

<?php
//Assume that $mysqli is an instance of the MySQLi class
define('SETTINGS_BEFORE_SAVE_BUTTON',5); //To display the Save button every so often

if (!empty($_POST['setting'])) {
  $result_message = '';
  $query_number = 1;
  foreach ($_POST['setting'] as $setting_id=>$setting_value) {
    $setting_value_sanitized = $mysqli->real_escape_string($setting_value);
    $query_string = "UPDATE settings SET setting_value='".$setting_value_sanitized."' WHERE setting_id=".((int)$setting_id)." LIMIT 1";
    if (!$mysqli->query($query_string)) {
      $result_message .= '<div class="error_message">ERROR UPDATING SETTING #'.$query_number.': '.$mysqli->error.'</div>';
    }
    $query_number++;
  }
  if (strlen($result_message) == 0) {
    $result_message = '<div class="success_message">SETTINGS UPDATED</div>';
  }
}

echo $result_message;

echo '<form method="post">';

if ($settings_query = $mysqli->query("SELECT * FROM settings")) {
  if ($settings_query->num_rows > 0) {
    $row_count=0;
    while ($a_setting = $settings_query->fetch_object()) {
      echo (++$row_count).') '.$a_setting->setting_name.': <input type="text" name="setting['.$a_setting->setting_id.']" value="'.$a_setting->setting_value.'" /><br />';
      if ($row_count%SETTINGS_BEFORE_SAVE_BUTTON == 0) {
        echo '<input type="submit" value="Save" /><br />';
      }
    }
  }
}
echo '</form>';

great working great im using this now

PHP Code:

<?php
include('../includes/includes.php');
login();
//Assume that $mysqli is an instance of the MySQLi class
define('SETTINGS_BEFORE_SAVE_BUTTON',5); //To display the Save button every so often
if (!empty($_POST['setting'])) {
  $result_message = '';
  $query_number = 1;
  foreach ($_POST['setting'] as $setting_id=>$setting_value) {
     $setting_value_sanitized = stripslashes(htmlspecialchars_decode(($db->escape($setting_value))));
    $query_string = "UPDATE temp_settings SET setting_value='".$setting_value_sanitized."' WHERE setting_id=".((int)$setting_id)." LIMIT 1";
    if (!$db->query($query_string)) {
      $result_message .= '<div class="error_message">ERROR UPDATING SETTING #'.$query_number.': '.$db->query_error().'</div>';
    }
    $query_number++;
  }
  if (strlen($result_message) == 0) {
    $result_message = '<div class="success_message">SETTINGS UPDATED</div>';
  }
}
echo $result_message;
echo '<form method="post">';
$settings_query = $db->query("SELECT * FROM temp_settings") or $query_error();
#print_r($db->fetch_row($settings_query));
 # if ($settings_query->num_rows > 0) {
    $row_count=0;
    while ($a_setting = $db->fetch_object($settings_query)) {
      echo '<label style="width: 200px">'.($row_count++).') '.$a_setting->setting_name.':</label><span>'; 
 
   switch($a_setting->format)
   {
   case 'input':
   echo '<input type="text" name="setting['.$a_setting->setting_id.']" value="'.htmlspecialchars($a_setting->setting_value).'" width="35" />';
   break;
 
   case 'textarea':
   echo '<textarea name="setting['.$a_setting->setting_id.']" cols="35" rows="5">'.htmlspecialchars($a_setting->setting_value).'</textarea>';
   break;
 
   default:
   echo '<input type="text" name="setting['.$a_setting->setting_id.']" value="'.htmlspecialchars($a_setting->setting_value).'" width="35" />';
   break;
   }
   echo $a_setting->setting_comment.'</span>
   <br />';
      if ($row_count%SETTINGS_BEFORE_SAVE_BUTTON == 0) {
        echo '<input type="submit" value="Save" /><br />';
      }
    }
#  }
echo '</form>';
 
?>

How would i group them? by setting_group field (its a varchar so theme settings are in the theme group etc.

:S

would this be simple like adding group by setting_group?

EDIT: its not i added GROUP BY setting_group and only showed 9 settings (theres currently 34 in DB)

You'd need to order by the setting group; track the current group while looping through the result set; on change of group, add in a divisor of some kind (e.g. OPTGROUP for SELECTs).

I'd code further for you, but this is already showing creeping scope and as I mentioned at the very beginning, this is something I get paid to do, so sorry to be a pain at this point, but... hopefully the tip I gave helps.

I will have to try and figure out what you ment when i get home and have my PHP cap on (on a side note im really trying to find a PHP base ball cap but i cant find one anywhere! anyone ?)

ok i got it so it is orderd by group, but how could i have it reconise a new group and add a title or whatever?

anyone else Jeremy has already been very helpful