Greywacke Posted August 9, 2010 Share Posted August 9, 2010 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. Link to comment Share on other sites More sharing options...
Greywacke Posted August 9, 2010 Author Share Posted August 9, 2010 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 15it needs to be looking for the right side in the left side - but i don't know what to do! Link to comment Share on other sites More sharing options...
Greywacke Posted August 9, 2010 Author Share Posted August 9, 2010 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} Link to comment Share on other sites More sharing options...
Greywacke Posted August 10, 2010 Author Share Posted August 10, 2010 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.