Solved

php cross-db referenced synchronizer script: 1st and 2nd mysql queries run, third does not! why could this be?

Posted on 2012-04-12
3
384 Views
Last Modified: 2012-06-21
hi,
i have the following php cross-database referenced synchronizer script - however note that of the 6 queries in essence, only 1 and 2 run! why could this be??? i am at my ends wits trying to figure it out... we are synchronizing from an outdated database to a new database with an improved region / attribute set. the table layouts are attached in the XLS documents. The script is written to parse the current non-attribute bindings verbosely, then recreate them on the test system. but unfortunately something is wrong with the translation script as can be seen from the 3 xls documents. the new attributes table supports parent attributes now, with multiple possibilities. attributes with names in capitals are so called "megavariables", which are compared to a combination of form variables. eg Condition + Colour = REQUIREMENT and Make + Model + Shape which are not added yet, are = MAKEMODELSHAPE to be added as required. The parent of 18 is a NISSAN Make obviously, from the NAVARA KING CAB & NP300 KING CAB child attributes. Megavariables also are not used in the form, so generally have a parentid of 0 or NULL.
<?php
// define mysql_query_errors function
function mysql_query_errors($sql, $conn, $doc, $line, $expret = false) {
	$line--;
	$err = "";
	$result = false;
	global $conn;
	$result = mysql_query($sql,$conn);
	if (mysql_errno()) {
		$err = "ERROR #".mysql_errno().": ".mysql_error()." in ".$doc." on Line ".$line;
		array_push($GLOBALS["sql"],$sql."\n",$err."\n");
	} elseif ($expret) {
		if (is_null($result)) {
			$err = "NOTICE #0504: A null value was returned by the query defined in ".$doc." on line ".$line--;
		} elseif (!isset($result)) {
			$err = "NOTICE #0503: An undefined 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--;
		}
		array_push($GLOBALS["sql"],$sql."\n",$err."\n");
	}
	return $result;
}
set_time_limit(0);
header('Cache-Control: no-cache');
header('Pragma: no-cache');
header("Content-type: text/plain; charset=utf-8");
$sql = array();
// set 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 test supplier attributes table
$sql1 = "TRUNCATE TABLE dwtphovu_8347379386_test.9_supplierattributes;";
$result1 = mysql_query_errors($sql1, $conn , __FILE__ , __LINE__ , true );
if (is_resource($result1)) mysql_free_result($result1);
print_r($sql);
flush();
$sql = array();
// REGIONAL SUPPLIER NON-ATTRIBUTES
// select prod supplier attributes
$sql2 = "SELECT 
			PSPA.bigint_SupplierID, 
			PSPA.bigint_ServiceID, 
			PSPL.text_SupplierName, 
			PSCT.text_ServiceDescription, 
			PRGN.text_RegionDescription, 
			PSVA.text_AttributeDescription, 
			PSVA.text_AttributeValue
		FROM 
			dwtphovu_8347379386_prod.9_supplierattributes PSPA 
		LEFT JOIN 
			dwtphovu_8347379386_prod.5_suppliers PSPL 
		ON 
			PSPA.bigint_SupplierID = PSPL.bigint_SupplierID 
		LEFT JOIN 
			dwtphovu_8347379386_prod.2_servicescatalogue PSCT 
		ON 
			PSPA.bigint_ServiceID = PSCT.bigint_ServiceID 
		LEFT JOIN 
			dwtphovu_8347379386_prod.1_regions PRGN 
		ON 
			PSPA.bigint_RegionID = PRGN.bigint_RegionID 
		LEFT JOIN 
			dwtphovu_8347379386_prod.3_serviceattributes PSVA 
		ON 
			PSPA.bigint_AttributeID = PSVA.bigint_AttributeID 
		WHERE 
			PSPL.text_SupplierName IS NOT NULL AND 
			PSVA.text_AttributeDescription != \"budget\" AND 
			PSVA.text_AttributeDescription != \"canopy_req\" AND 
			PSVA.text_AttributeDescription != \"vehicle_make_model\" AND 
			PSVA.text_AttributeDescription != \"canopy_style\" AND 
			PSVA.text_AttributeDescription != \"vehicle_status\" 
		ORDER BY 
			PSPL.text_SupplierName ASC, 
			PSCT.text_ServiceDescription ASC, 
			PRGN.text_RegionDescription ASC, 
			PSVA.text_AttributeDescription ASC, 
			PSVA.text_AttributeValue ASC;";
