Update not working properly

[smccoy]

First off I'm mostly a noob at php and mysql so be kind if I ask something stoopid!

Ok So I have a db with a table that users can add website links to, and display them on a links page

I wanted to add categories to the links, so I created a second table that just has the link categories. I also made a page to edit the categories. I want to make it so when a category is updated it will also update the fields in the links table that has the old category.

Here is my code:

<?
// includes
include("conf.php");
include("functions.php");

// form not yet submitted
// display initial form with values pre-filled
if (!$submit)
{
// 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 = "SELECT * FROM linkcat WHERE id = '$id'";
$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);
	$oldcat = $row->catagory;

	// print form with values pre-filled

?>
<table cellspacing="5" cellpadding="5">
<form action="<? echo $PHP_SELF; ?>" method="POST">
<input type="hidden" name="id" value="<? echo $id; ?>">
<tr>
<td valign="top"><b><font size="-1">Old Catagory: <input size="30" maxlength="200" type="text" name="catagory" value="<? echo $oldcat; ?>"></font></b></td>
</tr>
<tr>
<td colspan=2><input type="Submit" name="submit" value="Update"></td>
</tr>
</form>
</table>
<?
}
// no result returned
// print graceful error message
else
{
echo "<font size=-1>That catagory could not be located in our database.</font>";
}
}
// form submitted
// start processing it
else
{
// set up error list array
$errorList = array();
$count = 0;

// validate text input fields
if (!$catagory) { $errorList[$count] = "Invalid entry: catagory"; $count++; }

// 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 linkcat SET catagory = '$catagory' WHERE id = '$id'";
	$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

	// generate and execute query to update links with new catagory
	$query = "UPDATE links SET catagory = '$catagory' WHERE catagory = '$oldcat'";
	$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

	// print result
	echo "Update successful. Catagory: $oldcat has been updated to $catagory <a href=listcat.php>Go back to menu</a>";

	// close database connection
	mysql_close($connection);
}
else
{
	// errors occurred
	// print as list
	echo "The following errors were encountered: <br>";
	echo "<ul>";
	for ($x=0; $x<sizeof($errorList); $x++)
	{
		echo "<li>$errorList[$x]";
	}
	echo "</ul>";
}

}
?>

The first problem that I know of is $oldcat is losing ist value somewhere, it works when I first disply the info, but when I get to the UPDATE query it is gone. I guess there is probably more wrong with it but I'm stuck!

Thanks!

[richie]

You have

catagory = '$catagory'

I think you want

category = '$catagory'

In any event, watch your spellings and be consistent. What does stoopid mean? I can guess of course, but inconsistent spellings could cost you.

Richie.

[smccoy]

nope, i started with a typo and I'm sticking with it for consistancy.
its misspelled in every table, field and variable, and I'm just gonna leave it cause I dont want to go back thru all the pages and fix it.
As it is now it does update the catagory field in the linkcat table, it just doesnt update any catagory fields in the links table.

Sean

[richie]

Maybe I'm reading your code wrong, but I don't see a field named "oldcat" in your form.

Add a hidden field with a value of $oldcat.

Otherwise you need to do

$query = "UPDATE links SET catagory = '$catagory' WHERE catagory = '$catagory'";

One piece of advice:

I think you should stop relying on php automatically creating your post variables for you.

Intead of

$catagory,

do

$_POST[catagory], or $HTTP_POST_VARS[catagory].

Richie.

[smccoy]

Add a hidden field with a value of $oldcat.

Oh I think thats what I was doing wrong I was setting $oldcat=$row->catagory before the form, so after submitting it it would lose its value...

One piece of advice:

I think you should stop relying on php automatically creating your post variables for you.

Intead of

$catagory,

do

$_POST[catagory], or $HTTP_POST_VARS[catagory].

I'm not sure what you mean by this?
Where does this go in the code?

Sean

[richie]

Normally when you submit a form, let's say a field is named lastName, you get the value of lastName from the variable $lastName.

You can't always be sure that $lastName will be created by PHP automatically.

So rather than $lastName, you should use $_POST['lastName']. For an older php version, use $HTTP_POST_VARS['lastName'].

[smccoy]

Originally posted by richie
So rather than $lastName, you should use $_POST['lastName']. For an older php version, use $HTTP_POST_VARS['lastName'].

Ok I think I get what your saying, so after the form is submitted and I am running the query I should use $_POST['catagory'] instead of $catagory

like this?

$query = "UPDATE linkcat SET catagory = '$_POST['catagory'] WHERE id = '$id'";