Updating a database

[danbriant]

Basically i have a MYSQL table whcih is laid out like so

 

config_name

config_value

those are the only 2 fields in the database

 

Now what im trying to do is create a settings page in a script im working on that will update the config database except i can't figure it out

I know it's all got to be inside a form etc

Heres an example of the database with data in it

 

EG

config_name    config_value

sitename          yourdomain.com

description      my test site

 

Now i need to figure out how to make a statement that will update the database. Ofcourse these will be assigned to text boxes etc. But i can't figure out how to pull the data from SQL in php and then get it updating.

[aeroswat]

Basically i have a MYSQL table whcih is laid out like so

 

config_name

config_value

those are the only 2 fields in the database

 

Now what im trying to do is create a settings page in a script im working on that will update the config database except i can't figure it out

I know it's all got to be inside a form etc

Heres an example of the database with data in it

 

EG

config_name    config_value

sitename          yourdomain.com

description      my test site

 

Now i need to figure out how to make a statement that will update the database. Ofcourse these will be assigned to text boxes etc. But i can't figure out how to pull the data from SQL in php and then get it updating.

 

You just need an UPDATE statement?

 

UPDATE tbl SET config_name="sitename", config_value="yoursite.com"

[danbriant]

No i know how to do it in SQL that's easy

I am just stuck on how to do the php part

I can't figure out the arrays etc

[aeroswat]

No i know how to do it in SQL that's easy

I am just stuck on how to do the php part

I can't figure out the arrays etc

 

What arrays? The POST or GET arrays?

 

You need to make a form with the inputs and assign each input names. Then when you submit it the action needs to be a php file that will handle the information. If you are using a get method then you would access it by the GET array. $_GET['name_of_input']

The post method will use the POST array $_POST['name_of_input']

 

and if you are trying to do a loop with your query then you can assign the names of the inputs to array names... like

name="configname[]"

value="configvalue[]"

 

and use a foreach loop to cycle through each...

If neither of these answer ur question then you need to clarify.

[danbriant]

Here's my SQL database currently for the config table

--
-- Table structure for table `config`
--

CREATE TABLE IF NOT EXISTS `config` (
  `config_name` varchar(255) NOT NULL,
  `config_value` varchar(255) NOT NULL,
  PRIMARY KEY  (`config_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `config`
--

INSERT INTO `config` (`config_name`, `config_value`) VALUES
('sitename', 'YourDomain.com'),
('description', 'my test site');

 

Now i need to be able to take the value of sitename (in this case yourdomain.com) and get it to show in a text field, then update it when a user hits submit. Then again for description and so on etc, all on one form with one button. i dont want to show description or sitename, only the assigned values

 

I know i need to use arrays etc

As if i use

$row['config_name']. " - ". $row['config_value'];

It slings out the values of everything etc. Which i don't want.

 

 

[aeroswat]

Here's my SQL database currently for the config table

--
-- Table structure for table `config`
--

CREATE TABLE IF NOT EXISTS `config` (
  `config_name` varchar(255) NOT NULL,
  `config_value` varchar(255) NOT NULL,
  PRIMARY KEY  (`config_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `config`
--

INSERT INTO `config` (`config_name`, `config_value`) VALUES
('sitename', 'YourDomain.com'),
('description', 'my test site');

 

Now i need to be able to take the value of sitename (in this case yourdomain.com) and get it to show in a text field, then update it when a user hits submit. Then again for description and so on etc, all on one form with one button. i dont want to show description or sitename, only the assigned values

 

I know i need to use arrays etc

As if i use

$row['config_name']. " - ". $row['config_value'];

It slings out the values of everything etc. Which i don't want.

 

Are you asking for someone to show you how to show the values in the input text boxes? Or are you asking for someone to write an entire update script for you?

 

What is the problem you are having? It seems like you are familiar with the aspects of the language that are required in order to be able to do an update script. You will need to of course have the config name somewhere in order to know what record of the database you are updating. Please explain what trouble you are having. Do you not how to make form submissions? Do you not know how to handle a form submission in php?

[Psycho]

Give this a try (not tested so there may be some syntax errors):

 

<?php
    
$response = '';
    
if(isset($_POST))
{
    //Changes were submitted
    //Put post data in another array for cleaning/parsing
    $configValues = array();
    foreach($_POST as $name => $value)
    {
        $name  = "'" . mysql_real_esacape_string($name) . "'";
        $value = "'" . mysql_real_esacape_string($value) . "'";
      $configValues[$name] = $value;
    }
  
    //Create/run update query
    $caseConditions = '';
    foreach ($configValues as $name => $value)
    {
        $caseConditions .= "        WHEN {$name} THEN {$value}\n";
    }
    $query = "UPDATE `configTable`
              SET `config_value` =
                  CASE `config_name`
                      {$caseConditions}
                  END
              WHERE `config_name` IN (" . implode(', ', array_keys($configValues)) . ")";
    $result = mysql_query($query) or die (mysql_error());
    $response = 'Config values were updated';
}
    
//Get the current values
$query = "SELECT `config_name`, `config_value` FROM `configTable`";
$result = mysql_query($query) or die (mysql_error());
    
//Process results into form fields
$configFields = '';
while($record = mysql_fetch_assoc($result))
{
    $configFields .= "{$record['config_name']}: ";
    $configFields .= "<input type=\"text\" name=\"{$record['config_name']}\" value=\"{$record['config_value']}\" /><br />\n";
}

?>
<html>
<head></head>

<body>
<div style="color:red;"><?php echo $response; ?></div>
<form name="updateConfigs" action="" method="POST">
<?php echo $configFields; ?>
<br />
<button type="submit">Update Config Values</button>
</form>
</body>

</html>

[danbriant]

I just don't know how to get the values out of the database and display and update them.

I am familiar with updating and getting values from normal SQL etc, but i cant figure out how to do it in this type of array

 

So all i want is just one example on pulling out data from this array, assign that to some variable which i can then assign to a text box

 

Will try and see if the example above helps me as well.

 

As the way i am used to doing stuff is as follows,

$sql = mysql_query("SELECT * FROM cats WHERE cat_id = " . (int) $_GET['id']) or die('Could not grab cats: ' . mysql_error());  

while($row = mysql_fetch_array( $sql )) {
	// Print out the contents of each row into a table
	$cat_name = stripslashes( $row['cat_name'] );
	$cat_desc = stripslashes( $row['cat_desc'] );
} 

	echo'<table border="0" cellspacing="0" cellpadding="3" width="100%">
        		<caption>
				<div>Posts in '.$cat_name.'</div>
        		</caption>
  				<tr>
    				<td style="text-align: center;">'.$cat_desc.'</td>
  				</tr>
		</table>';

[Psycho]

Just tested out the code I posted previously and there are two minor errors:

 

#1: change the IF statement to this:

 
if(isset($_POST) && count($_POST)>0)

 

These lines had a typo in the function name:

 
        $name  = "'" . mysql_real_escape_string($name) . "'";
        $value = "'" . mysql_real_escape_string($value) . "'";

 

Other than that the script *should* work.

 

EDIT: One thing the above script does not do is validate the values with respect to the database field constraints. So, if those fields have a limit of x number of characters, expect numbers, etc. then you should be validating them. Also, since these are configuration settings I would assume each config value has specific validation that is separate from the database constraints. So, a 'domain name' would have different constraints as opposed to an "admin email" setting. I would suggest creating a function with a switch statement to validate all the particular values and call it within the loop that parses the POST data.