Help with PHP Arrays

[BerbBrown]

Hi All

 

Hope you can help me please.

 

I have some data from a MySQL database table that I need to extract into a specific order within an array. The data will be graphed. The graphing is not the problem but making sure the data is in the right order is.

 

Data Table:

 

 

Product ID Date Value

 

1 01/01/08 10

2 01/01/08 20

3 02/01/08 30

4 02/01/08 40

5 03/01/08 50

6 04/01/08 60

 

I know there are 4 unique dates (01/01/08, 02/01/08, 03/01/08, 04/01/08). The graph will have 4 bars representing the dates.

 

If there is no value for the date I have to provide a zero.

 

The array has to be provided in the following way:

 

 

01/01/08 10, 0, 0, 0

01/01/08 20, 0, 0, 0

02/01/08 0, 30, 0, 0

02/01/08 0, 40, 0, 0

03/01/08 0, 0, 50, 0

04/01/08 0, 0, 0, 60

 

 

Can anyone help please?

 

Thanks in advance.

 

Berb

[Barand]

try something like this

<?php
include('db.inc.php');
/**
* initialise array for each date
*/
$init = array ('',0,0,0,0);
$result = array();
/**
* get the data
*/
$sql = "SELECT proddate, MONTH(proddate), value FROM berbbrown
        WHERE MONTH(proddate) BETWEEN 1 AND 4";
$res = mysql_query($sql);
$i=0;
while (list($d, $m, $v) = mysql_fetch_row($res))
{
    $result[$i] = $init;
    $result[$i][0] = $d;            // store date
    $result[$i][$m] = $v;           // store value in its month's slot
    $i++;
}

/**
* results
*/

foreach ($result as $data) echo join (', ', $data) , '<br />';

?>

-->
2008-01-01, 10, 0, 0, 0
2008-01-01, 20, 0, 0, 0
2008-02-01, 0, 30, 0, 0
2008-02-01, 0, 40, 0, 0
2008-03-01, 0, 0, 50, 0
2008-04-01, 0, 0, 0, 60

[BerbBrown]

Hi

 

Thank you for your reply.

 

I wondered if I could elaborate further on the data that I am using.  The dataset looks like:

 

Order Date      Product        Quantity

 

30/11/07        Product 1      10

02/12/07        Product 2      20

02/12/07        Product 3      30

03/12/07        Product 4      40

03/12/07        Product 5      50

04/12/07        Product 6      60

02/02/08        Product 7      70

 

When the query is run we have no idea how many products are being returned.  My previous example provided 4.

 

I need to create the data as a stacked bar chart so I need to show the data as in the attached PNG.

 

I am absolutely stuck.  HELP!!.

 

Many thanks

 

Robert

 

 

 

 

 

 

 

 

 

 

 

[attachment deleted by admin]

[BerbBrown]

Hi Barry

 

Just checked your website.  I would be happy to purchase your BAAChart class.  Would my previous post work with your graph?

 

I am becoming frustrated with the various other Chart classes out there.

 

Many thanks

 

Robert Brown

 

 

[Barand]

30/11/07

 

If that is an example of how the date is stored in you database then change it. That format is totally useless in a database.

 

Use column type DATE, format YYYY-MM-DD. Then you can order by dates, select date ranges, do date comparisons, use datetime functions. You'll find very little works with dd/mm/yy.

 

I don't know what your query is, so can there be any number of dates and products returned?

What are the maximum numbers of each that can be returned?

[BerbBrown]

Hi

 

Thank you for your reply.  I appreciate the time you have taken to answer my posts.

 

There can any number of values returned.

 

The attachment contains the typical data returned along with how the data should be transformed. 

 

The range of dates could be different and not necessarily consecutive months.

 

There may be more products on a certain day/date.

 

I hope this makes more sense.

 

Many thanks

 

Robert

 

 

 

[attachment deleted by admin]

[Barand]

I used your data

[pre]+----+-----------+------------+----------+

| id | product  | orderdate  | quantity |

+----+-----------+------------+----------+

|  7 | Product 1 | 2007-11-30 |      10 |

|  8 | Product 2 | 2007-12-02 |      20 |

|  9 | Product 3 | 2007-12-02 |      30 |

| 10 | Product 4 | 2007-12-03 |      40 |

| 11 | Product 5 | 2007-12-03 |      50 |

| 12 | Product 6 | 2007-12-04 |      60 |

| 13 | Product 7 | 2008-02-02 |      70 |

+----+-----------+------------+----------+[/pre]

 

This code

<?php
include('db.inc.php');
include('baachart.php');
/**
* initialise array for each date
*/
$init = array ();
$result = array();
$sql = "SELECT DISTINCT orderdate FROM berbbrown ORDER BY orderdate";
$res = mysql_query($sql);
while (list($d) = mysql_fetch_row($res))
{
    $dates[] = date('d M y', strtotime($d));                       // x axis labels
    $init[$d] = 0;
}
/**
* get the data
*/
$sql = "SELECT product, orderdate, quantity FROM berbbrown
        ORDER BY product, orderdate";
$res = mysql_query($sql);

while (list($p, $d, $q) = mysql_fetch_row($res))
{
    if (!isset($result[$p])) $result[$p] = $init;
    $result[$p][$d] = $q;                          // store value in its date's slot
    $i++;
}

/**
* results
*/

$chart = new baachart(500);
$chart->SetTitle ('Daily Orders','');
$chart->setXAxis ('Date', 1);
$chart->setYAxis ('Value', 0, 300, 50, 1);
$chart->setXLabels($dates);
$chart->setSeriesColor (6, 160,160,160);
$chart->setSeriesColor (7, 200,200,200);

foreach ($result as $prod => $vals)
{
    $chart->addDataSeries ('C', 1, array_values($vals), $prod);
}

$chart->display();
?>

 

result attached

 

[attachment deleted by admin]

[BerbBrown]

Hi Barry

 

Many thanks.  It looks great.

 

Can you tell me if you can add data values to the bars?

 

Many thanks

 

Robert

 

 

[Barand]

That's a "work in progress". There is a showValues(series_number) but not perfected (or documented) yet.

[BerbBrown]

Hi

 

Thank you for all your help.

 

I would be grateful if you could let me know when the next version will be available as I have three domains where the component can be used.

 

Cheers

 

Robert

 

 

[corbin]

Wow your chart class looks pretty impressive.

 

 

 

Just felt the need to say that lol.