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?
 
intellisourceConnect With a Mentor Author 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.