Ahh, I Need Help With My Basket Script

[chibineku]

I am going mad here. I keep thinking I've cracked what I have to do, but I can't keep it straight in my head.When someone is browsing my site, I want them to be able to add items to their basket, associated in my database table sessionBasket with their session id. Then, when they sign in, I want to get all items with the current session id and put them in another table, userBasket. When users are signed in, though, I want the items in userBasket to persist, so next time if someone is browsing, not signed in, and they add an item to their basket, when they sign in, I want to effectively merge the old userBasket with the new userBasket.So, I need to select all items from the sessionBasket table first and for each one, check if it's product_id matches a row in the userBasket that is associated with the current userid. If it is, I need to get the old quantity from the userBasket and add to it the new quantity (from the sessionBasket) in an update query.If there isn't already a product with the product_id in the userBasket table, I make an insert query and move on to the next item in the sessionBasket table that has the right session id.I have strarted to code this but I can't keep it straight. Here is what I have (so far only for users logging in with their username and password, not their email address, but it's a straight copy of the first part.Here is it - help me sort it out, please!!

<?phpsession_start();include_once("db_include.php5");doDB();//check for required fields from the formif ((empty($_POST['username']) && empty($_POST['email'])) || empty($_POST['password'])) {		$_SESSION["username"] = trim(mysqli_real_escape_string($mysqli, check_chars_username($_POST["username"])));	  $_SESSION["email"] = trim(mysqli_real_escape_string(check_chars_username($_POST["email"])));header("Location: loginform.php5?error=ef");exit;}   else if($_POST["username"] && $_POST["password"]){	//create and issue the query	$sql = "SELECT id, username, email FROM aromaMaster WHERE username='".$_POST["username"]."' AND password=PASSWORD('".$_POST["password"]."')";	$sql_res =mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));	//get the number of rows in the result set; should be 1 if a match	if(mysqli_num_rows($sql_res) != 0) {	  //if authorized, get the userid	  while($info = mysqli_fetch_array($sql_res)) {		$userid = $info["id"];		$username = stripslashes($info["username"]);		$email = stripslashes($info["email"]);	  }	  //set session variables	  $_SESSION["userid"] = $userid;	  $_SESSION["username"] = $username;	  $_SESSION["email"] = $email;	  session_write_close();	  //select all rows in sessionBasket with the current session id	  $select_sql = "SELECT product_id, qty, notes FROM sessionBasket WHERE usersessid = '".session_id()."'";	  $select_res = mysqli_query($mysqli, $select_sql) or die(mysqli_error($mysqli));	  $newInfo = mysqli_fetch_array($select_res);	  $newQty = $newInfo["qty"];	  //if no rows, then do nothing	  if(mysqli_num_rows($select_res) == 0) {		header("Location: your_basket.php5?res=loggedin_noitems");		exit();	  } else if(mysqli_num_rows($select_res) > 0) {	  //else, for each item, check if there is an existing item of that kind in the basket	  while($row = mysqli_fetch_array($select_res)) {		//check for pre-existing basket items for this account		$old_items_sql = "SELECT qty FROM userBasket WHERE userid = '".$userid."' AND product_id = '".$product_id."'";		$old_items_res = mysqli_query($mysqli, $old_items_sql) or die(mysqli_error($mysqli));				if(mysqli_num_rows($old_items_res) > 0) {		  //pre-existing items - update qty		  $oldInfo = mysqli_fetch_array($old_items_res);		  $oldQty =  $oldInfo["qty"];		  		  $update_sql = "UPDATE userBasket SET qty=".intval($oldQty+$newQty)." WHERE product_id = '".$product_id."' AND usersessid = '".session_id();"'";		  $update_res = mysqli_query($update_sql) or die(mysqli_error($mysqli));		  		  if(mysqli_num_rows($update_res) == 0) {			//failed			header("Location:your_basket.php5?failed=update5060");			exit();		  } else if(mysqli_num_rows($update_res) > 0) {			//success			header("Location:your_basket.php?success=update5060");			exit();		  }		 } else if(mysqli_num_rows($old_items_res) == 0) {		  //no preexisting items - insert the current ones		  $insert_new_sql = "INSERT INTO userBasket (userid, usersessid, date_added, product_id, qty, notes) VALUES (		  '".$userid."',		  '".session_id()."',		  now(),		  '".$product_id."',		  '".$qty."',		  '".$notes."')";		  $insert_res = mysqli_query($mysqli, $insert_new_sql) or die(mysqli_error($mysqli));		 }}	  mysqli_free_result($sql_res);	  //redirect to main page	  header("Location: loginredirect.php5");	  exit; }	} else if($_POST["email"] && $_POST["password"]) {		  //create and issue the query	$sql = "SELECT id, username FROM aromaMaster WHERE email='".$_POST["email"]."' AND password=PASSWORD('".$_POST["password"]."')";	$sql_res =mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));	//get the number of rows in the result set; should be 1 if a match	if(mysqli_num_rows($sql_res) != 0) {	  //if authorized, get the userid	  while($info = mysqli_fetch_array($sql_res)) {		$userid = $info["id"];		$username = stripslashes($info["username"]);		$email = stripslashes($info["email"]);	  }	  //set session variables	  $_SESSION["userid"] = $userid;	  $_SESSION["username"] = $username;	  $_SESSION["email"] = $email;	  session_write_close();	  mysqli_free_result($sql_res);	  $userupdate_sql = "UPDATE sessionBasket SET userid = '".$userid."' WHERE usersessid='".session_id()."'";			  $userupdate_res = mysqli_query($mysqli, $userupdate_sql) or die(mysqli_error($mysqli));	  //redirect to main page	  header("Location: loginredirect.php5");	  exit;	  } else {	  //redirect back to login form	  $_SESSION["username"] = trim(stripslashes(check_chars_username($_POST["username"])));	  $_SESSION["email"] = trim(stripslashes(check_chars_username($_POST["email"])));	  session_write_close();	  header("Location: loginform.php5?error=ef");	  exit;	}  }?>

[justsomeguy]

What part do you need help with?

[chibineku]

Well, mostly I wanted advice on whether that seemed like a good order for things to happen in and if it was a good idea in the first place to have my basket operate like that. I have since changed the design so that if you are not signed in and add items to the basket, then sign in, the items will be moved to a different table, but that if you sign out your items will not be saved. So when people login, I run a script that first clears any items that may be in your basket from last time, then checks the sessionBasket table for items with your session id, and creates entries in the userBasket table with those items associated wiht your user id. It may not be necessary to have two tables in this operation, actually, and simply to associated the session id with the userid. Hm. But anyway, somewhere along the way my add-to-cart script got messed up so I'm going to have to fix that first. Sorry for being vague, I was just going off my head and needed to vent.