Update mysql with all post data

[lindm]

I got help some help some while ago with a script to gather all post data and generate a mysql query to update all mysql columns. The script is based on the form field having the same name as the mysql column name. I have however some problems implementing the code into my script.

 

my current code:

if(isset($_POST["save"])){

mysql_query("UPDATE table SET FIELD = '$_POST[field]'");

 

the code supposed to help:

function updateTable($data){
        $q = "UPDATE erevisor SET ";

        $num=count($data);
$count=0;								
foreach($data as $key=>$value){
$count++;
	if($count<$num){
		$q .=$key."='".$value."', ";
	}
	else{
		$q .=$key."='".$value."' ";
	}

            $q .="WHERE userName='$user2'";

}

 

Now how to merge these two?

 

[chronister]

<?php

if(isset($_POST["save"])){


         $q = "UPDATE erevisor SET ";

        $num=count($data);
$count=0;								
foreach($data as $key=>$value){
$count++;
	if($count<$num){
		$q .=$key."='".$value."', ";
	}
	else{
		$q .=$key."='".$value."' ";
	}

            $q .="WHERE userName='$user2'";


           mysql_query($q);
}
?>

 

The best I can do for ya without seeing more.

 

Nate

[lindm]

Get

Parse error: syntax error, unexpected $end in

 

Which refers to the end tag ?>

 

What is this due to?

[discomatt]

Missing a closing curly brace...

 

Here's a working, safer version of the above code, with comments. If you don't understand let me know and I'll explain better

 

<?php

# Connect to database -> assuming you've already
# done this, so no error checking
mysql_connect( 'localhost', 'root', '' );
mysql_select_db( 'test' );

# Populate with some false data - testing purposes only
$_POST = array(
'test1' => 'Some data!',
'test2' => 'Some data that needs \' ESCAPING',
'submit' => 'Shouldn\'t even make it to the query',
'foo' => 'bar, obviously'
);
$user2 = 'discomatt';

# Verify there's been data submitted
if ( count($_POST) > 0 ) {

# The names of any elements you don't want
# added to mysql... most importantly the
# submit button  If you put the fields you
# want added in their own array, there's no
# need for this
$doNotAdd = array( 'submit' );

# Create the array to store each query row
$qArray = array();

# Loop through post data
foreach ( $_POST as $key => $val )
	# Make sure key isn't in 'doNotAdd' list
	if (  !in_array( $key, $doNotAdd )  )
		$qArray[] = '`'. $key .'` = \''. mysql_real_escape_string( $val ) .'\'';

# Verify there's actually data to insert
if ( count($qArray) > 0 ) {
	# Build query
	$q = 'UPDATE `erevisor` SET '. implode( ', ', $qArray ) .' WHERE `userName` = \''. $user2 .'\'';
	# Debug - incase stuff goes wrong remove # below
	# echo $q;
	# Check if query goes through
	if ( !mysql_query($q) )
		# Query broked!
		echo 'Could not update set...';
}


}

?>

 

Will execute the query

UPDATE `erevisor` SET `test1` = 'Some data!', `test2` = 'Some data that needs \' ESCAPING', `foo` = 'bar, obviously' WHERE `userName` = 'discomatt'

[lindm]

Great! Sees to work and I really like the debug feature.

 

 

 

[lindm]

Hello again,

 

Stumbled across a problem. I have several checkboxes with the value "checked" in my form . If checked the value should be written to the corresponding mysql column. If not checked of course the column should be nulled. Now with the excellent solutions from discomatt the script ignores the $_POST of the checkboxes when they are unchecked...I want the script to post for instance the value 0 to the mysql column for the checkbox if it is unchecked. Hope I am understandable...possible?

[discomatt]

Making generic code do specific things kind of defeats the purpose.

 

Your best bet is to set a default value for that column in your MySQL database.