UNEXPECTED RESULTS: php values not transferring between mysql queries - wtf?

hi, this synchronization script truncates the test table first - then copies the data over, individually translating the attributes, price categories & services for the new system.
the code is below:
<?php
set_time_limit(0);
header('Cache-Control: no-cache');
header('Pragma: no-cache');
header("Content-type: text/plain; charset=utf-8");
include("includes/content/dwtphovu_f3rr37y.php");							// include database connection
$conn = mysql_connect("localhost", "*****", "*****");
if (!$conn) {
	$err = "ERROR #".mysql_errno()."Could not connect: ".mysql_error()." in ".$doc." on Line ".$line;
	array_push($GLOBALS["sql"],$err."\n");
}// truncate attribute value combinations
$sql26 = "TRUNCATE TABLE dwtphovu_8347379386_test.15_categoryattributecombinations;";
$result26 = mysql_query_errors($sql26, $conn , __FILE__ , __LINE__ , true );
if ($result26) mysql_free_result($result26);
print_r($sql);
flush();
$sql = array();
// synchronize attribute value combinations
$sql27 = "SELECT 
			PAC.bigint_CombinationID, 
			PSC.text_ServiceDescription, 
			PPC.text_CategoryDescription, 
			GROUP_CONCAT(
				(
					SELECT 
						CONCAT(
							PSA1.text_AttributeDescription,
							\"=\",
							PSA1.text_AttributeValue
						) 
					FROM 
						dwtphovu_8347379386_prod.3_serviceattributes PSA1 
					WHERE 
						PSA1.bigint_AttributeID = PAC.bigint_AttributeValueID 
					ORDER BY 
						PSA1.text_AttributeDescription ASC
				)
				SEPARATOR ', ') AS text_CategoryAttributeValues
		FROM 
			dwtphovu_8347379386_prod.15_categoryattributecombinations PAC 
		LEFT JOIN dwtphovu_8347379386_prod.2_servicescatalogue PSC ON PAC.bigint_ServiceID = PSC.bigint_ServiceID 
		LEFT JOIN dwtphovu_8347379386_prod.14_pricecategories PPC ON PAC.bigint_CategoryID = PPC.bigint_CategoryID 
		LEFT JOIN dwtphovu_8347379386_prod.3_serviceattributes PSA ON PAC.bigint_AttributeValueID = PSA.bigint_AttributeID 
		GROUP BY 
			PAC.bigint_CombinationID 
		ORDER BY 
			PAC.bigint_CombinationID ASC, 
			PSC.text_ServiceDescription ASC, 
			PPC.text_CategoryDescription ASC, 
			text_CategoryAttributeValues ASC;";
