the problem is in line 64-87, trying to select the "linked" attributes (to the individual suppliers) and order them by service (region) like the block between lines 31-63 (available services, displayed from service binding).
attributes are "linked" to services in 3_serviceattributes, suppliers are "linked" to a region by service in 4_servicesuppliers. suppliers need to be linked to chosen attributes in 9_supplierattributes. the tables i am trying to select from and that are being used by the php, and the dummy data are below.
\\line 31-63 correct
$sql1 = "SELECT * FROM 4_servicesuppliers WHERE bigint_SupplierID = ".$row["bigint_SupplierID"].";\n";
$GLOBALS["sql"] .= $sql1;
$result1 = mysql_query($sql1);
$err = mysql_error();
$GLOBALS["sql"] .= strtoupper($err)."\n";
while ($row1 = mysql_fetch_array($result1)) {
$sql4 = "SELECT * FROM 1_regions WHERE bigint_RegionID = ".$row1["bigint_RegionID"].";\n";
$GLOBALS["sql"] .= $sql2;
$result4 = mysql_query($sql4);
$err = mysql_error();
$GLOBALS["sql"] .= strtoupper($err)."\n";
$regionname = "";
while ($row4 = mysql_fetch_array($result4)) {
$regionname = $row4["text_RegionDescription"];
}
$sql2 = "SELECT * FROM 2_servicescatalogue WHERE bigint_ServiceID = ".$row1["bigint_ServiceID"].";\n";
$GLOBALS["sql"] .= $sql2;
$result2 = mysql_query($sql2);
$err = mysql_error();
$GLOBALS["sql"] .= strtoupper($err)."\n";
while ($row2 = mysql_fetch_array($result2)) {
echo " <serviceattrib description=\"".$row2["text_ServiceDescription"]." (".$regionname." and children)\"";
$sql3 = "SELECT * FROM 3_serviceattributes WHERE bigint_AttributeServiceID = ".$row1["bigint_ServiceID"].";\n";
$GLOBALS["sql"] .= $sql3;
$result3 = mysql_query($sql3);
$err = mysql_error();
$GLOBALS["sql"] .= strtoupper($err)."\n";
while ($row3 = mysql_fetch_array($result3)) {
echo " attibute".$row3["bigint_AttributeID"]."=\"".$row3["bigint_AttributeID"].";".$row1["bigint_ServiceID"].";".$row1["bigint_RegionID"].";".$row3["text_AttributeDescription"].";".$row3["text_AttributeValue"]."\"";
}
echo " />\n";
}
}
😃 Correct available attributes
<serviceattrib description="Test Service 1 (Test Region 6 and children)" attibute1="1;1;7;Test Attribute 1;Test Value 1" attibute2="2;1;7;Test Attribute 1;Test Value 2" attibute11="11;1;7;Test Attribute 1;Test Value 3" attibute12="12;1;7;Test Attribute 2;Test Value 1" attibute13="13;1;7;Test Attribute 2;Test Value 2"/>
\\line 64-87 wrong approach
$sql5 = "SELECT DISTINCT(bigint_ServiceID),bigint_RegionID,bigint_AttributeID FROM 9_supplierattributes WHERE bigint_SupplierID = ".$row["bigint_SupplierID"].";\n";
$GLOBALS["sql"] .= $sql5;
$result5 = mysql_query($sql5);
$err = mysql_error();
$GLOBALS["sql"] .= strtoupper($err)."\n";
while ($row5 = mysql_fetch_array($result5)) {
$sql6 = "SELECT * FROM 2_servicescatalogue, 1_regions WHERE 2_servicescatalogue.bigint_ServiceID = ".$row5["bigint_ServiceID"]." AND 1_regions.bigint_RegionID = ".$row5["bigint_RegionID"].";\n";
$GLOBALS["sql"] .= $sql6;
$result6 = mysql_query($sql6);
$err = mysql_error();
$GLOBALS["sql"] .= strtoupper($err)."\n";
while ($row6 = mysql_fetch_array($result6)) {
$servicename = $row6["text_ServiceDescription"]." (".$row6["text_RegionDescription"]." and children )";
}
echo " <supplierattrib description=\"".$servicename."\"";
$sql7 = "SELECT * FROM 3_serviceattributes WHERE bigint_AttributeID = ".$row5["bigint_AttributeID"].";\n";
$GLOBALS["sql"] .= $sql7;
$result7 = mysql_query($sql7);
$err = mysql_error();
$GLOBALS["sql"] .= strtoupper($err)."\n";
while ($row7 = mysql_fetch_array($result7)) {
echo " attribute".$row7["bigint_AttributeID"]."=\"".$row7["bigint_AttributeID"].";".$row5["bigint_ServiceID"].";".$row5["bigint_RegionID"].";".$row7["text_AttributeDescription"].";".$row7["text_AttributeValue"]."\"";
}
echo " />\n";
}
🙁 Wrong selected attributes
<supplierattrib description="Test Service 1 (Test Region 6 and children )" attribute1="1;1;7;Test Attribute 1;Test Value 1"/>
<supplierattrib description="Test Service 1 (Test Region 6 and children )" attribute2="2;1;7;Test Attribute 1;Test Value 2"/>
:o Selected attribute results required
1_regions
bigint_RegionID | text_RegionDescription | bigint_ParentRegionID
1 | Test Region | 0
2 | Test Region 1 | 1
3 | Test Region 2 | 1
4 | Test Region 3 | 1
5 | Test Region 4 | 2
6 | Test Region 5 | 2
7 | Test Region 6 | 3
8 | Test Region 7 | 7
9 | Test Region 8 | 8
10 | Test Region 9 | 4
2_servicescatalogue
bigint_ServiceID | text_ServiceDescription | bigint_CostPerLead
1 | Test Service 1 | 20
3_serviceattributes
bigint_AttributeID | text_AttributeDescription | text_AttributeValue | bigint_AttributeServiceID
1 | Test Attribute 1 | Test Value 1 | 1
2 | Test Attribute 1 | Test Value 2 | 1
11 | Test Attribute 1 | Test Value 3 | 1
12 | Test Attribute 2 | Test Value 1 | 1
13 | Test Attribute 2 | Test Value 2 | 1
4_servicesuppliers
bigint_ServiceID | bigint_SupplierID | bigint_RegionID
1 | 6 | 7
1 | 1 | 10
1 | 3 | 6
1 | 1 | 3
5_suppliers
bigint_SupplierID | text_SupplierName | text_SupplierW3 | text_ContactFirstName | text_ContactPosition | text_ContactE-mail | bigint_ContactTel | bigint_CurrentBalance
2 | Test Supplier 3 | http://www.google.co.za/ | Koos | Koekemoer | Foreman | koos@gmail.com | 27119756969 | 1000
5 | Test Supplier 2 | http://www.google.co.za/ | Koos | Koekemoer | Foreman | koos@gmail.com | 27119756969 | 1000
6 | Test Supplier 1 | http://www.google.co.za/ | Pierre | du Toit | Graphic Design & AJAX | pierre@greywacke.co.za | 27729154799 | 1000
9_supplierattributes
bigint_SupplierID | bigint_ServiceID | bigint_AttributeID | bigint_RegionID
6 | 1 | 1 | 7
6 | 1 | 2 | 7
