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
340 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 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

Highfive Gives IT Their Time Back

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

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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.

762 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

19 Experts available now in Live!

Get 1:1 Help Now