$result27 = mysql_query_errors($sql27, $conn , __FILE__ , __LINE__ , true );
if ($result27) {
	while ($row27 = mysql_fetch_assoc($result27)) {
		$service = $sql27["text_ServiceDescription"];
		$catdesc = $sql27["text_CategoryDescription"];
		$attvals = explode(", ",$sql27["text_CategoryAttributeValues"]);
		print_r(array($service,$catdesc,$attvals));
		$attrkey = array();
		$attrval = array();
		for ($i = 0; $i < count($attvals); $i++) {
			$kv = explode("=",$attvals[$i]);
			$attrkey[$i] = $kv[0];
			$attrval[$i] = $kv[1];
			switch ($attrkey[$i]) {
				case "Budget":
					switch ($attrval[$i]) {
						case "R3,000 to R4,000":
							$attrval[$i] = "R2,000 to R4,000";
							break;
					}
					break;
				case "Fitment":
					$attrval[$i] = str_replace(" the", "", $attrval[$i]);
					break;
				case "Requirement":
					$attrkey[$i] = "REQUIREMENT";
					switch ($attrval[$i]) {
						case "Pre-owned - White":
							$attrval[$i] = "USED STANDARD WHITE";
							break;
						case "Pre-owned - Colour-coded":
							$attrval[$i] = "USED COLOURCODED";
							break;
						case "New - White":
							$attrval[$i] = "NEW STANDARD WHITE";
							break;
						case "New - Colour-coded":
							$attrval[$i] = "NEW COLOURCODED";
							break;
						case "SELL my canopy Secondhand":
							$attrval[$i] = "FORSALE STANDARD WHITE";
							break;
					}
					break;
				default:
					unset($attrkey[$i]);
					unset($attrval[$i]);
			}
		}
		$attrkey = array_merge($attrkey);
		$attrval = array_merge($attrval);
		$sql28 = "SELECT 
					TSA1.bigint_AttributeServiceID, 
					TPC1.bigint_CategoryID, 
					GROUP_CONCAT(
						(
							TSA1.bigint_AttributeID
						)
						SEPARATOR ';'
					) AS group_ServiceAttributeValues
				FROM 
					dwtphovu_8347379386_test.3_serviceattributes TSA1 
				LEFT JOIN dwtphovu_8347379386_test.14_pricecategories TPC1 ON TPC1.text_CategoryDescription LIKE \"".$catdesc."\" 
				LEFT JOIN dwtphovu_8347379386_test.2_servicescatalogue TSC1 ON TSC1.text_ServiceDescription LIKE \"".$service."\" 
				WHERE 
					(
						TSA1.text_AttributeDescription LIKE \"".$attrkey[0]."\" AND 
						TSA1.text_AttributeValue LIKE \"".$attrval[0]."\"
					) OR (
						TSA1.text_AttributeDescription LIKE \"".$attrkey[1]."\" AND 
						TSA1.text_AttributeValue LIKE \"".$attrval[1]."\"
					) OR (
						TSA1.text_AttributeDescription LIKE \"".$attrkey[2]."\" AND 
						TSA1.text_AttributeValue LIKE \"".$attrval[2]."\"
					)";
		$result28 = mysql_query_errors($sql28, $conn , __FILE__ , __LINE__ , true );
		if ($result28) {
			if ($row28 = mysql_fetch_assoc($result28)) {
				$s = $row28["bigint_AttributeServiceID"];
				$id = $row28["bigint_CategoryID"];
				$sa = $row28["group_ServiceAttributeValues"];
				$sarr = split(";",$sa);
				$main_id = 0;
				for ($i = 0; $i < count($sarr); $i++) {
					if ($i > 0) {
						$sql29 = "INSERT INTO 
									dwtphovu_8347379386_test.15_categoryattributecombinations 
								(
									bigint_ServiceID, 
									bigint_CategoryID, 
									bigint_AttributeValueID
								) VALUES (
									".$s.", 
									".$id.", 
									".$sarr[$i]."
								);";
						$result29 = mysql_query_errors($sql29 , $conn , __FILE__ , __LINE__ , false);
						$sql30 = "UPDATE dwtphovu_8347379386_test.15_categoryattributecombinations 
								SET bigint_CombinationID = ".$main_id." WHERE bigint_CombinationID = ".mysql_insert_id($conn).";";
						$result30 = mysql_query_errors($sql30 , $conn , __FILE__ , __LINE__ , false);
					} else {
						$sql31 = "INSERT INTO 
									dwtphovu_8347379386_test.15_categoryattributecombinations 
								(
									bigint_ServiceID, 
									bigint_CategoryID, 
									bigint_AttributeValueID
								) VALUES (
									".$s.", 
									".$id.", 
									".$sarr[$i]."
								);";
						$result31 = mysql_query_errors($sql31 , $conn , __FILE__ , __LINE__ , false);
						$main_id = mysql_insert_id($conn);
					}
				}
			}
		}
	}
	mysql_free_result($result27);
}
print_r($sql);
flush();
$sql = array();
?>

Open in new window

i get the following results:
Array
(
    [0] => TRUNCATE TABLE dwtphovu_8347379386_test.15_categoryattributecombinations;

    [1] => NOTICE #0501: A true value was returned by the query defined in /home/dwtphovu/public_html/intellisource.co.za/test_8347379386/db_synchronizer.php on line 71

)
Array
(
    [0] => SELECT 
			PAC.bigint_CombinationID, 
			PSC.text_ServiceDescription, 
			PPC.text_CategoryDescription, 
			GROUP_CONCAT(
				(
					SELECT 
						CONCAT(
							PSA1.text_AttributeDescription,
							"=",
							PSA1.text_AttributeValue
						) 
					FROM 
						dwtphovu_8347379386_prod.3_serviceattributes PSA1 
					WHERE 
						PSA1.bigint_AttributeID = PAC.bigint_AttributeValueID 
					ORDER BY 
						PSA1.text_AttributeDescription ASC
				)
				SEPARATOR ', ') AS text_CategoryAttributeValues
		FROM 
			dwtphovu_8347379386_prod.15_categoryattributecombinations PAC 
		LEFT JOIN dwtphovu_8347379386_prod.2_servicescatalogue PSC ON PAC.bigint_ServiceID = PSC.bigint_ServiceID 
		LEFT JOIN dwtphovu_8347379386_prod.14_pricecategories PPC ON PAC.bigint_CategoryID = PPC.bigint_CategoryID 
		LEFT JOIN dwtphovu_8347379386_prod.3_serviceattributes PSA ON PAC.bigint_AttributeValueID = PSA.bigint_AttributeID 
		GROUP BY 
			PAC.bigint_CombinationID 
		ORDER BY 
			PAC.bigint_CombinationID ASC, 
			PSC.text_ServiceDescription ASC, 
			PPC.text_CategoryDescription ASC, 
			text_CategoryAttributeValues ASC;

    [1] => 

    [2] => Array
        (
            [0] => S
            [1] => S
            [2] => Array
                (
                    [0] => S
                )

        )

    [3] => SELECT 
					TSA1.bigint_AttributeServiceID, 
					TPC1.bigint_CategoryID, 
					GROUP_CONCAT(
						(
							TSA1.bigint_AttributeID
						)
						SEPARATOR ';'
					) AS group_ServiceAttributeValues
				FROM 
					dwtphovu_8347379386_test.3_serviceattributes TSA1 
				LEFT JOIN dwtphovu_8347379386_test.14_pricecategories TPC1 ON TPC1.text_CategoryDescription LIKE "S" 
				LEFT JOIN dwtphovu_8347379386_test.2_servicescatalogue TSC1 ON TSC1.text_ServiceDescription LIKE "S" 
				WHERE 
					(
						TSA1.text_AttributeDescription LIKE "" AND 
						TSA1.text_AttributeValue LIKE ""
					) OR (
						TSA1.text_AttributeDescription LIKE "" AND 
						TSA1.text_AttributeValue LIKE ""
					) OR (
						TSA1.text_AttributeDescription LIKE "" AND 
						TSA1.text_AttributeValue LIKE ""
					);

    [4] => 

    [5] => INSERT INTO 
									dwtphovu_8347379386_test.15_categoryattributecombinations 
								(
									bigint_ServiceID, 
									bigint_CategoryID, 
									bigint_AttributeValueID
								) VALUES (
									, 
									, 
									
								);

    [6] => ERROR #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 ' 
									, 
									
								)' at line 8 in /home/dwtphovu/public_html/intellisource.co.za/test_8347379386/db_synchronizer.php on Line 1052 // the above incorrectly compiled sql strings repeat for every record selected.

