PHP Language - question about synchronising results from 2 queries

Author

question about synchronising results from 2 queries

paulus4605

2004-08-23, 3:56 pm

Dears
I have the following problem

I’m using a query to get all the data from my database from the past
year

the second query is displaying the results by month.
How can I match the second query with the first query in order to get
the results from the same store on the same row?

example :
Ford got $2000,- in January and in February they earned $3000,-
BMW got $15000 in January and $7500 in February

in this example FORD AND BMW are the results from Query 1 and the
amounts mentioned are the results of Query 2. How can I print these
results on in the same row where I Ford or BMW is displayed?
thanks for your coop and feedback

Paul

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/PHP-synchro...pict142196.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=475576

Michael Austin

2004-08-23, 3:56 pm

paulus4605 wrote:

> Dears
> I have the following problem
>
> I’m using a query to get all the data from my database from the past
> year
>
> the second query is displaying the results by month.
> How can I match the second query with the first query in order to get
> the results from the same store on the same row?
>
> example :
> Ford got $2000,- in January and in February they earned $3000,-
> BMW got $15000 in January and $7500 in February
>
> in this example FORD AND BMW are the results from Query 1 and the
> amounts mentioned are the results of Query 2. How can I print these
> results on in the same row where I Ford or BMW is displayed?
> thanks for your coop and feedback
>
> Paul
>

without knowing your table definitions or the two queries, it is a bit difficult
to make suggestions, You can use left outer joins or "normal" joins depending
on lots of things...

table 1
id name
1 Ford
2 BMW

table2
id date amount
1 1/1/2004 1000
1 1/2/2004 1000
2 1/1/2004 500
2 1/2/2004 7000
1 2/3/2004 1000
2 2/9/2004 5000

select a.name as name, sum(b.amount) as jan,
sum(c.amount) as feb,
sum(d.amount) as mar....
from table1 a
left outer join table2 b on a.id = b.id
and extract(year from date) = '2004' and extract(MONTH from date) = '1'
left outer join table2 c on a.id = c.id
and extract(year from date) = '2004' and extract(MONTH from date) = '2'
left outer join table2 d on a.id = d.id
and extract(year from date) = '2004' and extract(MONTH from date) = '3'
.....
group by name, jan, feb, mar,....;

YMMV and you may need to tweak the syntax a bit... but should produce the output

name jan feb mar apr ....
FORD 1000 5000 3000
BMW 3000 7500 .....

Then using PHP you can add the "wording" if you want...

--
Michael Austin.
Consultant - Not Available.
Donations still welcomed. Http://www.firstdbasource.com/donations.html
:)

paulus4605

2004-08-23, 3:56 pm

Michael
thanks for your reply

the queries look like this

$Year ="SELECT DISTINCT DEALER_REM FROM ELEC_REM_NOTE WHERE
CURRENT_MONTH LIKE "%2004" GROUP BY DEALER_REM";

the second QUERY = $Month =" SELECT DISTINCT DEALER_REM WHERE
CURRENT_MONTH =’JAN2004’ AND BLOC_ID IN (1,2,3,4,5,7,8,10,12) group by
DEALER_REM

thanks for your feedback

Paul

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/PHP-synchro...pict142196.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=476061

Michael Austin

2004-08-23, 8:56 pm

paulus4605 wrote:

> Michael
> thanks for your reply
>
> the queries look like this
>
> $Year ="SELECT DISTINCT DEALER_REM FROM ELEC_REM_NOTE WHERE
> CURRENT_MONTH LIKE "%2004" GROUP BY DEALER_REM";
>
> the second QUERY = $Month =" SELECT DISTINCT DEALER_REM WHERE
> CURRENT_MONTH =’JAN2004’ AND BLOC_ID IN (1,2,3,4,5,7,8,10,12) group by
> DEALER_REM

and what column (field) contains the Amount information - you are not selecting
it... You have not selected it. The table that contains the dealer id should be
unique and therefore not require a DISTINCT clause. the 2nd query also does not
contain a table name.

