Whats wrong with the query?

storyboo

I'm trying to create an alert in the shopping cart that alerts customers when they add an item they have already purchased before. IT works..and heres the code:

//Were going to perform a check here to see if the product has been purchased already by the customer
		$already = $db->select("SELECT * FROM ".$glob['dbprefix']."CubeCart_order_sum INNER JOIN ".$glob['dbprefix']."CubeCart_order_inv ON ".$glob['dbprefix']."CubeCart_order_sum.cart_order_id = ".$glob['dbprefix']."CubeCart_order_inv.cart_order_id WHERE ".$glob['dbprefix']."CubeCart_order_inv.productId = ".$db->mySQLSafe($productId)." AND ".$glob['dbprefix']."CubeCart_order_sum.customer_id=".$db->mySQLsafe($ccUserData[0]['customer_id']));

HOWEVER, Its alerting them even on cart orders that have been abandoned without paying. So if they come to the store a few days later...they are thinking they already bought the products and I have to explain this issue. So I changed the code to add in the factor of the ORDER STATUS....any status greater than 1 (which is processing)....0 is pending status in the databse.

//Were going to perform a check here to see if the product has been purchased already by the customer
		$already = $db->select("SELECT * FROM ".$glob['dbprefix']."CubeCart_order_sum INNER JOIN ".$glob['dbprefix']."CubeCart_order_inv ON ".$glob['dbprefix']."CubeCart_order_sum.cart_order_id = ".$glob['dbprefix']."CubeCart_order_inv.cart_order_id WHERE ".$glob['dbprefix']."CubeCart_order_inv.productId = ".$db->mySQLSafe($productId)." AND ".$glob['dbprefix']."CubeCart_order_sum.customer_id=".$db->mySQLsafe($ccUserData[0]['customer_id']) [COLOR="Red"]AND status >1[/COLOR]);

When I view the result of this I get a sql error saying...query return no results inthe browser. What is wrong with this query?

The status is stored in the order_sum table.

dagon

no results is not an error, there are simply no results that match the query.

storyboo

Hmmm well since I was logged into a test customer account with 15 orders...some were pending and some were actual orders paid for..what does that mean 😛

Do I need to add an else statement to the code:

Heres the full code:

if($ccUserData[0]['customer_id']>0){

	//Were going to perform a check here to see if the product has been purchased already by the customer
	$already = $db->select("SELECT * FROM ".$glob['dbprefix']."CubeCart_order_sum INNER JOIN ".$glob['dbprefix']."CubeCart_order_inv ON ".$glob['dbprefix']."CubeCart_order_sum.cart_order_id = ".$glob['dbprefix']."CubeCart_order_inv.cart_order_id WHERE ".$glob['dbprefix']."CubeCart_order_inv.productId = ".$db->mySQLSafe($productId)." AND ".$glob['dbprefix']."CubeCart_order_sum.customer_id=".$db->mySQLsafe($ccUserData[0]['customer_id'])) AND status > 1;

	$alreadyPurchased=fetchDbConfig("Already_Purchased");

	if($already==TRUE && $alreadyPurchased['status']==1){

	$view_cart->assign("TD_CART_CLASS",cellColor($i, $tdEven="tdcartEven", $tdOdd="tdcartOdd"));
	$view_cart->assign("ALREADY_PURCHASED_WARNING",$alreadyPurchased['message']);

	$view_cart->parse("view_cart.cart_true.repeat_cart_contents.already_purchased");

	}


	}

dagon

echo the query and compare it to the data

storyboo

I'm not sure that would be so easy to do as I would need to do it in a test environment without disturbing the shopping cart..i have like 150 people on the website right now create orders lol.

dagon

echo it to a file or email