Solved

from an sql into an array difficult question

Posted on 2006-07-10
3
200 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now