Solved

from an sql into an array difficult question

Posted on 2006-07-10
3
211 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

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…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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…

635 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