Solved

from an sql into an array difficult question

Posted on 2006-07-10
3
203 Views
Last Modified: 2013-12-12
Hello,

I want to get data from the database and into an array but cant figure out how to do it becasue it is a bit more complicated than i have done before.

SQL QUERY is
SELECT
      COUNT(transaction_amttobepaid) AS clicks,  
      SUM(transaction_amttobepaid) as comissions,
      transaction_dateoftransaction as dot,
      program_url AS program
      
FROM
      partners_transaction AS t,
      partners_joinpgm AS j,
      partners_merchant as m,
      partners_program AS p
WHERE
      transaction_joinpgmid = '$joinid'
  AND
        t.transaction_joinpgmid = j.joinpgm_id
  AND
    j.joinpgm_merchantid=m.merchant_id
 AND
    transaction_type = 'sale'
  AND  
    transaction_dateoftransaction
BETWEEN
       DATE( '$From' )
  AND  
       DATE( '$To' )
GROUP BY program_url, DATE( transaction_dateoftransaction )
ORDER BY transaction_dateoftransaction ASC";

Returns  the following data:
Clicks comissions dot             program
1       443       2006-06-25       company 1
1       443       2006-06-25       company 2
1       443       2006-06-25       company 3
1       443       2006-06-25       company 4
1       134       2006-07-02       company 1
1       134       2006-07-02       company 2
1       134       2006-07-02       company 3
1       134       2006-07-02       company 4
1       561       2006-07-04       company 1
1       561       2006-07-04       company 2
1       561       2006-07-04       company 3
1       561       2006-07-04       company 4
2       3730       2006-07-09       company 1
2       3730       2006-07-09       company 2
2       3730       2006-07-09       company 3
2       3730       2006-07-09       company 4


the clicks and the comissions are the sum of everything for the day and written out 3 times.  i need company 1's data at company 1 and company 2's data at company2.
0
Comment
Question by:ussher
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
Raynard7 earned 250 total points
ID: 17079348
I have had a look at your query and I think the reason the numbers are repeated is because there is no join between the program and the clicks and commissions - so for each program it is joined to all the others in a carteision join.

Although you have alised all your tables - when you have referred to your fields you have not exclusivley mentioned what table you have joined it to (i reccomend this should always be done) ie p.program_url.

It looks as though you have four tables, t, j, m and p - you have links to t, j and m - but I can not find a reference to p - if you can join that in on the program id then the query should work.

I tried to make it neater below - but without knowing your tables I can not create the missing link.

SELECT
     program_url AS program,
     date(transaction_dateoftransaction) as dot,
     COUNT(transaction_amttobepaid) AS clicks,  
     SUM(transaction_amttobepaid) as comission
FROM
     partners_transaction AS t,
     partners_joinpgm AS j,
     partners_merchant as m,
     partners_program AS p
WHERE
     transaction_joinpgmid = '$joinid'
  AND t.transaction_joinpgmid = j.joinpgm_id
  AND j.joinpgm_merchantid=m.merchant_id
  AND transaction_type = 'sale'
  AND transaction_dateoftransaction BETWEEN DATE( '$From' ) AND DATE( '$To' )
GROUP BY 1, 2
ORDER BY 2 ASC";
0
 
LVL 1

Author Comment

by:ussher
ID: 17079432
hi,

I thought this might be difficult without actually seeing the database.

And you are right that partners program is an afterthought.  the query was ment for another purpose but i have added partners program on and altered it a bit so that i can have the information in needed from partners_program.

I will try to find what join is missing form parnters_program.

thanks

michael
0
 
LVL 1

Author Comment

by:ussher
ID: 17079520
I think that might have worked.

I used your query plus another line
SELECT
     program_url AS program,
     date(transaction_dateoftransaction) as dot,
     COUNT(transaction_amttobepaid) AS clicks,  
     SUM(transaction_amttobepaid) as comission
FROM
     partners_transaction AS t,
     partners_joinpgm AS j,
     partners_merchant as m,
     partners_program AS p
WHERE
     transaction_joinpgmid = 14
  AND t.transaction_joinpgmid = j.joinpgm_id
  AND j.joinpgm_merchantid=m.merchant_id
  AND j.joinpgm_programid=p.program_id
  AND transaction_type = 'sale'
  AND transaction_dateoftransaction BETWEEN DATE( '2006-06-01' ) AND DATE( '2006-07-09' )
GROUP BY 1, 2
ORDER BY 2 ASC

Ill go an test it now
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
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.

803 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