Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > Moderated Pro PHP
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Moderated Pro PHP This is a moderated forum for discussing advanced, professional level issues with PHP. Your posts will not appear until a moderator approves them. Posts that are not the right level for this forum will be responded to and you'll be asked to post them in the [url="http://p2p.wrox.com/sql-server-2000-20/9"]Beginning PHP[/url] forum instead.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Moderated Pro PHP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 29th, 2009, 10:13 AM
Authorized User
 
Join Date: Sep 2003
Location: Wonderboom South, Pretoria, Gauteng, South Africa.
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Greywacke
Angry battling to select the data correctly from mysql

the problem is from 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.

PHP Code:
\\line 31-63
$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";
        }

PHP Code:
\\line 64-87
$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";

Quote:
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
__________________
Sincerely,
Pierre "Greywacke" du Toit
pierre@greywacke.co.za
don't worry about my 0 thankyou's either way, i would say thank you should you help, and i will gladly help others when this work rush is over.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Cannot Displayed On SubForm Correctly cheer Access VBA 0 October 29th, 2009 10:14 PM
SSIS Not pulling data correctly chevy SQL Server 2005 0 February 26th, 2008 04:12 PM
Select Box update to MySQL Jdbellmon Pro PHP 1 October 10th, 2003 12:03 PM
battling to add username & password too similar... Greywacke Javascript 4 October 8th, 2003 02:23 AM
Select Box update to MySQL Jdbellmon PHP Databases 1 October 7th, 2003 06:45 PM



All times are GMT -4. The time now is 01:44 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.