MySQL update form problem

[ddarsow]

I have a form which pre-fills some text areas from the results of a MySQL query and is intended to update the record when submitted. For some reason though the values reset to the prefilled data and do not edit when submitting.

The form code is:

<?php
if (isset($_GET['id'])) {
$id = $_GET['id'];
}
?>

<?
$username="***";
$password="***";
$database="***";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM categories WHERE cat_id=$id";
$result=mysql_query($query);

$catname=mysql_result($result,$i,"cat_name");
$catorder=mysql_result($result,$i,"cat_order");
$catdescription=mysql_result($result,$i,"cat_description");
$catsorting=mysql_result($result,$i,"cat_sorting");
$cathide=mysql_result($result,$i,"cat_hide");
$catwholesale=mysql_result($result,$i,"cat_wholesale");

mysql_close();



echo"
<p class=\"nopad\">Simply modify the information below to modify this category.<br>Need help with the form? Hover your cursor on the [?] beside each field to learn more.</p>
<table class=\"main\" cellspacing=\"0\">
<tbody>
	<tr>
		<td class=\"head\" colspan=\"3\">
<h1 class=\"table\">Edit an Existing Category</h1>
		</td>
	</tr>
	<tr cellpadding=\"5\">
		<td class=\"padleft\" clospan=\"3\">
		<p>
		<form class=\"mid\" action=\"updatecat.php\" method=\"post\" onsubmit=\"this.submit(); this.reset(); return false\">
<strong>Category Name:</strong> <input type=\"text\" name=\"name\" value=\"$catname\" size=\"35\"> <strong><a class=\"tip\" href=\"#\">[?]<span>The category name will be displayed in the left navigation on your site.</span></a></strong><br><br>
<input type=\"hidden\" name=\"order\" value=\"$catorder\">
<strong>Category Description:</strong><br><textarea cols=\"75\" rows=\"10\" name=\"description\">$catdescription</textarea> <strong><a class=\"tip\" href=\"#\">[?]<span>The category description displays above the products on the category page. You may use html code to include images and format the text if desired. For more information on using html, see the main help link above or contact opti-CART</span></a></strong><br><br>
<strong>Sort Products By:</strong> 
<SELECT NAME=\"sort\">
	<OPTION>$catsorting</OPTION>
	<OPTION>SKU ASC</OPTION>
	<OPTION>SKU DESC</OPTION>
	<OPTION>Price ASC</OPTION>
	<OPTION>Price DESC</OPTION>
</SELECT> <strong><a class=\"tip\" href=\"#\">[?]<span>This simply determines how the products are sorted on the category page.</span></a></strong><br>
<strong>Hidden?:</strong> 
<SELECT NAME=\"feature\">
	<OPTION>$cathide</OPTION>
	<OPTION>N</OPTION>
	<OPTION>Y</OPTION>
</SELECT> <strong><a class=\"tip\" href=\"#\">[?]<span>Hidden categories will not display on your site. This is useful when you are working on a new category, for seasonal categories, or any time you wish to not show some products.</span></a></strong><br>
<strong>Wholesale?:</strong> 
<SELECT NAME=\"feature\">
	<OPTION>$catwholesale</OPTION>
	<OPTION>N</OPTION>
	<OPTION>Y</OPTION>
</SELECT> <strong><a class=\"tip\" href=\"#\">[?]<span>If the wholesale option is enabled the category will only display when a registered wholesale customer logs in to the site. Wholesale categories are hidden from retail customers.</span></a></strong>

<br><br><input type=\"Submit\" value=\"Save Changes\"><br> 
</form>
		</p>
		</td>
	</tr>
	<tr>
		<td class=\"head\" colspan=\"3\" height=\"30px\">
		</td>
	</tr>
</tbody>
</table>"
?>

 

The page it submits to is:

<?
$username="***";
$password="***";
$database="***";

$id=$_POST['id'];
$name=$_POST['name'];
$order=$_POST['order'];
$description=$_POST['description'];
$sort=$_POST['sort'];
$hide=$_POST['hide'];
$wholesale=$_POST['wholesale'];


mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "UPDATE categories SET cat_id='$id', cat_nam='$name', cat_order='$order', cat_description='$description', cat_sort='$sort', cat_hide='$hide', cat_wholesale='$wholesale' WHERE cat_id='$id'";

mysql_query($query);

mysql_close();

?>

<script language="javascript">
location.replace("admin.php?page=category");
</script>

 

Anyone know how to make this actually edit the table and not reset to the original values pulled from the database when submitting?

Thanks,

Dave

[kickstart]

Hi

 

Suspect there are 2 issues.

 

The bit onsubmit=\"this.submit(); this.reset(); return false\" will submit the form and reset all the fields, so what is displayed will be what was originally displayed.

 

Suspect the reason the actual update doesn't happen is an SQL issue. First issue is that you are updating the key field, which is probably going to cause an issue. However there is no "id" field in the form either.

 

All the best

 

Keith

[ddarsow]

OK, once again copy & paste to create one form from another is not always a time saver! I removed the this.reset(); from the form tag so it now reads:

<form class=\"mid\" action=\"updatecat.php\" method=\"post\" onsubmit=\"this.submit();\">

 

Also, I removed the id update & added an id field to the form.

 

But,...still not working. It does not seem to revert back to the default when hitting the button, but it does not update the database either.

The update code now reads:

<?
$username="***";
$password="***";
$database="***";

$id=$_POST['id'];
$name=$_POST['name'];
$order=$_POST['order'];
$description=$_POST['description'];
$sort=$_POST['sort'];
$hide=$_POST['hide'];
$wholesale=$_POST['wholesale'];


mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "UPDATE categories SET cat_nam='$name', cat_order='$order', cat_description='$description', cat_sorting='$sort', cat_hide='$hide', cat_wholesale='$wholesale' WHERE cat_id='$id'";

mysql_query($query);

mysql_close();

?>

<script language="javascript">
location.replace("admin.php?page=category");
</script>

[kickstart]

Hi

 

Change the mysql_query statement to:-

 

mysql_query($query) or die(mysql_error()." ".$query);

 

See what the error it puts out (if any).

 

All the best

 

Keith

[ddarsow]

(kicking my own butt)

I had a typo in one of the table field names; I had cat_nam & should have been cat_name

I looked over the code about 200 times and I swear I read name every time. Darn stupid brain.

Thanks!