Open in new window

now i get the following resultset for sql27 in pma:
bigint_CombinationID	text_ServiceDescription	text_CategoryDescription	text_CategoryAttributeValues
29			Bakkie Canopy		Category A			fitment=ASAP, canopy_req=new_white, budget=R7,500 ...
35			Bakkie Canopy		Category A			fitment=within_2_weeks, canopy_req=new_white, budg...
41			Bakkie Canopy		Category A			fitment=ASAP, canopy_req=new_white, budget=R10,000...
143			Bakkie Canopy		Category B			Budget=R7,500 to R10,000, Fitment=ASAP, Requiremen...
149			Bakkie Canopy		Category B			Budget=R10,000 to R12,500, Fitment=ASAP, Requireme...
155			Bakkie Canopy		Category B			Budget=R12,500 plus, Fitment=ASAP, Requirement=Pre...
257			Bakkie Canopy		Category C			Budget=R5,000 to R7,500, Fitment=ASAP, Requirement...
263			Bakkie Canopy		Category C			Budget=R7,500 to R10,000, Fitment=ASAP, Requiremen...
269			Bakkie Canopy		Category C			Budget=R7,500 to R10,000, Fitment=Within the next ...

Open in new window

which is as expected, 169 rows over Category A - F. Only the next sql query runs with the $service, $catdesc & $attvals variables either empty or equal to S!!! :o
how is this happening, and what in the code needs to change to address this? It is a rather urgent issue, have to have these databases synchronized by this afternoon for replacing the production system with the test system (where a while's development has been completed).
another related question - if the following is the mysql_query_errors function from the include, then why do some of the queries return no status report when $expret is set to true??? 0o
function mysql_query_errors($sql, $conn, $doc, $line, $expret = false) {
	$line--;
	$err = "";
	$result = mysql_query($sql);
	if (mysql_errno() > 0) {
		$err = "ERROR #".mysql_errno().": ".mysql_error()." in ".$doc." on Line ".$line;
	} elseif ($expret) {
		if (is_null($result)) {
			$err = "NOTICE #0504: A null value was returned by the query defined in ".$doc." on line ".$line--;
		} elseif ($result === false) {
			$err = "NOTICE #0502: A false value was returned by the query defined in ".$doc." on line ".$line--;
		} elseif ($result === true) {
			$err = "NOTICE #0501: A true value was returned by the query defined in ".$doc." on line ".$line--;
		} elseif (mysql_num_rows($result) < 1) {
			$err = "NOTICE #0500: An empty result set was returned by the query defined in ".$doc." on line ".$line--;
		} elseif (mysql_num_rows($result) > 0) {
			$err = "NOTICE #0505: A result set of ".mysql_num_rows($result)." rows was returned by the query defined in ".$doc." on line ".$line--;	
		}
		array_push($GLOBALS["sql"],$sql."\n",$err."\n");
	}
	return $result;
}

Open in new window

intellisourceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

intellisourceAuthor Commented:
i should really stop working when mentally inebriated from fatigue -_-
lol - note that i try retrieve the values from the resultset via $sql27[...] instead of $rrow27[...] as i should! XD this issue is resolved!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.