#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2001
    Posts
    94
    Rep Power
    18

    need SQL help - one query or multiples


    Ok here's the scoop :

    I am building a fundraiser application for a client where a users can come to the web, sign up to run a specified fundraiser and once it's approved, they can come back to the web, login and
    basically build their fundraiser and submit it.

    This means they chose to run a specific fundraiser type, can add customers and participants, add orders and get fundraiser totals etc. This works great and everything is tied together by fundraiser id, participant id and customer id.

    Each fundraiser type has different "wigets". The user has the ability to customize what they want to put on the "wiget" (letters,names).
    This data is captured in the DB. Each wiget also has the associated fundraiser id, participant id and customer id so I know exactly what fundraiser, customer and participant they belong to.

    My problem here is one of the reports that I allow the client to run is a master report that will show all of the details of any current active fundraiser they choose.

    I need help with a query or a series of queries that will produce the following display ;

    Code:
    //CLIENT SELECTS ACTIVE FUNDRAISER (UNIQUE FUND ID IS a10461) FUND ID = $getid
    
    PARTICIPANT 1 NAME
     ** CUSTOMER NAME
        ** WIGET TYPE 1 
       	 ** WIGET NAME
       	 ** WIGET NAME
        ** WIGET TYPE 2 
       	 ** WIGET NAME
       	 ** WIGET NAME
        ** WIGET TYPE 3 
       	 ** WIGET NAME
       	 ** WIGET NAME
        ** WIGET TYPE 4 
       	 ** WIGET NAME
       	 ** WIGET NAME
    
    
    PARTICIPANT 2 NAME
     ** CUSTOMER NAME
        ** WIGET TYPE 1 
       	 ** WIGET NAME
       	 ** WIGET NAME
        ** WIGET TYPE 2 
       	 ** WIGET NAME
       	 ** WIGET NAME
        ** WIGET TYPE 3 
       	 ** WIGET NAME
       	 ** WIGET NAME
        ** WIGET TYPE 4 
       	 ** WIGET NAME
       	 ** WIGET NAME
    And so on ....


    Here's 1 query I've tried (I could alias the tables but to show you what I'm doing its easier)

    Code:
    $find_order_sql ="SELECT * from active_fund, fund_participants, fund_customers, key_single, 
    
    key_double WHERE active_fund.fundID='$getID' AND fund_participants.part_fundID = '$getID' AND 
    
    fund_customers.c_fund = '$getID' AND key_single.fundID = '$getID' AND key_double.fundID = 
    
    '$getID' AND fund_participants.partID = fund_customers.part_id AND key_single.custID = 
    
    fund_customers.custID"; 
    
    
    This second query is actually doing a query within a query loop
    
    //GET ALL ORDER INFORMATION
      
      
     $find_order_sql ="SELECT DISTINCT part_fname, part_lname, partID from fund_participants WHERE 
    
    part_fundID ='$getID'"; 
     $find_order_sql_result = mysql_query($find_order_sql) or die (mysql_error());
     
     while ($row = mysql_fetch_array($find_order_sql_result)) {
       $find_pfname = $row['part_fname'];
       $find_plname = $row['part_lname'];
       $find_pid = $row['partID'];
       
       $find_display_p.= "<table><tr class='copy'><td>Participant name : $find_pfname $find_plname 
    
    - $find_pid</td></tr>
       <tr class='copy'><td>Customer name : $find_cfname $find_clname - 
    
    $find_cid</td></tr></table>
       ";
       
     $find_cust_sql ="SELECT DISTINCT c_fname, c_lname, custID, part_id from fund_customers WHERE 
    
    part_id ='$find_pid'"; 
     $find_cust_sql_result = mysql_query($find_cust_sql) or die (mysql_error());
     
     while ($row = mysql_fetch_array($find_cust_sql_result)) {
       $find_cfname = $row['c_fname'];
       $find_clname = $row['c_lname'];
       $find_cid = $row['custID'];
       
       $find_display_p.= "<table><tr class='copy'><td>Participant name : $find_pfname $find_plname 
    
    - $find_pid</td></tr>
       <tr class='copy'><td>Customer name : $find_cfname $find_clname - 
    
    $find_cid</td></tr></table>
       ";
       
       }
       
     
       }
    I'm getting duplicate results and I'm thinking I need to get some help on how I can do this better. It almost seems like a sub-select would work better.

    Any help is appreciated

    Coach
    A gentle push and a mild arc -
    And the cowhide globe hit home

    Hot Rod Hundley
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    never do a query within a loop

    warning bells should go off in your head

    99.9 times out of a hunnert, what you want is a join

    in your case, get everything with a single query:

    PARTICIPANT 1 NAME ** CUSTOMER NAME ** WIGET TYPE 1 ** WIGET NAME
    PARTICIPANT 1 NAME ** CUSTOMER NAME ** WIGET TYPE 1 ** WIGET NAME
    PARTICIPANT 1 NAME ** CUSTOMER NAME ** WIGET TYPE 2 ** WIGET NAME
    PARTICIPANT 1 NAME ** CUSTOMER NAME ** WIGET TYPE 2 ** WIGET NAME
    PARTICIPANT 1 NAME ** CUSTOMER NAME ** WIGET TYPE 3 ** WIGET NAME
    PARTICIPANT 1 NAME ** CUSTOMER NAME ** WIGET TYPE 3 ** WIGET NAME
    PARTICIPANT 1 NAME ** CUSTOMER NAME ** WIGET TYPE 4 ** WIGET NAME
    PARTICIPANT 1 NAME ** CUSTOMER NAME ** WIGET TYPE 4 ** WIGET NAME

    PARTICIPANT 2 NAME ** CUSTOMER NAME ** WIGET TYPE 1 ** WIGET NAME
    PARTICIPANT 2 NAME ** CUSTOMER NAME ** WIGET TYPE 1 ** WIGET NAME
    PARTICIPANT 2 NAME ** CUSTOMER NAME ** WIGET TYPE 2 ** WIGET NAME
    PARTICIPANT 2 NAME ** CUSTOMER NAME ** WIGET TYPE 2 ** WIGET NAME
    PARTICIPANT 2 NAME ** CUSTOMER NAME ** WIGET TYPE 3 ** WIGET NAME
    PARTICIPANT 2 NAME ** CUSTOMER NAME ** WIGET TYPE 3 ** WIGET NAME
    PARTICIPANT 2 NAME ** CUSTOMER NAME ** WIGET TYPE 4 ** WIGET NAME
    PARTICIPANT 2 NAME ** CUSTOMER NAME ** WIGET TYPE 4 ** WIGET NAME

    and then use looping over the results in order to "prettify" the printed output
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2001
    Posts
    94
    Rep Power
    18

    Thanks - but now what?


    I like the single query idea myself, but I'm at odds on how to do it - mostly because I have little experience with JOINS.

    What I need to do is get all the participants for the selected fundraiser and then get each customer and their associated wigets.

    Any suggestions on how to get started?

    Thanks

    Coach
    A gentle push and a mild arc -
    And the cowhide globe hit home

    Hot Rod Hundley
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    best way to get started is to analyze the relationships between the tables

    1. active_fund ---< fund_participants >--- fund_customers

    funds and customers are many-to-many

    each fund can have zero or more participants
    each participant can belong to zero or more funds

    2. what are key_single and key_double? and how do they relate to funds and/or customers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2001
    Posts
    94
    Rep Power
    18
    Hey Rudy -

    Each fundraiser has unique participants and customers in that each participant can be associated with only one fundraiser and the customers are tied to the participant id who is in turn, tied to the fund id.

    The trick would seem to be this:
    1. Get all participants tied to selected fund
    2 Get all customers tied to each participant
    3. Get all wiget information that is tied to each customer

    Do you think this can be accomplished through a JOIN? I'm trying to figure a way to do something like the following:

    1. select partID, partfname,partlname from fund_particpants where fundID ='$selectedID'
    2 loop through resultset and perform second query for each value from first result set
    3. select c_fname,c_lname, c_id from fund_customers where part_id = partID
    4 select * from wiget_type1,wiget_type2,wiget_type3 where custID = c_id

    I can see what I need to do - just wonderted if there is a way to do it w/o putting results into 3 different arrays and looping through each time.

    BTW - key_single and key_double are db tables that contain order information for wiget types(single name key chains, double name key chains)


    I appreciate your help.

    Coach
    A gentle push and a mild arc -
    And the cowhide globe hit home

    Hot Rod Hundley
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    hey coach, could you do me a favour, list the tables and their columns and show me which columns are related

    i'm sure you can avoid looping a second query inside your query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo