MySQL complex query help for a Newbie
This is the code for my action="blah.php" page following the submit of my html page form. My form contained a call to an ajax/javascript drop down box which pulled information from my category table and subcategory table. That script used the cat_id to draw up the first category name drop down box, and then used the matching cat_id field from the subcategory table to draw up the subcategory name field.
I have tried multiple insert and update commands to try and get the sub_id from the subcategory table to be inserted into my item_cat_associations table.
This is my latest attempt at getting to that sub_id. I have used a select and fetch scheme which does output the correct sub_id now, but I still can't get this script to put that sub_id into my item_cat_associations table.
Help...please.
$connect = mysql_connect($dbhost, $dbusername, $dbpassword);
//Select the correct database.
$db = mysql_select_db($dbname,$connect) or die ("Could not select database" . mysql_error());
$result=mysql_query("SELECT subcategory.sub_id FROM subcategory WHERE subcategory.subcategory=('$HTTP_POST_VARS[subcat]')");
while($myrow=mysql_fetch_assoc($result))
{ $sub_id=$myrow["sub_id"];
echo "\$sub_id is $sub_id";
echo "<br />";
}
$sub_id=$_GET['sub_id'];
$result = mysql_query("INSERT INTO stuff (item_name, dtime, item_url, thumb_url, item_desc)VALUES ('$_POST[item_name]',NOW(),'$_POST[item_url]','$_POST[thumb_url]','$_POST[item_desc]')",$connect);
$result = mysql_query("INSERT INTO item_cat_associations (item_id) SELECT stuff.item_id FROM stuff WHERE stuff.item_id = LAST_INSERT_ID()",$connect);
$result = mysql_query("UPDATE item_cat_associations SET cat_id='$HTTP_POST_VARS[cat]' WHERE id=LAST_INSERT_ID()", $connect);
$result = mysql_query("UPDATE item_cat_associations SET sub_id = '$HTTP_POST_VARS[sub_id]' WHERE id=LAST_INSERT_ID()",$connect);
while (list ($key,$val) = each ($_POST)) {
echo "\$$key = $val";
echo "<br>";
}
mysql_close($connect)
?>