a relational cross database comparison function

[Greywacke]

hi all,got a bit of a brainteaser here to work on...firstly, the prices are defined in the database 14_pricecategories.secondly, a group of attribute keys which the services price determining, attribute combinations consist of - exists as 16_serviceattributegroups.thirdly, the various attribute combinations were defined in 15_categoryattributecombinations.an attribute combination is the combined attribute values defined by 16_serviceattributegroups.i've written the following function, and managed to get the client's request into a multidimentional array of keys and values.the second part of the function, selects all the database entries available, and sorts them by combination id, attribute key and attribute value.i seem to have hit a mental block of sorts regarding how to locate the valid price and exiting the loops.here is the function i wrote sofar:

function costbycategory($sid,$attribsarr,$variablepricing) {	if ($variablepricing == 0) {		return $GLOBALS["fixedcost"];	} else {		// step through attribute array, keeping attribute		// keys and values in an array to check.		$clientcombo = array();		foreach ($attribsarr as $key->$val) {			$sql = "SELECT 					16_serviceattributegroups.bigint_ServiceID, 					16_serviceattributegroups.bigint_AttributeKeyID, 					3_serviceattributes.text_AttributeDescription 				FROM 					16_serviceattributegroups 					JOIN 						3_serviceattributes 					ON (						16_serviceattributegroups.							bigint_AttributeKeyID = 							3_serviceattributes.bigint_AttributeID					) 				WHERE 					16_serviceattributegroups.bigint_ServiceID = 						".$sid." AND 					3_serviceattributes.text_AttributeDescription  = 						\"".$key."\";";			$result = mysql_query_errors($sql, $conn , __FILE__ , 				__LINE__ , false);			if ($result) {				if ($row = mysql_fetch_array($result)) {					array_push(						$clientcombo, 						array(						      $row["text_AttributeDescription"],						      $val						)					);				}			}		}		mysql_free_result($result);		// step through recordset, comparing attribute combinations and		// keeping track of combination id's and category prices to keep		// price		$datbcomboid = 0;		$datbctprice = 0;		$servercombo = array;		foreach ($pricingcombo as $arr) {			$sql = "SELECT 					14_pricecategories.bigint_ServiceID, 					14_pricecategories.bigint_CategoryPrice, 					15_categoryattributecombinations.						bigint_CombinationID, 					3_serviceattributes.text_AttributeDescription, 					3_serviceattributes.text_AttributeValue				FROM 					14_pricecategories 				LEFT JOIN 				(					15_categoryattributecombinations, 					3_serviceattributes				)				ON (					14_pricecategories.bigint_ServiceID = 						15_categoryattributecombinations.						bigint_ServiceID AND 					14_pricecategories.bigint_CategoryID = 						15_categoryattributecombinations.						bigint_CategoryID AND 					15_categoryattributecombinations.						bigint_ServiceID = 						3_serviceattributes.bigint_AttributeServiceID 						AND 					15_categoryattributecombinations.						bigint_AttributeValueID = 						3_serviceattributes.bigint_AttributeID				) 				WHERE 					14_pricecategories.bigint_ServiceID = ".$sid." 				ORDER BY 					15_categoryattributecombinations.						bigint_CombinationID ASC, 					3_serviceattributes.text_AttributeDescription ASC, 					3_serviceattributes.text_AttributeValue ASC;";			$result = mysql_query_errors($sql, $conn , __FILE__ , 				__LINE__ , false);			if ($result) {				foreach ($attribids as $key->$val) {					// i seem to be getting lost around here... :/ ******					while ($row = mysql_fetch_array($result)) {						if ($row["text_AttributeDescription"] == 							$key) {							if ($row["text_AttributeValue"] == 								$val) {								if ($row["bigint_CombinationID"] 									== 								    $datbcomboid && 								    $row["bigint_AttributeID"] 									== 								    $datbctprice) {									$datbcomboid = $row[										"bigint_"+										"CombinationID"									];									$datbctprice = $row[										"bigint_"+										"CategoryPrice"									];									array_push(										$servercombo,									// realised i'm stuck									);								}															}						}					}					// i seem to be getting lost around here... :/ ******				}			}			mysql_free_result($result);		}	}}

i know i need to return the price from this function, but i need to find it first. coould somebody please help?

[Greywacke]

haha!just realised i had an unrequired loop going through all the client arrays.here is the function without the unrequired loop

function costbycategory($sid,$attribsarr,$variablepricing) {	if ($variablepricing == 0) {		return $GLOBALS["fixedcost"];	} else {		// step through attribute array, keeping attribute		// keys and values in an array to check.		$clientcombo = array();		foreach ($attribsarr as $key->$val) {			$sql = "SELECT 					16_serviceattributegroups.bigint_ServiceID, 					16_serviceattributegroups.bigint_AttributeKeyID, 					3_serviceattributes.text_AttributeDescription 				FROM 					16_serviceattributegroups 					JOIN 						3_serviceattributes 					ON (						16_serviceattributegroups.							bigint_AttributeKeyID = 							3_serviceattributes.bigint_AttributeID					) 				WHERE 					16_serviceattributegroups.bigint_ServiceID = 						".$sid." AND 					3_serviceattributes.text_AttributeDescription  = 						\"".$key."\";";			$result = mysql_query_errors($sql, $conn , __FILE__ , 				__LINE__ , false);			if ($result) {				if ($row = mysql_fetch_array($result)) {					array_push(						$clientcombo, 						array(						      $row["text_AttributeDescription"],						      $val						)					);				}			}		}		mysql_free_result($result);		// step through recordset, comparing attribute combinations and		// keeping track of combination id's and category prices to keep		// price		$datbcomboid = 0;		$datbctprice = 0;		$servercombo = array;		foreach ($pricingcombo as $arr) {			$sql = "SELECT 					14_pricecategories.bigint_ServiceID, 					14_pricecategories.bigint_CategoryPrice, 					15_categoryattributecombinations.						bigint_CombinationID, 					3_serviceattributes.text_AttributeDescription, 					3_serviceattributes.text_AttributeValue				FROM 					14_pricecategories 				LEFT JOIN 				(					15_categoryattributecombinations, 					3_serviceattributes				)				ON (					14_pricecategories.bigint_ServiceID = 						15_categoryattributecombinations.						bigint_ServiceID AND 					14_pricecategories.bigint_CategoryID = 						15_categoryattributecombinations.						bigint_CategoryID AND 					15_categoryattributecombinations.bigint_ServiceID 						= 						3_serviceattributes.bigint_AttributeServiceID 						AND 					15_categoryattributecombinations.						bigint_AttributeValueID = 						3_serviceattributes.bigint_AttributeID				) 				WHERE 					14_pricecategories.bigint_ServiceID = ".$sid." 				ORDER BY 					15_categoryattributecombinations.						bigint_CombinationID ASC, 					3_serviceattributes.text_AttributeDescription ASC, 					3_serviceattributes.text_AttributeValue ASC;";			$result = mysql_query_errors($sql, $conn , __FILE__ , 				__LINE__ , false);			if ($result) {				// i seem to be getting lost around here... :/ ******				while ($row = mysql_fetch_array($result)) {					if ($row["text_AttributeDescription"] == $key) {						if ($row["text_AttributeValue"] == $val) {							if ($row["bigint_CombinationID"] == 							    $datbcomboid && 							    $row["bigint_AttributeID"] == 							    $datbctprice) {								$datbcomboid = $row[									"bigint_CombinationID"									];								$datbctprice = $row[									"bigint_CategoryPrice"									];								array_push(									$servercombo,									// realised i am stuck 0o								);							}													}					}				}				// i seem to be getting lost around here... :/ ******			mysql_free_result($result);		}	}}

i'm still a bit stuck though... here is what the second resultset looks like:

[b]bigint_ServiceID[/b]	[b]bigint_CategoryPrice[/b]	[b]bigint_CombinationID[/b]1			20			111			20			111 			20			111 			20			111 			20			111 			20			111 			30			171 			30			171 			30			171 			30			171 			30			171 			30			171 			30			231 			30			231 			30			231 			30			231 			30			231 			30 			23[b]text_AttributeDescription[/b]	[b]text_AttributeValue[/b]Budget				R12,500 plusFitment				Within the next 2 weeksRequirement			New - Whitebudget				R12,500 pluscanopy_req			new_whitefitment				within_2_weeksBudget				R12,500 plusFitment				Within the next 2 weeksRequirement			Pre-owned - Colour-codedbudget				R12,500 pluscanopy_req			pre-owned_colour_codedfitment				within_2_weeksBudget				R7,500 to R10,000Fitment				Within the next 2 weeksRequirement			New - Whitebudget				R7,500 to R10,000canopy_req			new_whitefitment				within_2_weeks

[Greywacke]

okay, i seem to have come up with the following for this function to work.i've rather gone through the attribute combinations, and see if the number of matched key and values are equal to the count of the pricing combination pulled from the consumer attribute array. if they are, then return that category price :)took some thinking this one 0o

function costbycategory($sid,$attribsarr,$variablepricing) {	if ($variablepricing == 0) {		return $GLOBALS["fixedcost"];	} else {		$consumercombo = array();		foreach ($attribsarr as $key->$val) {			$sql = "SELECT 					16_serviceattributegroups.bigint_ServiceID, 					16_serviceattributegroups.bigint_AttributeKeyID, 					3_serviceattributes.text_AttributeDescription 				FROM 					16_serviceattributegroups 					JOIN 						3_serviceattributes 					ON (						16_serviceattributegroups.							bigint_AttributeKeyID = 							3_serviceattributes.bigint_AttributeID					) 				WHERE 					16_serviceattributegroups.bigint_ServiceID = 						".$sid." AND 					3_serviceattributes.text_AttributeDescription  = 						\"".$key."\";";			$result = mysql_query_errors($sql, $conn , __FILE__ , 				__LINE__ , false);			if ($result) {				if ($row = mysql_fetch_array($result)) {					array_push(						$consumercombo, 						array(						      $row["text_AttributeDescription"],						      $val						)					);				}			}		}		mysql_free_result($result);		// step through attribute value combinations, comparing attribute		// values with consumer attributes tracked from form above (calc		// and compare max successful hits) 		$sql0 = "SELECT bigint_CombinationID FROM 			15_categoryattributecombinations WHERE 			bigint_ServiceID = ".$sid." GROUP BY bigint_CombinationID 			ORDER BY bigint_CombinationID ASC;"		$result0 = mysql_query_errors($sql0, $conn , __FILE__ , 			__LINE__ , false);		while ($row0 = mysql_fetch_result($result0)) {			$combocount = 0;			$sql1 = "SELECT 					14_pricecategories.bigint_CategoryPrice, 					15_categoryattributecombinations.						bigint_CombinationID, 					3_serviceattributes.text_AttributeDescription, 					3_serviceattributes.text_AttributeValue 				FROM 					15_categoryattributecombinations 				LEFT JOIN 					3_serviceattributes 				ON (					15_categoryattributecombinations.						bigint_AttributeValueID = 3_serviceattributes.						bigint_AttributeID				) 				LEFT JOIN 					14_pricecategories 				ON (					15_categoryattributecombinations.						bigint_CategoryID = 14_pricecategories.						bigint_CategoryID				) 				WHERE 					15_categoryattributecombinations.						bigint_CombinationID = ".$row0[							"bigint_CombinationID"						]." 				ORDER BY 					3_serviceattributes.text_AttributeDescription, 					3_serviceattributes.text_AttributeValue ASC;";			$result1 = mysql_query_errors($sql1, $conn , __FILE__ , 				__LINE__ , false);			while ($row1 = mysql_fetch_array($result1)) {				foreach ($consumercombo as $key->$val) {					if ($row1["text_AttributeDescription"] == $key && 						$row1["text_AttributeValue"] == $val) {							$combocount++;					}					if ($consumercombo == count($clientcombo)) {						return $row1["bigint_CategoryPrice"];					}				}			}		}	}}

this issue seems to be resolved

[Greywacke]

this issue IS resolved