php cross-db referenced synchronizer script: 1st and 2nd mysql queries run, third does not! why could this be?
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.
Production-System.xls
Test-System.xls
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();
?>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] =>
)Production-System.xls
Test-System.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for the tips - actually i noticed them before i came back to ee to close this post! XD
ASKER