[SOLVED] UPDATE help

[joshgarrod]

Hi all, i have an update command that doesnt work. any ideas?

 

Error:

ERROR: 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 '(Title, WelcomeText) VALUES ('HELLO','HELLO')' at line 1 UPDATE Welcome (Title, WelcomeText) VALUES ('HELLO','HELLO')

 

   <?
    $usr = "USERNAME";
    $pwd = "PASWORD";
    $db = "DATABASE";
    $host = "host";

    # connect to database
    $cid = mysql_connect($host,$usr,$pwd);
    if (!$cid) { echo("ERROR: " . mysql_error() . "\n");    }

?>
   </font></p>
   <TITLE></TITLE>
<P>Update welcome message    </P>

   <font face="Arial, Helvetica, sans-serif">
   <?
    # this is processed when the form is submitted
    # back on to this page (POST METHOD)
    if ($REQUEST_METHOD=="POST") {

        # double-up apostrophes
        $Title = str_replace("'","''",$Title);
        $WelcomeText = str_replace("'","''",$WelcomeText);

        # setup SQL statement
        $SQL = " UPDATE Welcome ";
        $SQL = $SQL . " (Title, WelcomeText) VALUES ";
        $SQL = $SQL . " ('$Title','$WelcomeText') ";


        #execute SQL statement
        $result = mysql_db_query($db,"$SQL",$cid);
	$ID=mysql_insert_id();


        # check for error
        if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n");    }

        echo ("<P>Welcome message updated</P>\n");

    }

?>
   </font>
<FORM NAME="fa" ACTION="index.php?page=welcomeMessage" METHOD="POST">
<TABLE>
<TR>
  <TD><font face="Arial, Helvetica, sans-serif"><B>Title: </B> </font></TD><TD><font face="Arial, Helvetica, sans-serif">
  <INPUT NAME="Title" TYPE="text" id="Title" value="<?php echo $Title; ?>" SIZE=40>
</font></TD></TR>
<TR>
  <TD height="96" VALIGN=TOP><font face="Arial, Helvetica, sans-serif"><B>Welcome message : </B> </font></TD><TD><p><font face="Arial, Helvetica, sans-serif"><textarea name="WelcomeText" cols=40 rows=5 id="WelcomeText"><?php echo $WelcomeText; ?></textarea>
</font></p>
    </TD>
</TR>
<TR>
  <TH COLSPAN=2><font face="Arial, Helvetica, sans-serif">
    <input name="submit" type="submit" value="Update message" />
  </font></TH>
</TR>
</TABLE>
<p align="center"> </p>
</FORM>


   <font face="Arial, Helvetica, sans-serif">
   <?    mysql_close($cid); ?>

[revraz]

UPDATE is not the same as INSERT, it uses different parameters.

 

http://dev.mysql.com/doc/refman/5.0/en/update.html

[irvieto]

Try

 

 

<?php
$SQL = "UPDATE Welcome SET Title='$Title',WelcomeText='$WelcomeText')";
?>

[joshgarrod]

with that i got:

 

ERROR: 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 ') (Title, WelcomeText) VALUES ('hello','hellou')' at line 1 UPDATE Welcome SET Title='hello',WelcomeText='hellou') (Title, WelcomeText) VALUES ('hello','hellou')

[revraz]

$SQL = "UPDATE Welcome SET Title='$Title',WelcomeText='$WelcomeText' WHERE recordid = '$x'"; //$x being the record id

 

You also need to add a WHERE clause for what record you are UPDATING, and you want to remove the $SQL = $SQL + code as well.

 

with that i got:

 

ERROR: 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 ') (Title, WelcomeText) VALUES ('hello','hellou')' at line 1 UPDATE Welcome SET Title='hello',WelcomeText='hellou') (Title, WelcomeText) VALUES ('hello','hellou')

[joshgarrod]

hi sorry, i put the piece of code in but i am still getting errors, as you can tell i am complete noob at this. could you put it into my code please and i will try that.

[revraz]

Would be better if you learned how they worked.

 

http://www.freewebmasterhelp.com/tutorials/phpmysql

[joshgarrod]

I have done the tutorials before, i have given it a go myself this time, still getting an error but it looks in better shape, i took a query from phpmyadmin...

 

   <?
    $usr = "sto21";
    $pwd = "ixhp388";
    $db = "sto21";
    $host = "localhost";

    # connect to database
    $cid = mysql_connect($host,$usr,$pwd);
    if (!$cid) { echo("ERROR: " . mysql_error() . "\n");    }

?>
   </font></p>
   <TITLE></TITLE>
<P>Update welcome message    </P>

   <font face="Arial, Helvetica, sans-serif">
   <?
    # this is processed when the form is submitted
    # back on to this page (POST METHOD)
    if ($REQUEST_METHOD=="POST") {

        # double-up apostrophes
        $Title = str_replace("'","''",$Title);
        $WelcomeText = str_replace("'","''",$WelcomeText);

        # setup SQL statement
	$SQL = "UPDATE `Welcome` SET `Title` = '$Title',`WelcomeText` = '$WelcomeText' WHERE `Welcome`.`ID` =1 LIMIT 1 ;";
        $SQL = $SQL . " (Title, WelcomeText) VALUES ";
        $SQL = $SQL . " ('$Title','$WelcomeText') ";


        #execute SQL statement
        $result = mysql_db_query($db,"$SQL",$cid);
	$ID=mysql_insert_id();


        # check for error
        if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n");    }

        echo ("<P>Welcome message updated</P>\n");

    }

?>
   </font>
<FORM NAME="fa" ACTION="index.php?page=welcomeMessage" METHOD="POST">
<TABLE>
<TR>
  <TD><font face="Arial, Helvetica, sans-serif"><B>Title: </B> </font></TD><TD><font face="Arial, Helvetica, sans-serif">
  <INPUT NAME="Title" TYPE="text" id="Title" value="<?php echo $Title; ?>" SIZE=40>
</font></TD></TR>
<TR>
  <TD height="96" VALIGN=TOP><font face="Arial, Helvetica, sans-serif"><B>Welcome message : </B> </font></TD><TD><p><font face="Arial, Helvetica, sans-serif"><textarea name="WelcomeText" cols=40 rows=5 id="WelcomeText"><?php echo $WelcomeText; ?></textarea>
</font></p>
    </TD>
</TR>
<TR>
  <TH COLSPAN=2><font face="Arial, Helvetica, sans-serif">
    <input name="submit" type="submit" value="Update message" />
  </font></TH>
</TR>
</TABLE>
<p align="center"> </p>
</FORM>


   <font face="Arial, Helvetica, sans-serif">
   <?    mysql_close($cid); ?>

[revraz]

Remove LIMIT 1

 

Remove

 

$SQL = $SQL . " (Title, WelcomeText) VALUES ";

$SQL = $SQL . " ('$Title','$WelcomeText') ";

 

 

[joshgarrod]

Thanks soo much, brilliant. works lovely now