$result2 = mysql_query_errors($sql2, $conn , __FILE__ , __LINE__ , true );
if (is_resource($result2)) mysql_free_result($result2);
$supid = 0;
$svcid = 0;
$regid = 0;
$attid = 0;
if (is_resource($result2)) {
// loop prod supplier attributes
	while ($row2 = mysql_fetch_assoc($result2)) {
		$supid = $row2["bigint_SupplierID"];
		$svcid = $row2["bigint_ServiceID"];
		// prod supplier attributes switch case, translating and creating new attributes
		switch ($row2["text_AttributeDescription"]) {
			case "Budget":
				if ($row2["text_AttributeValue"] = "R3,000 to R4,000") {
					$row2["text_AttributeValue"] = "R2,000 to R4,000";
				}
				// find coinciding Budget to add non-attribute for
				$sql3 = "SELECT 
							SA.bigint_AttributeID 
						FROM 
							dwtphovu_8347379386_test.3_serviceattributes SA 
						WHERE 
							SA.text_AttributeDescription = \"Budget\" AND 
							SA.text_AttributeValue = \"".$row2["text_AttributeValue"]."\";";
				$result3 = mysql_query_errors($sql3, $conn , __FILE__ , __LINE__ , true );
				if (is_resource($result3)) {
					if (mysql_num_rows($result3)) {
						if ($row3 = mysql_fetch_assoc($result3)) {
							$attid = $row3["bigint_AttributeID"];
						}
					} else {
						$sql4 = "INSERT INTO dwtphovu_8347379386_test.3_serviceattributes (text_AttributeDescription, text_AttributeValue) VALUES (\"Budget\",\"".$row21["text_AttributeValue"]."\");";
						$result4 = mysql_query_errors($sql4, $conn , __FILE__ , __LINE__ , true );
						$attid = mysql_insert_id($conn);
					}
				}
				$sql5 = "SELECT 
							RG.bigint_RegionID 
						FROM 
							dwtphovu_8347379386_test.1_regions RG 
						WHERE 
							RG.text_RegionDescription = \"".$row2["text_RegionDescription"]."\";";
				$result5 = mysql_query_errors($sql5, $conn , __FILE__ , __LINE__ , true );
				if (is_resource($result5)) {
					if ($row5 = mysql_fetch_assoc($result5)) {
						$regid = $row5["bigint_RegionID"];
					}
				}
				break;
			case "Fitment":
				if (strstr($row2["text_AttributeValue"]," the") !== FALSE) {
					$row2["text_AttributeValue"] = str_replace(" the", "", $row2["text_AttributeValue"]);
				}
				// find coinciding Fitment to add non-attribute for
				$sql3 = "SELECT 
							SA.bigint_AttributeID 
						FROM 
							dwtphovu_8347379386_test.3_serviceattributes SA 
						WHERE 
							SA.text_AttributeDescription = \"Fitment\" AND 
							SA.text_AttributeValue = \"".$row2["text_AttributeValue"]."\";";
				$result3 = mysql_query_errors($sql3, $conn , __FILE__ , __LINE__ , true );
				if (is_resource($result3)) {
					if (mysql_num_rows($result3)) {
						if ($row3 = mysql_fetch_assoc($result3)) {
							$attid = $row3["bigint_AttributeID"];
						}
					} else {
						$sql4 = "INSERT INTO dwtphovu_8347379386_test.3_serviceattributes (text_AttributeDescription, text_AttributeValue) VALUES (\"Fitment\",\"".$row21["text_AttributeValue"]."\");";
						$result4 = mysql_query_errors($sql4, $conn , __FILE__ , __LINE__ , true );
						$attid = mysql_insert_id($conn);
					}
				}
				$sql5 = "SELECT 
							RG.bigint_RegionID 
						FROM 
							dwtphovu_8347379386_test.1_regions RG 
						WHERE 
							RG.text_RegionDescription = \"".$row2["text_RegionDescription"]."\";";
				$result5 = mysql_query_errors($sql5, $conn , __FILE__ , __LINE__ , true );
				if (is_resource($result5)) {
					if ($row5 = mysql_fetch_assoc($result5)) {
						$regid = $row5["bigint_RegionID"];
					}
				}
				break;
			case "Requirement":
				switch ($row2["text_AttributeValue"]) {
					case "Pre-owned - White":
						$row2["text_AttributeValue"] = "USED STANDARD WHITE";
						break;
					case "Pre-owned - Colour-coded":
						$row2["text_AttributeValue"] = "USED COLOURCODED";
						break;
					case "New - White":
						$row2["text_AttributeValue"] = "NEW STANDARD WHITE";
						break;
					case "New - Colour-coded":
						$row2["text_AttributeValue"] = "NEW COLOURCODED";
						break;
					case "SELL my canopy Secondhand":
						$row2["text_AttributeValue"] = "FORSALE STANDARD WHITE";
						break;
				}
				// find coinciding REQUIREMENT to add non-attribute for
				$sql3 = "SELECT 
							SA.bigint_AttributeID 
						FROM 
							dwtphovu_8347379386_test.3_serviceattributes SA 
						WHERE 
							SA.text_AttributeDescription = \"REQUIREMENT\" AND 
							SA.text_AttributeValue = \"".$row2["text_AttributeValue"]."\";";
				$result3 = mysql_query_errors($sql3, $conn , __FILE__ , __LINE__ , true );
				if (is_resource($result3)) {
					if (mysql_num_rows($result3)) {
						if ($row3 = mysql_fetch_assoc($result3)) {
							$attid = $row3["bigint_AttributeID"];
						}
					} else {
						$sql4 = "INSERT INTO dwtphovu_8347379386_test.3_serviceattributes (text_AttributeDescription, text_AttributeValue) VALUES (\"REQUIREMENT\",\"".$row21["text_AttributeValue"]."\")".(($row21["text_AttributeValue"] == "FORSALE STANDARD WHITE")?",(\"REQUIREMENT\",\"FORSALE COLOURCODED\")":"").";";
						$result4 = mysql_query_errors($sql4, $conn , __FILE__ , __LINE__ , true );
						$attid = mysql_insert_id($conn);
					}
				}
				$sql5 = "SELECT 
							RG.bigint_RegionID 
						FROM 
							dwtphovu_8347379386_test.1_regions RG 
						WHERE 
							RG.text_RegionDescription = \"".$row2["text_RegionDescription"]."\";";
				$result5 = mysql_query_errors($sql5, $conn , __FILE__ , __LINE__ , true );
				if (is_resource($result5)) {
					if ($row5 = mysql_fetch_assoc($result5)) {
						$regid = $row5["bigint_RegionID"];
					}
				}
			break;
			case "products_description":
				switch ($row2["text_AttributeValue"]) {
					case "Mitsubishi Triton C/C":
						$row2["text_AttributeValue"] = "MITSUBISHI TRITON CLUB CAB";
						break;
					case "Toyota SWB":
						$row2["text_AttributeValue"] = "TOYOTA HILUX SHORT WHEEL BASE";
						break;
					case "X Space":
						$row2["text_AttributeValue"] = "X-SPACE";
						break;
					case "D/C":
						$row2["text_AttributeValue"] = "DOUBLE CAB";
						break;
					case "Ext/C":
						$row2["text_AttributeValue"] = "EXTENDED CAB";
						break;
					case "K/C":
						$row2["text_AttributeValue"] = "KING CAB";
						break;
					case "LWB":
						$row2["text_AttributeValue"] = "SINGLE CAB";
						break;
					case "S/C":
						$row2["text_AttributeValue"] = "SUPER CAB";
						break;
					case "SCAB":
						$row2["text_AttributeValue"] = "SUPER CAB";
						break;
					case "Toyota L/C":
						$row2["text_AttributeValue"] = "TOYOTA LAND CRUISER";
						break;
					case "SWB":
						$row2["text_AttributeValue"] = "SHORT WHEEL BASE";
						break;
					case "Chana Star D/C":
						$row2["text_AttributeValue"] = "CHANA STAR DOUBLE CAB";
						break;
					case "Isuzu Ext/C":
						$row2["text_AttributeValue"] = "ISUZU KB EXTENDED CAB";
						break;
					case "Nissan K/C":
						$row2["text_AttributeValue"] = "NISSAN NP300 KING CAB";
						break;
					case "Ford LWB":
						$row2["text_AttributeValue"] = "FORD RANGER SINGLE CAB";
						break;
					case "Ford S/C":
						$row2["text_AttributeValue"] = "FORD RANGER SUPER CAB";
						break;
					case "GWM LWB":
						$row2["text_AttributeValue"] = "GWM SINGLE CAB";
						break;
					case "Isuzu LWB":
						$row2["text_AttributeValue"] = "ISUZU KB SINGLE CAB";
						break;
					case "Nissan LWB":
						$row2["text_AttributeValue"] = "NISSAN NP300 SINGLE CAB";
						break;
					case "Mazda LWB":
						$row2["text_AttributeValue"] = "MAZDA DRIFTER SINGLE CAB";
						break;
					case "Tata SWB":
						$row2["text_AttributeValue"] = "TATA TELCOLINE SINGLE CAB";
						break;
					case "Toyota D/C":
						$row2["text_AttributeValue"] = "TOYOTA HILUX DOUBLE CAB";
						break;
					case "Ford D/C":
						$row2["text_AttributeValue"] = "FORD RANGER DOUBLE CAB";
						break;
					case "GWM D/C":
						$row2["text_AttributeValue"] = "GWM DOUBLE CAB";
						break;
					case "GWM LWB":
						$row2["text_AttributeValue"] = "GWM SINGLE CAB";
						break;
					case "Mazda D/C":
						$row2["text_AttributeValue"] = "MAZDA DRIFTER DOUBLE CAB";
						break;
					case "Tata D/C":
						$row2["text_AttributeValue"] = "TATA TELCOLINE DOUBLE CAB";
						break;
					case "Tata Xenon D/C":
						$row2["text_AttributeValue"] = "TATA XENON DOUBLE CAB";
						break;
					case "Fiat Strada X-Space":
						$row2["text_AttributeValue"] = "X-SPACE";
						break;
				}
				// find if nonexistant, create: coinciding MAKEMODELSHAPE attribute to add non-attribute for
				$sql3 = "SELECT 
							SA.bigint_AttributeID 
						FROM 
							dwtphovu_8347379386_test.3_serviceattributes SA 
						WHERE 
							SA.text_AttributeDescription = \"MAKEMODELSHAPE\" AND 
							SA.text_AttributeValue = \"".$row21["text_AttributeValue"]."\";";
				$result3 = mysql_query_errors($sql3, $conn , __FILE__ , __LINE__ , true );
				if (is_resource($result3)) {
					if (mysql_num_rows($result3)) {
						if ($row3 = mysql_fetch_assoc($result3)) {
							$attid = $row3["bigint_AttributeID"];
						}
					} else {
						$sql4 = "INSERT INTO dwtphovu_8347379386_test.3_serviceattributes (text_AttributeDescription, text_AttributeValue) VALUES (\"MAKEMODELSHAPE\",UPPER(\"".$row2["text_AttributeValue"]."\"));";
						$result4 = mysql_query_errors($sql4, $conn , __FILE__ , __LINE__ , true );
						$attid = mysql_insert_id($conn);
					}
				}
				$sql5 = "SELECT 
							RG.bigint_RegionID 
						FROM 
							dwtphovu_8347379386_test.1_regions RG 
						WHERE 
							RG.text_RegionDescription LIKE \"%".$row2["text_RegionDescription"]."%\";";
				$result5 = mysql_query_errors($sql5, $conn , __FILE__ , __LINE__ , true );
				if (is_resource($result5)) {
					if ($row5 = mysql_fetch_assoc($result5)) {
						$regid = $row5["bigint_RegionID"];
					}
				}
			break;
		}
		$sql6 = "INSERT INTO 
					  dwtphovu_8347379386_test.9_supplierattributes 
					  (
						  bigint_SupplierID, 
						  bigint_ServiceID, 
						  bigint_RegionID, 
						  bigint_AttributeID
					  ) 
				  VALUES 
					 (
						  ".$supid.", 
						  ".$svcid.", 
						  ".$regid.", 
						  ".$attid."
					 )".(($row21["text_AttributeValue"] == "FORSALE STANDARD WHITE")?
					 ", (".$supid.", 
						  ".$svcid.", 
						  ".$regid.", 
						  (SELECT 
							SA.bigint_AttributeID 
						FROM 
							dwtphovu_8347379386_test.3_serviceattributes SA 
						WHERE 
							SA.text_AttributeDescription = \"REQUIREMENT\" AND 
							SA.text_AttributeValue = \"FORSALE COLOURCODED\"))":"").
				  ";";
		$result6 = mysql_query_errors($sql6, $conn , __FILE__ , __LINE__ , true );
// end loop prod supplier attributes
	}
}
print_r($sql);
flush();
$sql = array();
?>

