?
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
Medium Priority
?
402 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
  • 2
3 Comments
 
LVL 1

Accepted Solution

by:
chrisroch earned 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 1 hour left to enroll

809 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