Solved

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

Posted on 2012-03-28
1
307 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:intellisource
1 Comment
 

Accepted Solution

by:
intellisource earned 0 total points
ID: 37775595
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question