Solved

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

Posted on 2012-03-28
1
306 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now