Update query problem

[glava]

hi good people, i'm trying to update my database with values from textboxes and i'm stuck with it. Can someone tell my what's wrong with this :

 

mysql_query("UPDATE table SET column1 = {$_POST['txt1_$j']} , column2 = {$_POST['txt2_$j']} , column3 = {$_POST['txt3_$j']}
    WHERE ID = '$k'") or die(mysql_error());

 

I use $j and $k variables because i'm updating row by row in while loop and $j is also used by generating texboxes names in html table.

 

error wich query gives me is : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' column2 = , column3 = WHERE ID = '8'' at line 1

 

 

 

 

[Drummin]

You might give this a go.

mysql_query("UPDATE table SET column1='$_POST[txt1_$j]', column2='$_POST[txt2_$j], column3='$_POST[txt3_$j]'
    WHERE ID = '$k'") or die(mysql_error());

[fugix]

Yeah, looks like you need to wrap you field values in single quotes

[Pikachu2000]

Remove the query string from the query execution. Form the query string in a variable and echo it along with the error. Without knowing what the actual query string contains, debugging it is a craps shoot.

 

$query = "SELECT whatever FROM table WHERE something = something_else";
if( !$result = mysql_query($query) ) {
     echo "<br>Query $query<br>Failed with error " . mysql_error() . '<br>';
}

[glava]

You might give this a go.

mysql_query("UPDATE table SET column1='$_POST[txt1_$j]', column2='$_POST[txt2_$j], column3='$_POST[txt3_$j]'
    WHERE ID = '$k'") or die(mysql_error());

 

That works if i dont have $j in post like :  column1='$_POST[txt1_1]' , but if i put my $j i get Error :

 

Parse error: syntax error, unexpected T_VARIABLE, expecting ']' in /home/.../indexa.php on line 91

line 91 is that query

 

but if i put my $j and '{  like :  column1='{$_POST['txt_$j']}'  i got no error but also it doesnt update DB with value from that txtbox ??

[glava]

Remove the query string from the query execution. Form the query string in a variable and echo it along with the error. Without knowing what the actual query string contains, debugging it is a craps shoot.

 

$query = "SELECT whatever FROM table WHERE something = something_else";
if( !$result = mysql_query($query) ) {
     echo "<br>Query $query<br>Failed with error " . mysql_error() . '<br>';
}

 

ok, here is error after i do that :

 

Fatal error: Function name must be a string in /home/.../indexa.php on line 91

 

and line 91 is query itself

 

also it doesnt mathers if i leave my variable like : column1='{$_POST['txt_$j']}'

or i put a number like : column1='$_POST[txt1_1]'

 

error is same.. ?

[Skylight_lady]

What is the $_POST coming from the form? Give us the full line. I'm guessing it's.

$j = $_POST['something'];

 

Therefore, u can do.

$m = "txt_".$j;

 

And add to the SQL,

column1 = '$m'

[glava]

What is the $_POST coming from the form? Give us the full line. I'm guessing it's.

$j = $_POST['something'];

 

Therefore, u can do.

$m = "txt_".$j;

 

And add to the SQL,

column1 = '$m'

 

ok, i'm printing rows from mysql table into html table with WHILE loop, three columns are numeric values and i print them in textbox like :

 

<td><input type="text" name="txt1_<?php print $i;?>" value="<?php echo $redak["k1O"]?>" size="1" maxlength="1"/></td>

 

$i variable is value 1 and goes i++ in every step of loop so it generates textbox names like txt1_1 , txt1_2 , txt1_3 ... also for txt2_$i and txt3_$i . I want to user enter value in textbox and with submit button saves that new value in mysql table (with update). I put update query also in while loop and used $j wich is same like $i after html table is printed ( $j = $i ) and that's number of last txtbox (also number of rows). then i update 1 by 1 row in revers $j-- in every step of update loop until $j > 1 . Unique ID of every row in mysql table is same like txtbox number so i use it for WHERE clause like WHERE ID = '$j' .

 

so i got query like this :

 

$query = "UPDATE table_name SET k1O = '".$_POST['txt1_".$j."']."' , k2O = '".$_POST['txt2_".$j."']."' , k3O = '".$_POST['txt3_".$j."']."' WHERE ID = '".$j."'";
    mysql_query( $query ) or die(mysql_error());

 

with syntax like this i dont get any error but also i dont update mysql table with new values but if i remove $j from query like this :

 

$query = "UPDATE table_name SET k1O = '".$_POST['txt1_1']."' , k2O = '".$_POST['txt2_1']."' , k3O = '".$_POST['txt3_1']."' WHERE ID = '1'";
    mysql_query( $query ) or die(mysql_error());

 

update is then good. numbers from 3 txtboxes goes in mysql table where ID is 1 .. So the $j is problem in query !? is there another way ?

[Pikachu2000]

This now has no parse errors, but as I said previously, you need to echo the query string when you have problems with it so you know what is actually being used instead of trying to guess. You should also be validating/sanitizing the incoming form data to help prevent SQL injection.

 

$query = "UPDATE table_name SET k1O = '" . $_POST["txt1_$j"] . "', k2O = '" . $_POST["txt2_$j"] . "', k3O = '" . $_POST["txt3_$j"] . "' WHERE ID = '$j'";

[PFMaBiSmAd]

Using a series of name/numbered variables/fields is the HARDEST way of doing something for a SET of same/similar type data.

 

If you use arrays for the form field names, you can use simple php array functions, such as a foreach loop, to iterated over the data. See this link - http://us2.php.net/manual/en/faq.html.php#faq.html.arrays

 

You would typically use the database id value as the array index value so that you can associate the submitted data with the row(s) to update in the database.

[PFMaBiSmAd]

Sample code using arrays for the form fields -

 

<?php

$fields = array('k1O','k2O','k3O'); // list of table fields (used to generate form fields and to process form data)

// the form processing code
if(isset($_POST['submit'])){
echo '<pre>',print_r($_POST,true),'</pre>'; // check out the actual form data
foreach($_POST[$fields[0]] as $key => $value){
	// key will be the id. value is not directly used
	$key = intval($key); // cast as integer
	$sets = array();
	foreach($fields as $field){
		$sets[] = "$field = " . intval($_POST[$field][$key]);
	}
	$query = "UPDATE your_table SET ".implode(',',$sets)." WHERE id = $key";
	echo $query . '<br />'; // check out the actual query statement
	// execute your query here...
}
}

// the form
echo "<form action='' method='post'>\n<table>\n";
while($row = mysql_fetch_assoc($result)){
echo "<tr>\n";
foreach($fields as $field){
	echo "<td><input type='text' name='{$field}[{$row['id']}]' value='{$row[$field]}' size='1' maxlength='1'/></td>\n";
}
echo "</tr>\n";
}
echo "</table>\n<input type='submit' name='submit'>\n</form>\n";
?>

[glava]

I did it , it works now ! It was a syntax problem and if anybody would ever need something like this here's how it should be :

 

$query = "UPDATE studenti SET k1O = '".$_POST['k1Otxt_'.$j]."' , k2O = '".$_POST['k2Otxt_'.$j]."' , k3O = '".$_POST['k3Otxt_'.$j]."' WHERE ID = '".$k."'";
    mysql_query( $query ) or die(mysql_error());

 

thank you freaks for your time and willing to help !