kingnutter Posted April 1, 2008 Share Posted April 1, 2008 Hi Everyone, I am having trouble with the code to update a MySQL table using php. I have seen the problem tackled before on the forums, but I simply can’t work out where I’m going wrong. This is the error returned: Error in query: UPDATE words SET word = 'Clip', def = 'A short sequence', tags = 'sequence, bin, trim, edit', WHERE wid = '$wid'. 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 'WHERE wid = '$wid' at line 1 And I believe the problem is in this line: $query = ("UPDATE words SET word = '$word', def = '$def', tags = '$tags', WHERE wid = '$wid'"); I would really appreciate any advice regarding the correct syntax, or where I’m going wrong. Full code follows. Thanks all, Gary <html> <head></head> <body> <!-- standard page header begins --> <p> <p> <table width="100%" cellspacing="0" cellpadding="5"> <tr> <td></td> </tr> <tr> <td bgcolor="Navy"<font color="White"> <b><font size="4"> MEDIA THESAURUS</tr> <td bgcolor="Green"<font color="White"> <b><font size="4"> ADMIN EDIT </b></font> </td> </tr> </table> <!--Standard page header ends --> <?php // includes include('conf.php'); include('functions.php'); // form not yet submitted // display initial form with values pre-filled if (!$_POST['submit']) { // check for record ID if ((!isset($_GET['wid']) || trim($_GET['wid']) == '')) { die('Missing record ID!'); } // open database connection $connection = mysql_connect($host, $user, $pass) or die ('Unable to connect!'); // select database mysql_select_db($db) or die ('Unable to select database!'); // generate and execute query $wid = $_GET['wid']; $query = "SELECT word, category, tags, def, pub, author, submitted, amended, flagged FROM words WHERE wid = $wid"; $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error()); // if a result is returned if (mysql_num_rows($result) > 0) { // turn it into an object $row = mysql_fetch_object($result); // print form with values pre-filled ?> <table cellspacing="5" cellpadding="5"> <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST"> <input type="hidden" name="wid" value="<?php echo $wid; ?>"> <tr> <td valign="top"><b><font size="-1">Main Entry</font></b></td> <td> <input size="50" maxlength="50" type="text" name="word" value="<?php echo $row->word; ?>"> </td> </tr> <tr> <td valign="top"><b><font size="-1">Definition</font></b></td> <td> <input size="50" maxlength="500" type="text" name="def" value="<?php echo $row->def; ?>"> </td> </tr> <tr> <td valign="top"><font size="-1">Tags</font></td> <td> <input size="50" maxlength="200" type="text" name="tags" value="<?php echo $row->tags; ?>"> </td> </tr> <tr> <td colspan=2> <input type="Submit" name="submit" value="Update"> </td> </tr> </form> </table> <?php } // no result returned // print graceful error message else { echo '<font size=-1>That definition could not be located in our database.</font>'; } } else { // set up error list array $errorList = array(); $word = $_POST['word']; $def = $_POST['def']; $tags = $_POST['tags']; // check for record ID if ((!isset($_POST['wid']) || trim($_POST['wid']) == '')) { die ('Missing record ID!'); } // validate text input fields if (trim($_POST['word']) == '') { $errorList[] = 'Invalid entry: Word'; } if (trim($_POST['def']) == '') { $errorList[] = "Invalid entry: Definition"; } if (trim($_POST['tags']) == '') { $errorList[] = "Invalid entry: Tags"; } // check for errors // if none found... if (sizeof($errorList) == 0) { // open database connection $connection = mysql_connect($host, $user, $pass) or die ('Unable to connect!'); // select database mysql_select_db($db) or die ('Unable to select database!'); // generate and execute query $query = ("UPDATE words SET word = '$word', def = '$def', tags = '$tags', WHERE wid = '$wid'"); $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error()); // print result echo '<font size=-1>Update successful.'; echo '<a href=record.php>Go back to the main menu</a>.</font>'; // close database connection mysql_close($connection); } else { // errors occurred // print as list echo '<font size=-1>The following errors were encountered:'; echo '<br>'; echo '<ul>'; for ($x=0; $x<sizeof($errorList); $x++) { echo "<li>$errorList[$x]"; } echo '</ul></font>'; } } ?> <!-- standard page footer --> </body> </html> Quote Link to comment Share on other sites More sharing options...
zenag Posted April 1, 2008 Share Posted April 1, 2008 try this.... $wid=mysql_query ("UPDATE words SET word = '$word', def = '$def', tags = '$tags', WHERE wid = '$wid'"); Quote Link to comment Share on other sites More sharing options...
jibster Posted April 1, 2008 Share Posted April 1, 2008 Try: $query = ("UPDATE words SET word = '$word', def = '$def', tags = '$tags', WHERE wid = $wid"); Notice that there are no single quotes around $wid now. Only reason I suggest this is that you have a SELECT statement that (i assume must) works and that doesn't have quotes around $wid. If wid is a string then '' are needed. MySQL should be fine if its a number, ie wid = 5. What is wid out of curiosity. Width? Thanks Jib Quote Link to comment Share on other sites More sharing options...
zenag Posted April 1, 2008 Share Posted April 1, 2008 or like this .. $query = ("UPDATE words SET word = '$word', def = '$def', tags = '$tags', WHERE wid = $wid"); $wid=mysql_query($query); Quote Link to comment Share on other sites More sharing options...
kingnutter Posted April 1, 2008 Author Share Posted April 1, 2008 Thanks for your help everyone. I'll give it a try later and let you know how it goes. Jib - "wid" is the PRIMARY KEY in my table of words, which auto-increments each time a new entry is made. I've used it instead of "id" which I thought I may wish to use later as unique identifiers for users. Quote Link to comment Share on other sites More sharing options...
jibster Posted April 1, 2008 Share Posted April 1, 2008 I see, in which case its a number so that should be problem solved when you try later. Cheers Jib Quote Link to comment Share on other sites More sharing options...
kingnutter Posted April 1, 2008 Author Share Posted April 1, 2008 Hi again, I've just tried both of the tips. Zenag, when I use the first one (the one liner) I get "Error in query: Query was empty" returned. The second gives me the same error as in my original post. Out of interest, why should I be assigning a different value to $wid when it is purely there to select the right record in my database? Quote Link to comment Share on other sites More sharing options...
zenag Posted April 2, 2008 Share Posted April 2, 2008 can u pls show the code.. Quote Link to comment Share on other sites More sharing options...
kingnutter Posted April 2, 2008 Author Share Posted April 2, 2008 Hi Zenag, It is exactly as in my first post above, but with the line of code in question replaced. I shall cut and paste the actual code from my editor when I'm at my laptop later on if needed. Many thanks, Gary Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted April 2, 2008 Share Posted April 2, 2008 GUYS GUYS GUYS... while the code layout is not my cup of tea - the only problem with teh query is the comma before the where clause!!!! $query = ("UPDATE words SET word = '$word', def = '$def', tags = '$tags', WHERE wid = '$wid'"); just get rid of that and you shoudl be ok. Quote Link to comment Share on other sites More sharing options...
kingnutter Posted April 2, 2008 Author Share Posted April 2, 2008 Thanks for that Toon. I had a feeling it was syntax but just couldn't see it. I'd be keen to hear how you would layout this code. I have used a template from an exercise in a PHP manual and adapted it accordingly so I am sure it is far from optimised. Quote Link to comment Share on other sites More sharing options...
kingnutter Posted April 2, 2008 Author Share Posted April 2, 2008 That seems much happier. No error message. Trouble is not making any actual changes to the database. If someone could scan my code to establish why the php isn't working with MySQL I would be very grateful. Also, as I said earlier, I am open to any new approach to the layout. Thanks, Gary <html> <head></head> <body> <!-- standard page header begins --> <p> <p> <table width="100%" cellspacing="0" cellpadding="5"> <tr> <td></td> </tr> <tr> <td bgcolor="Navy"<font color="White"> <b><font size="4"> MEDIA THESAURUS</tr> <td bgcolor="Green"<font color="White"> <b><font size="4"> ADMIN EDIT </b></font> </td> </tr> </table> <!--Standard page header ends --> <?php // includes include('conf.php'); include('functions.php'); // form not yet submitted // display initial form with values pre-filled if (!$_POST['submit']) { // check for record ID if ((!isset($_GET['wid']) || trim($_GET['wid']) == '')) { die('Missing record ID!'); } // open database connection $connection = mysql_connect($host, $user, $pass) or die ('Unable to connect!'); // select database mysql_select_db($db) or die ('Unable to select database!'); // generate and execute query $wid = $_GET['wid']; // Am only retrieving key fields at the minute $query = "SELECT word, tags, def FROM words WHERE wid = $wid"; $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error()); // if a result is returned if (mysql_num_rows($result) > 0) { // turn it into an object $row = mysql_fetch_object($result); // print form with values pre-filled ?> <table cellspacing="5" cellpadding="5"> <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST"> <input type="hidden" name="wid" value="<?php echo $wid; ?>"> <tr> <td valign="top"><b><font size="-1">Main Entry</font></b></td> <td> <input size="50" maxlength="50" type="text" name="word" value="<?php echo $row->word; ?>"> </td> </tr> <tr> <td valign="top"><b><font size="-1">Definition</font></b></td> <td> <input size="50" maxlength="500" type="text" name="def" value="<?php echo $row->def; ?>"> </td> </tr> <tr> <td valign="top"><font size="-1">Tags</font></td> <td> <input size="50" maxlength="200" type="text" name="tags" value="<?php echo $row->tags; ?>"> </td> </tr> <tr> <td colspan=2> <input type="Submit" name="submit" value="Update"> </td> </tr> </form> </table> <?php } // no result returned // print graceful error message else { echo '<font size=-1>That definition could not be located in our database.</font>'; } } else { // set up error list array $errorList = array(); $word = $_POST['word']; $def = $_POST['def']; $tags = $_POST['tags']; // check for record ID if ((!isset($_POST['wid']) || trim($_POST['wid']) == '')) { die ('Missing record ID!'); } // validate text input fields if (trim($_POST['word']) == '') { $errorList[] = 'Invalid entry: Word'; } if (trim($_POST['def']) == '') { $errorList[] = "Invalid entry: Definition"; } if (trim($_POST['tags']) == '') { $errorList[] = "Invalid entry: Tags"; } // check for errors // if none found... if (sizeof($errorList) == 0) { // open database connection $connection = mysql_connect($host, $user, $pass) or die ('Unable to connect!'); // select database mysql_select_db($db) or die ('Unable to select database!'); // generate and execute query $query = ("UPDATE words SET word = '$word', def = '$def', tags = '$tags' WHERE wid = '$wid'"); // $query = ("UPDATE words SET word = '$word', def = '$def', tags = '$tags' WHERE wid = '$wid'"); // $wid=mysql_query($query); // $wid=mysql_query ("UPDATE words SET word = '$word', def = '$def', tags = '$tags' WHERE wid = '$wid'"); $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error()); // print result echo '<font size=-1>Update successful.'; echo '<a href=record.php>Go back to the main menu</a>.</font>'; // close database connection mysql_close($connection); } else { // errors occurred // print as list echo '<font size=-1>The following errors were encountered:'; echo '<br>'; echo '<ul>'; for ($x=0; $x<sizeof($errorList); $x++) { echo "<li>$errorList[$x]"; } echo '</ul></font>'; } } ?> <!-- standard page footer --> </body> </html> Quote Link to comment Share on other sites More sharing options...
quickstopman Posted April 2, 2008 Share Posted April 2, 2008 im pretty sure this will make it work $query = "UPDATE words SET word = '$word', def = '$def', tags = '$tags' WHERE wid = '$wid'"; $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error()); i have no idea why you were using ()'s because the query statement would look like mysql_query(("blah")); which is incorrect -Zack Quote Link to comment Share on other sites More sharing options...
kingnutter Posted April 3, 2008 Author Share Posted April 3, 2008 Thanks for the input. My records are still not being updated. I shall go through the code letter by letter unless anyone can see a glaring error in my code above. Cheers all, Gary Quote Link to comment Share on other sites More sharing options...
zenag Posted April 3, 2008 Share Posted April 3, 2008 post variable $wid=$_POST["wid"]; in update section.... like this...its wrkng now.... else { // set up error list array $errorList = array(); $word = $_POST['word']; $def = $_POST['def']; $tags = $_POST['tags']; $wid=$_POST["wid"]; Quote Link to comment Share on other sites More sharing options...
kingnutter Posted April 3, 2008 Author Share Posted April 3, 2008 Genius. This works now. If you have time, please explain why the $wid had to be included in the error array for the update to word. Thanks so much. Gary Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.