show me the output of the describe for table1 and table2.

>
>
>

> thanks for your feedback

You're welcome...
>
> Paul
>

--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

paulus4605

2004-08-24, 4:37 pm

"Michael Austin" wrote:
> paulus4605 wrote:
>
> _> Michael
> _> thanks for your reply
> _> 
> _> the queries look like this 
> _> 
> _> $Year ="SELECT DISTINCT DEALER_REM
> FROM ELEC_REM_NOTE WHERE
> _> CURRENT_MONTH LIKE "%2004" GROUP BY
> DEALER_REM";
> _> 
> _> the second QUERY = $Month =" SELECT
> DISTINCT DEALER_REM WHERE
> _> CURRENT_MONTH =’JAN2004’ AND
> BLOC_ID IN (1,2,3,4,5,7,8,10,12) group by
> _> DEALER_REM
>
> and what column (field) contains the Amount information - you
> are not selecting
> it... You have not selected it. The table that contains the
> dealer id should be
> unique and therefore not require a DISTINCT clause. the 2nd
> query also does not
> contain a table name.
>
> show me the output of the describe for table1 and table2.
>
>
> _> 
> _> 
> _> 
>
> _> thanks for your feedback
>
> You’re welcome...
> _> 
> _> Paul
> _> 
>
>
> --
> Michael Austin.
> Consultant - Available.
> Donations welcomed.
> Http://www.firstdbasource.com/donations.html
> :)

oeps stupid of me

the both tables that I use are "FROM ELEC_REM_NOTE"

so the first query is $Year ="SELECT DISTINCT DEALER_REM FROM
ELEC_REM_NOTE WHERE CURRENT_MONTH LIKE"%2004" GROUP BY DEALER_REM";

the second query looks like this
$MONTH = "SELECT DISTINCT DEALER_REM ,SUM(TOTAL_REM_AMOUNT) FROM
ELEC_REM_NOTE WHERE CURRENT_MONTH =’JAN2004’ AND BLOC_ID IN
(1,2,3,4,5,7,8,10,12) GROUP BY DEALER_REM

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/PHP-synchro...pict142196.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=476807

paulus4605

2004-08-24, 4:37 pm

Michael Austin

2004-08-25, 3:55 pm

paulus4605 wrote:

<snip>

> so the first query is $Year ="SELECT DISTINCT DEALER_REM FROM
> ELEC_REM_NOTE WHERE CURRENT_MONTH LIKE"%2004" GROUP BY DEALER_REM";
>
> the second query looks like this
> $MONTH = "SELECT DISTINCT DEALER_REM ,SUM(TOTAL_REM_AMOUNT) FROM
> ELEC_REM_NOTE WHERE CURRENT_MONTH =’JAN2004’ AND BLOC_ID IN
> (1,2,3,4,5,7,8,10,12) GROUP BY DEALER_REM
>

Same as I previously described:

select a.name as name, sum(b.amount) as jan,
sum(c.amount) as feb,
sum(d.amount) as mar....
from table1 a
left outer join table1 b on a.name = b.name
and CURRENT_MONTH =’JAN2004’
AND BLOC_ID IN (1,2,3,4,5,7,8,10,12)
left outer join table1 c on a.name = c.name
and CURRENT_MONTH =’FEB2004’
AND BLOC_ID IN (1,2,3,4,5,7,8,10,12)
....
group by a.name, b.amount, c.amount, d.amount,,,,;

You should get the distinct names and sums for each month.

if you want to insert the actual text, then it gets a bit more conveluted and
may not fit on one line... If you are using PHP then display the results in a table

NAME JAN FEB MAR ...
FORD 2000 4000 ...
BMW 9000 3000 ...

--
Michael Austin.
Consultant -Not Available.
Donations still welcomed. Http://www.firstdbasource.com/donations.html
:)

Sponsored Links