Jump to content

php comparing "attributes" in binary mysql tables


Greywacke
 Share

Recommended Posts

okay. for namibia, there are 10 suppliers, half of them have the colour-coded, and 3 have a vehicle_make_model attribute matched against them (ie records exists in a table, the sql query below returns the values below that).Query:

SELECT 	3_serviceattributes.text_AttributeDescription, 	3_serviceattributes.text_AttributeValue FROM 	9_supplierattributes LEFT JOIN (	3_serviceattributes) ON (	9_supplierattributes.bigint_AttributeID 	= 3_serviceattributes.bigint_AttributeID) WHERE 	9_supplierattributes.bigint_ServiceID = 1 AND 	9_supplierattributes.bigint_RegionID = 26;

Result:

[b]text_AttributeDescription[/b]	[b]text_AttributeValue[/b]canopy_req			pre-owned_colour_codedRequirement			Pre-owned - Colour-codedcanopy_req			pre-owned_colour_codedRequirement			Pre-owned - Colour-codedRequirement			Pre-owned - Colour-codedcanopy_req			pre-owned_colour_codedRequirement			Pre-owned - Colour-codedcanopy_req			pre-owned_colour_codedRequirement			Pre-owned - Colour-codedcanopy_req			pre-owned_colour_codedproducts_description		GWMproducts_description		Fiatproducts_description		GWMvehicle_make_model		GMWvehicle_make_model		GMWvehicle_make_model		Fiat

however, when i try execute the query below, i get nothing 0o

SELECT 	3_serviceattributes.text_AttributeDescription, 	3_serviceattributes.text_AttributeValue FROM 	9_supplierattributes LEFT JOIN (	3_serviceattributes) ON (	9_supplierattributes.bigint_AttributeID 		= 3_serviceattributes.bigint_AttributeID) WHERE 	"vehicle_make_model" LIKE 		CONCAT("%",LOWER(3_serviceattributes.text_AttributeDescription),		"%") AND 	"Fiat - Strada X Space" LIKE 		CONCAT("%",LOWER(3_serviceattributes.text_AttributeValue),"%") 		AND 	9_supplierattributes.bigint_ServiceID = 1 AND 	9_supplierattributes.bigint_RegionID = 26;

now below is the function that is supposed to drop suppliers from the array of suppliers retrieved from the database by region.

