problem with this script

paulus4605

I created this page see the complete script below
<head>
<style type="text/css">
body, td, th, h1, h2 {font-family: sans-serif;}
body, td, th {font-size: 100%;}
a:link { font-weight:bold; color:E00000; text-decoration:none }
a:visited { font-weight:bold; color:E00000; text-decoration:none }
a:hover { font-weight:bold; color:#E00000; text-decoration:none }
a:active { font-weight:bold; color:#F00000; text-decoration:none }
a:focus { font-weight:bold; color:#E00000; text-decoration:none }
.text1 {font-family: Arial, Helvetica, sans-serif;font-size: 16px;color: White;text-align : left;}
</style>
<script language="javascript">
function call(location)
{
url = new String(parent.sub.location.href);
arrayOfStrings = url.split('#');
parent.sub.location = arrayOfStrings[0] + location;
return false;
}
</script>
</head>

include "datas/connection_data.php";
include "datas/connect.php";
include "datas/stats2.php";

$Dblnk=connect ();

if ($res=send_sql($Data_Base, $Query))
{
while ($row = mysql_fetch_row ($res))
{
$Line1 = $Line1.$row[0]." ";

}

<TABLE bgcolor="#777888" border="1" width="100%">
<TR>
<TD align="center" colspan="22" bgcolor="grey">Rem amount POR</TD></TR>
<TR>
<TD align="center" colspan="1">Dealer</TD>
<TD align="center" colspan="1">Jan2004</TD>
<TD align="center" colspan="1">Feb2004</TD>
<TD align="center" colspan="1">Mar2004</TD>
<TD align="center" colspan="1">Apr2004</TD>
<TD align="center" colspan="1">MAY2004</TD>
<TD align="center" colspan="1">JUN2004</TD>
<TD align="center" colspan="1">JUL2004</TD>
<TD align="center" colspan="1">AUG2004</TD>
<TD align="center" colspan="1">Sep2004</TD>
<TD align="center" colspan="1">OCT2004</TD>
<TD align="center" colspan="1">NOV2004</TD>
<TD align="center" colspan="1">Dec2004</TD>
<TD align="center" colspan="1">Totaal</TD>
<TR align="center">
<TD width="10%">Dealer</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD></TD>
</TR>
<TR>
<TD width="10%"><? echo "$Line1" ?></TD></TR>

</TR>
</TABLE>

the query that I use to display at " <? echo "$line1" ?> is this one

<?
$Query = "SELECT DISTINCT DEALER_REM FROM ELEC_REM_NOTE WHERE CURRENT_MONTH like '%2004'group by Dealer_rem";
?>

One of the queries that I need is the following one

$jan = "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";

I have to use this query 12 times once for each month of the year.

the problem that I have is that the result of $jan has to be matched with the result of $Query and when the result is not equal it has to display "0" (=zero).

example a salesperson callled dave sold in January 5 items and in February nothing on the other hand his collegue (Stanley) sold 3 Items in January and 1 in February.

I already have all the salespersons in my table but I need to find a way to match the results from Dave with the ones from dave and so on.

salesperson January | February
dave| 5 | 0 |
Stanley| 3| 1|

and this for all the rest of the month

thanks for your coop and feedback

Paul

k9sar72

Originally posted by paulus4605
I created this page see the complete script below
<head>
<style type="text/css">
body, td, th, h1, h2 {font-family: sans-serif;}
body, td, th {font-size: 100%;}
a:link { font-weight:bold; color:E00000; text-decoration:none }
a:visited { font-weight:bold; color:E00000; text-decoration:none }
a:hover { font-weight:bold; color:#E00000; text-decoration:none }
a:active { font-weight:bold; color:#F00000; text-decoration:none }
a:focus { font-weight:bold; color:#E00000; text-decoration:none }
.text1 {font-family: Arial, Helvetica, sans-serif;font-size: 16px;color: White;text-align : left;}
</style>
<script language="javascript">
function call(location)
{
url = new String(parent.sub.location.href);
arrayOfStrings = url.split('#');
parent.sub.location = arrayOfStrings[0] + location;
return false;
}
</script>
</head>

<body bgcolor="#777888">

<?

include "datas/connection_data.php";
include "datas/connect.php";
include "datas/stats2.php";

$Dblnk=connect ();

if ($res=send_sql($Data_Base, $Query))
{
while ($row = mysql_fetch_row ($res))
{
$Line1 = $Line1.$row[0]." ";
} 
}

?>

<TABLE bgcolor="#777888" border="1" width="100%">
<TR>
<TD align="center" colspan="22" bgcolor="grey">Rem amount POR</TD></TR>
<TR>
<TD align="center" colspan="1">Dealer</TD>
<TD align="center" colspan="1">Jan2004</TD>
<TD align="center" colspan="1">Feb2004</TD>
<TD align="center" colspan="1">Mar2004</TD>
<TD align="center" colspan="1">Apr2004</TD>
<TD align="center" colspan="1">MAY2004</TD>
<TD align="center" colspan="1">JUN2004</TD>
<TD align="center" colspan="1">JUL2004</TD>
<TD align="center" colspan="1">AUG2004</TD>
<TD align="center" colspan="1">Sep2004</TD>
<TD align="center" colspan="1">OCT2004</TD>
<TD align="center" colspan="1">NOV2004</TD>
<TD align="center" colspan="1">Dec2004</TD>
<TD align="center" colspan="1">Totaal</TD>
<TR align="center">
<TD width="10%">Dealer</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD>
<TD width="10%">Amount</TD></TD>
</TR>
<TR>
<TD width="10%"><? echo "$Line1" ?></TD></TR>

</TR>
</TABLE>

the query that I use to display at " <? echo "$line1" ?> is this one

<?
$Query = "SELECT DISTINCT DEALER_REM FROM ELEC_REM_NOTE WHERE CURRENT_MONTH like '%2004'group by Dealer_rem";
?>

One of the queries that I need is the following one

$jan = "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";

I have to use this query 12 times once for each month of the year.

the problem that I have is that the result of $jan has to be matched with the result of $Query and when the result is not equal it has to display "0" (=zero).

example a salesperson callled dave sold in January 5 items and in February nothing on the other hand his collegue (Stanley) sold 3 Items in January and 1 in February.

I already have all the salespersons in my table but I need to find a way to match the results from Dave with the ones from dave and so on.

salesperson January | February
dave| 5 | 0 |
Stanley| 3| 1|

and this for all the rest of the month

thanks for your coop and feedback

Paul [/B]

If your returning your query results in an array you could try array_pad().
I'm not certain if it will work in this instance (without the data and script to test).


$result = mysql_fetch_array($jan);
$padded = array_pad($result, 12, 0)
// inserts  a 0 for any null values a maximum of 12 times.

That should pad any Null values to zero.

Run your query through a couple tests using var_dump($padded) to see how the output looks.

If array_pad won't cut it.....

Try doing a for loop and array_push.

$new_result = array();
for ($i = 0; $i < 12; $i++) {
$query[$i] = "SELECT ......"
$result[$i] = mysql_query ($query[$i], $connection);
while ($sqlarray = mysql_fetch_array($result[$i])) {
if isset($sqlarray[0]) {
    array_push($new_result, $sqlarray[0]);
}
else
{
    array_push($new_result, "0");
}
....

paulus4605

The question is not the Query as such the problem is situated in matching the result of query 2 with the result of Query 1

I mean In Jan2004 I have 2 salespersons = result of Query 1

they sell x items each (result Query 2)

if in February 2004 A third person starts to work for me (his name is Bert) the first Query would come up with
Bert , Dave and stanley (ordered by Salesperson .

at this point Query 2 would come up with
Dave 0 items Stanley 1 item and Bert 4 Items also ordered by salesperson

now I need to match the results from Query 2 with the result of Query 1 and I don't know how to solve this problem

and if Dave is leaving in March and is been replaced by charley who sold 3 items Stanley 2 Bert 3 I would get the following result

the content of the table looks like this

Name jan feb march
Bert 0 4 3
Charley 0 0 3
Dave 5 0 0
Stanley 3 1 2

thanks again for your coop and feedback