update

[PurpleMonkey]

I've got a form that allows the user to update a record that they have entered. I know my SQL syntax is wrong for this, so could someone please point out where I'm going wrong.

$sql = "update terms set property=\"$def\", info=\"$text\", type=\"$type\" where id=$id ";

[AstroTeg]

When you echo $sql to the browser, what is the output (or what is $sql set to)?

[PurpleMonkey]

Um, what is says above.

[AstroTeg]

Um, no.

What is says above is:

$sql = "update terms set property=\"$def\", info=\"$text\", type=\"$type\" where id=$id ";

What is the final output or the final result of $sql, as in what data are you shoving in the variables? And is that data actually getting there? By dumping this string out to the browser and posting it here, we can identify if there's a hang up on the data.

[mrhappiness]

additionally do change your

mysql_query($sql);

to

mysql_query($sql) or die(mysql_error());

and post the result

[PurpleMonkey]

I guess what I'm really asking is how to update multiple columbs with one query.

[Sxooter]

You really should get in the habit of using SQL standard ' to quote data, not ", which most (all but MySQL???) databases will puke on:

$q = "update table set field1='$val1' where id=123";

[PurpleMonkey]

I've echoed $sql to my browser and it says what I exected it to say. What I said before was how do I update more than one column in a row with one[/] query.

[Sxooter]

Well, considering your original query's syntax is exactly correct EXCEPT for quoting with " instead of ', maybe you could go back and try it with ' instead and let us know if it works?

[AstroTeg]

You're positive your data is groovy? That's the reason I was asking if you could post the finished query product so we can check out the data to validate it.

[Sxooter]

Oh yeah, you need to escape your data. All ' marks should become '' inside the strings. so, updating with a string like"

Don O'Neill

would look like:

update table set field1='Don O''Neill' where id=1

[PurpleMonkey]

<link rel="StyleSheet" type="text/css" href="css/style.css">
<form action="<?php echo($PHP_SELF); ?>" method="post">
<table>

<td>Definition:</td><td><input type="text" name="def" value="<?php echo($def); ?>"></td><tr>
<td>Text:</td><td><textarea name="text" cols=15 rows=3><?php echo($info); ?></textarea></td><tr>
<td>Type:</td><td><select name="type">
<?php
echo($list);
?>

</select></td><tr>

<td colspan=2><center><input type="submit" value="submit"></center></td>
</form></table>
<?php if($text and $def and $type) {


$conn = @mysql_connect("localhost","","");

$db = @mysql_select_db("users", $conn);

$sql = "update terms set property=\"$def\" info=\"$text\" type=\"$type\" where id=$id ";

$qr = mysql_query($sql, $conn);
if($qr) {
header("Location:index.php");
}

}

?>

[Sxooter]

What part of change \" to ' and escape ' to '' did you not understand?

[PurpleMonkey]

Um, Changing all of the code on all of the PHP scripts I have would take ages. And I learnt this way and it works, so I think I'll stick with it.

[Sxooter]

Well then, I guess you're on your own.

[csn]

It's trivial to change them. Simple shell script that does it:

#!/bin/csh

foreach file (*.sql)
        sed 's/\\"/'\''/g' $file > $file.new
end