Page 1 of 1

Insert + Update Problem

Posted: Sun Mar 28, 2010 10:59 am
by nitediver
What am I trying to do is, Inserting new record in product table then also updating(one increment) the value in cat_amt(amount of product in this category).

>cat_amt is the amount of product in that category.

But the cat_id doesnt insert into product table, even the value is loaded.

So when I insert new product(into product table) the cat_id always empty.
The cat_amt(in category table) value doesnt change.

This is the current result.
[text]
product table
---------------------------
prod_id| prod_name | cat_id
---------------------------
1 | Red | 1
2 | Blue | 2
3 | Green | 0 >>>New record after insertion.
---------------------------


category table
---------------------------
cat_id | cat_name | cat_amt
---------------------------
1 | Cat_1 | 2
2 | Cat_2 | 4
---------------------------
[/text]

======================================================================

This is my Table.
[text]
product table
---------------------------
prod_id| prod_name | cat_id
---------------------------
1 | Red | 1
2 | Blue | 2
---------------------------


category table
---------------------------
cat_id | cat_name | cat_amt
---------------------------
1 | Cat_1 | 2
2 | Cat_2 | 4
---------------------------
[/text]

Code: Select all

<?
include "function.php";
connect();

	//List category
	function listcat(){
	echo "<select name=\"cat_id\">";
	$loadcat = "SELECT * FROM cat";
	$loadquery= mysql_query($loadcat) or die ("Error");
	while($result=mysql_fetch_array($loadquery))
		{
	echo ("
		<option value=$result[id]>$result[cat_id],$result[cat_name]</option>
		");
	}
	echo "</select>";
	}
	
	//List name
	function listname(){
	echo (" 
	<form name=\"update\" method=\"post\" action=\"$PHP_SELF\">
	prod_name		<input type=\"text\" name=\"prod_name\"><br />
	<input type=\"submit\" value=\"Update\" name=\"submit\">
	</form>
	
	");
	}



if(isset($_POST['submit'])){

//$cat_cnt Increment...
$cat_cnt = $cat_cnt++;

$prod_name = $_POST['prod_name'];
$cat_id = $_POST['cat_id'];
$cat_cnt = $_POST['cat_cnt'];

//It is correct? I want to make it more simple, is it possible?
$update_prod = mysql_query("INSERT INTO prod(prod_name,cat_id) VALUES ('$prod_name','$cat_id')");
$update_cat = mysql_query("UPDATE produk SET cat_jml='$cat_jml' WHERE cat_id='$cat_id'");

//
if($update_prod && $update_cat){
	echo "ok";
}else{
	echo "bad";
	}
}

listcat();
listname();

?>
Any help and advice will be useful.
thanks

Re: Insert + Update Problem

Posted: Sun Mar 28, 2010 12:27 pm
by califdon
You really shouldn't be maintaining a dependent field in the category table, like that. Determining the number of products in a category should be done with a query whenever it is needed. That's the whole point of relational databases.

Re: Insert + Update Problem

Posted: Mon Mar 29, 2010 3:14 am
by nitediver
So, what am I supposed to do, at least give me advice.
Im still new in php and now Im stuck.

Re: Insert + Update Problem

Posted: Mon Mar 29, 2010 4:22 am
by learnerabn
<?
include "function.php";
connect();

//List category
function listcat(){
echo "<select name=\"cat_id\">";
$loadcat = "SELECT * FROM cat";
$loadquery= mysql_query($loadcat) or die ("Error");
while($result=mysql_fetch_array($loadquery))
{
echo ("
<option value=$result[id]>$result[cat_id],$result[cat_name]</option>
");
}
echo "</select>";
}

//List name
function listname(){
echo ("
<form name=\"update\" method=\"post\" action=\"$PHP_SELF\">
prod_name <input type=\"text\" name=\"prod_name\"><br />
<input type=\"submit\" value=\"Update\" name=\"submit\">
</form>

");
}



if(isset($_POST['submit'])){

//$cat_cnt Increment...
$cat_cnt = $cat_cnt++;

$prod_name = $_POST['prod_name'];
$cat_id = $_POST['cat_id'];
$cat_cnt = $_POST['cat_cnt'];

//It is correct? I want to make it more simple, is it possible?
$update_prod = mysql_query("INSERT INTO prod(prod_name,cat_id) VALUES ('$prod_name','$cat_id')");
$update_cat = mysql_query("UPDATE produk SET cat_jml='$cat_jml' WHERE cat_id='$cat_id'");

//
if($update_prod && $update_cat){
echo "ok";
}else{
echo "bad";
}
}

listcat();
listname();

?>
i cant get ur point here why you are calling the two function after executing the query string?
i think it will work if you call the function before the if statement.
i'm not sure since i'm also a newbee.