Open in new window

I have been through this script countless times over the past 3 weeks, and have been able to come up with nothing. -_- after the second sql query it simply stops processing. why would/could this be? i only get the following response.
Array
(
    [0] => TRUNCATE TABLE dwtphovu_8347379386_test.9_supplierattributes;

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

)
Array
(
    [0] => SELECT 
			PSPA.bigint_SupplierID, 
			PSPA.bigint_ServiceID, 
			PSPL.text_SupplierName, 
			PSCT.text_ServiceDescription, 
			PRGN.text_RegionDescription, 
			PSVA.text_AttributeDescription, 
			PSVA.text_AttributeValue
		FROM 
			dwtphovu_8347379386_prod.9_supplierattributes PSPA 
		LEFT JOIN 
			dwtphovu_8347379386_prod.5_suppliers PSPL 
		ON 
			PSPA.bigint_SupplierID = PSPL.bigint_SupplierID 
		LEFT JOIN 
			dwtphovu_8347379386_prod.2_servicescatalogue PSCT 
		ON 
			PSPA.bigint_ServiceID = PSCT.bigint_ServiceID 
		LEFT JOIN 
			dwtphovu_8347379386_prod.1_regions PRGN 
		ON 
			PSPA.bigint_RegionID = PRGN.bigint_RegionID 
		LEFT JOIN 
			dwtphovu_8347379386_prod.3_serviceattributes PSVA 
		ON 
			PSPA.bigint_AttributeID = PSVA.bigint_AttributeID 
		WHERE 
			PSPL.text_SupplierName IS NOT NULL AND 
			PSVA.text_AttributeDescription != "budget" AND 
			PSVA.text_AttributeDescription != "canopy_req" AND 
			PSVA.text_AttributeDescription != "vehicle_make_model" AND 
			PSVA.text_AttributeDescription != "canopy_style" AND 
			PSVA.text_AttributeDescription != "vehicle_status" 
		ORDER BY 
			PSPL.text_SupplierName ASC, 
			PSCT.text_ServiceDescription ASC, 
			PRGN.text_RegionDescription ASC, 
			PSVA.text_AttributeDescription ASC, 
			PSVA.text_AttributeValue ASC;

    [1] => 

)

Open in new window

visual-non-attribute-bindings-fr.xls
Production-System.xls
Test-System.xls
0
Comment
Question by:intellisource
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 1

Accepted Solution

by:
chrisroch earned 500 total points
ID: 37837865
I see issues with line# 88:
if (is_resource($result2)) mysql_free_result($result2);

Open in new window


and line# 93:
if (is_resource($result2)) {
// loop prod supplier attributes
	while ($row2 = mysql_fetch_assoc($result2)) {

Open in new window


You are destroying the result2 object, then trying to access the data associated with it afterwards. The code will never enter that loop, hence the rest of your queries will never run.
0
 

Author Comment

by:intellisource
ID: 37839517
thanks man - i saw and read your reply as i saw what was happening! hehe
0
 

Author Closing Comment

by:intellisource
ID: 37839527
thanks for the tips - actually i noticed them before i came back to ee to close this post! XD
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

729 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