Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

from an sql into an array difficult question

Posted on 2006-07-10
3
Medium Priority
?
228 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 750 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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.
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 a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

972 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