Solved

from an sql into an array difficult question

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Simple function not working 7 43
check mysql insert 12 38
AJAX, If No returns don't let the AJAX Call run again 2 32
Make check boxes work 8 41
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

685 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