Update Database Error

kingnutter · 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>

zenag · April 1, 2008

try this....

$wid=mysql_query ("UPDATE words SET word = '$word', def = '$def', tags = '$tags', WHERE wid = '$wid'");

jibster · 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

zenag · April 1, 2008

or like this ..

$query = ("UPDATE words SET word = '$word', def = '$def', tags = '$tags', WHERE wid = $wid");

$wid=mysql_query($query);

kingnutter · 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.

jibster · April 1, 2008

I see, in which case its a number so that should be problem solved when you try later.

Cheers

Jib

kingnutter · 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?

zenag · April 2, 2008

can u pls show the code..

kingnutter · 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

ToonMariner · 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.

kingnutter · 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.

kingnutter · 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>

quickstopman · 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

kingnutter · 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

zenag · 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"];

kingnutter · 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

Sign In

Update Database Error

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information