Solved

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

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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 look for a specific file type in a local or remote server directory using PHP.

707 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

16 Experts available now in Live!

Get 1:1 Help Now