Sales Tracking Help Needed

[Anon]

Working on a website and require some sales tracking displays...

The primary display I need tracks sales by month for the current year.. the 3 columns I need to fill are:

Year and Month|Type 1 Sales|Type 2 Sales| Total Profit For month

eg: 2001 10 | 1 | 5 | $9999

The info is stord in one table.. the fields i am concerned with are timestamp (format: yyyymmddhhmmss) sales_type (either 1 or 2) sale confirm (needs to be 1 to confirm the sale)..

Basically im trying to get it into an array then just go through the array echoing as needed... prefably the arry will return in the form:

$array['Year-Month']['type1sales']=1
$array['Year-Month']['type2sales']=1
$array['Year-Month']['profits']=5

eg:

$array['2001-10']['type1sales']=1
$array['2001-10']['type2sales']=1
$array['2001-10']['profits']=999

My prob is im going round in circles trying to get all the conditions met in the queries:

Must be grouped by that month.. eg all 2001-20 values together for the current year.. must show all months even if no sales met (so 0s in)..

Please can someone advis eon the queries to build the array I require?

Many thanks in advance...

[vsego]

THREE columns?! :-)

1. Grouped by month (idea, not finished code!):
   SELECT substring(timestamp_field, 1, 6) as yearmonth,... FROM... GROUP BY yearmonth
   This will include field consisting of first 6 chars of timestamp (yyyymm), naming it (for this query only) "yearmonth", then grouping it by that field.

2. when writing your output to string (while(...mysql_fetch_row...)...), you need to compare current month (the one to be outputed) with the last one:

$lastmonth = "";
while ($row = mysql_fetch_row($sqlResult)) {
if ($lastmonth != $row["yearmonth"]) {
$lastmonth = $row["yearmonth"];
output_header_for_month($lastmonth);
}
output_data($row);
}

output_data() outputs data contained in $row and output_header_for_month() outputs header for $lastmonth (you define these functions according to your needs). This can, of course, be output to stream OR array or anything you want.

To get all the months - even unused ones - you need to modify

if ($lastmonth != $row["yearmonth"]) {
$lastmonth = $row["yearmonth"];
output_header_for_month($lastmonth);
}

How? You need to include

while(???) output_header_for_month(???);

that would output ALL the months (from the one right after $lastmonth to (and including) $row["yearmonth"]). After while(){}, call

$lastmonth = $row["yearmonth"];

As for "???", make them up for yourself. It's not too hard, but I don't feel like doing it. If you get stuck, post a question.

Good luck!

P.S. Check substr() function - I think it'll prove usefull on solving several problems here.

[Anon]

Cheers for help Vedran!

In the end i went for this:

/ set current year and 13 months in format YYYYMM for the whole year /
$year=date("Y");
$month=range(1,12);
for($i=0; $i<12; $i++)
{
add extra 0 if needed
$yrmonth=$year.$month[$i];
if(strlen($yrmonth)==5)
{
$yrmonth=$year."0".substr($yrmonth,4,5);
}

     //do query for each month of the year and place in 2D array $yearly_sales as $yearly_sales["yyyymm"]["field"]

}
//return $yearly sales from the function

//to display info

while(list($key, $val) = each($yearly_sales))
{
$yr = substr($key,0,4);
$month = strftime("%B", mktime(0,0,0,substr($key,4,2), 1, $year));?>
<tr><td align=left>&nbsp;<?= $yr?>&nbsp;<?= $month?></td>

etc etc

}

ya right about substr..

many thanks for help.

[vsego]

Consider replacing

$yrmonth=$year.$month[$i];
if(strlen($yrmonth)==5) {
$yrmonth=$year."0".substr($yrmonth,4,5);
}

with

$yrmonth=$year.($i < 9 ? "0" : "").($i + 1);

or, even better

$yrmonth=sprintf("%4d%2d", $year, $i + 1);

In both cases, "$month=range(1,12);" is not needed.

[Anon]

Thanks for tips..

Appreciate it!

[scottt]

This thread was really informative...... I'm newb to this ... but I am sure being here I can learn many things..
[right]Sales tracking sales tracking software Sales lead tracking software[/right]