function dropattribs($status) {	switch ($status) {		case 2:			$sarray = "mailaddp";				// prospecting status 2			break;		case 4:			$sarray = "mailaddl";					// listed status 4			break;		default:			$sarray = "mailadds";					// active status 0	}	//echo mysql_real_escape_string("\r\ndropattribs(".$status.")");	for ($i = count($GLOBALS[$sarray]) - 1; $i > -1; $i--) {		$sdata = $GLOBALS[$sarray][$i];				// get supplier data		$arr = explode(";;;",$sdata);					// explode supplier array		$sid = $arr[6];								// get supplier id		//echo mysql_escape_string("\r\n\t".$arr[1]);		reset($GLOBALS["attribsarr"]);		foreach ($GLOBALS["attribsarr"] as $key => $value) {	// begin iterate form attributes			// select current attribute key and value for supplier that is not			// supported			$tsql = "SELECT 					3_serviceattributes.text_AttributeDescription, 					3_serviceattributes.text_AttributeValue 				FROM 					9_supplierattributes 				LEFT JOIN (					3_serviceattributes				) 				ON (					9_supplierattributes.bigint_AttributeID =					 3_serviceattributes.bigint_AttributeID				) 				WHERE 					\"".strtolower($key)."\" LIKE CONCAT(\"%\",						LOWER(3_serviceattributes.							text_AttributeDescription),\"%\") AND 					\"".strtolower($value)."\" LIKE CONCAT(\"%\",						LOWER(3_serviceattributes.							text_AttributeValue),\"%\") AND 					9_supplierattributes.bigint_ServiceID = ".						$GLOBALS["service"]." AND 					9_supplierattributes.bigint_RegionID = ".						$GLOBALS["region"]." AND 					9_supplierattributes.bigint_SupplierID = ".$sid.";";			//echo $tsql."\n";			$result = mysql_query_errors($tsql, $conn , __FILE__ , 				__LINE__ , false);			if ($result) {					// begin if mysql result				if ($row = mysql_fetch_array($result)) { // begin if suppliers !attribs					//echo $GLOBALS[$sarray][$i]."\n";					unset($GLOBALS[$sarray][$i]);	// drop the supplier from the array					mysql_free_result($result);	// free mysql result after dropping					break;				// break out of attributes loop				}						// end while suppliers !attributes				mysql_free_result($result);		// free mysql result after looping			}							// end if mysql result		}								// end iterate form attributes	}									// end iterate suppliers array}

5 suppliers are returned (top level $mailaddp, $mailaddl and $mailadds are altogether populated with) 5 suppliers, but when i test the sql query string as it would execute, it returns nothing - what could be going on? i have run out of ideas and am at the end of the line here...if somebody could please assist, i would be greatly appreciative.

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

ahahahaha! it seems one cannot convert a binary to lowercase using the lower function in mysql. therefore, i would need to add a string conversion.unfortunately i do not know of any utf-8 conversion types though for unicode strings. some more research on that later, but right now in pma i get an error with the following sql:

SELECT 	3_serviceattributes.text_AttributeDescription, 	3_serviceattributes.text_AttributeValue FROM 	9_supplierattributes LEFT JOIN (	3_serviceattributes) ON (	9_supplierattributes.bigint_AttributeID 		= 3_serviceattributes.bigint_AttributeID) WHERE 	"vehicle_make_model" LIKE 		CONCAT("%",LOWER(CONVERT(3_serviceattributes.text_AttributeDescription latin1)),"%") AND 	"fiat - strada x space" LIKE 		CONCAT("%",LOWER(CONVERT(3_serviceattributes.text_AttributeValue latin1)),"%") AND 	9_supplierattributes.bigint_ServiceID = 1 AND 	9_supplierattributes.bigint_RegionID = 26;

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'latin1)),"%") AND "fiat - strada x space" LIKE CONCAT("%",LOWER(CONVERT(3_s' at line 15
it needs to be looking for the right side in the left side - but i don't know what to do! Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

AWESOME!!! :)here is the correct conversion of binary to lowercase utf8, to be used in that query:

LOWER(CONVERT(3_serviceattributes.text_AttributeDescription USING utf8))

unfortunately this issue is not yet resolved. it finds the records, but does not drop the supplier elements from the page level arrays, $mailadds, $mailaddp and $mailaddl. why would this be? i now also use mb_strtolower too. also converting the passed strings as lower utf8...

function dropattribs($status) {	switch ($status) {		case 2:			$sarray = "mailaddp";											// prospecting status 2			break;		case 4:			$sarray = "mailaddl";											// listed status 4			break;		default:			$sarray = "mailadds";											// active status 0	}	//echo mysql_real_escape_string("\r\ndropattribs(".$status.")");	for ($i = count($GLOBALS[$sarray]) - 1; $i > -1; $i--) {		$sdata = $GLOBALS[$sarray][$i];											// get supplier data		$arr = explode(";;;",$sdata);											// explode supplier array		$sid = $arr[6];													// get supplier id		//echo mysql_escape_string("\r\n\t".$arr[1]);		reset($GLOBALS["attribsarr"]);		foreach ($GLOBALS["attribsarr"] as $key => $value) {								// begin iterate form attributes			// select current attribute key and value for supplier that is not supported			$tsql = "SELECT 					3_serviceattributes.text_AttributeDescription, 					3_serviceattributes.text_AttributeValue 				FROM 					9_supplierattributes 				LEFT JOIN (					3_serviceattributes				) 				ON (					9_supplierattributes.bigint_AttributeID = 3_serviceattributes.bigint_AttributeID				) 				WHERE 					\"".mb_strtolower($key,"UTF-8")."\" LIKE CONCAT(\"%\",LOWER(CONVERT(3_serviceattributes.text_AttributeDescription USING utf8)),\"%\") AND 					\"".mb_strtolower($value,"UTF-8")."\" LIKE CONCAT(\"%\",LOWER(CONVERT(3_serviceattributes.text_AttributeValue USING utf8)),\"%\") AND 					9_supplierattributes.bigint_ServiceID = ".$GLOBALS["service"]." AND 					9_supplierattributes.bigint_RegionID = ".$GLOBALS["region"]." AND 					9_supplierattributes.bigint_SupplierID = ".$sid.";";			//echo $tsql."\n";			$result = mysql_query_errors($tsql, $conn , __FILE__ , __LINE__ , false);			if ($result) {												// begin if mysql result				if ($row = mysql_fetch_array($result)) {								// begin while suppliers !attribs					//echo $GLOBALS[$sarray][$i]."\n";					unset($GLOBALS[$sarray][$i]);					mysql_free_result($result);					break;												// break out of attributes loop				}													// end while suppliers !attributes				mysql_free_result($result);										// free mysql result after looping			}														// end if mysql result		}															// end iterate form attributes	}																// end iterate suppliers array}

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

ahah! i didn't see that it was trying to split an array, after about 18 times of going through the function, i did not consciously register that it was trying to explode an array by ";;;". that was a remnant of the supplier strings, which are now saved as a multidimensional array. the supplier id's were therefore not retrieved, neither were the sql queries valid.finally, this issue is RESOLVED! :)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...