...

View Full Version : combine arrays to create a string



kdalts
01-28-2012, 12:32 PM
Hi Guys..

I am trying to create the following variable for a Javascript trend map -


[new Date(2011, 0), 599, undefined, undefined, 156, undefined, undefined, 232, undefined, undefined],

My challenges are -

This needs repeating for each month of data - this will vary as the source data is updated stored in MySql
the number of values for each line could also vary depending on which region is selected ie: if region1 is selected there could be 8 values for each month but if region 2 is selected there could be 10.


I've started by trying to use for loops to figure out the number of months and then to figure out the number of values and assign them to an array ($dataset_months) and ($dataset_partners). Where I'm stuck is how to create the full concatenated variable based on these 2 for loops.


for($m=1; $m<=$num_months; ++$m){//repeat this as many times as there are months
$dataset_month[$m] = "[new Date(2011, " . $m;

}

for($i=1; $i<=$num_partners; ++$i){//repeat this as many times as there are partners
$dataset_partner[$i] = " , 599, undefined, undefined";

}

Assuming there are 4 months and 3 partners the end result should be 4 lines of code with 3 values in each row like below (which I typed by the way)




[new Date(2011, 1), $partner1, undefined, undefined, $partner2, undefined, undefined, $partner3, undefined, undefined],

[new Date(2011, 2), $partner1, undefined, undefined, $partner2, undefined, undefined, $partner3, undefined, undefined],

[new Date(2011, 3), $partner1, undefined, undefined, $partner2, undefined, undefined, $partner3, undefined, undefined],



Then I need to figure out how to create this date format from my SQL data that is currently text - jez, I'll think about that later:eek:

Fou-Lu
01-28-2012, 05:37 PM
So this is the creation of the two arrays themselves. What is the value for your actual arrays you want to concat (just use print_r($arrayName) and post that).

Too early in the morning for me :/
Since these are already in strings, you just need to combine them up. Given what you have for your array creation (although lots of hardcode that doesn't match either), you just need to iterate the "outer" loop, and implode the "inner" loop:


$sResult = '';
foreach ($dataset_month AS $sMonth)
{
$sResult .= $month;
$sResult .= implode('', $dataset_partner);
$sResult .= '], ' . PHP_EOL;
}

Given the unusual string structure we have here, it would be much easier to build this from raw data than from strings that have been cut up.

kdalts
01-29-2012, 10:34 AM
Thanks FL... I'm still lost

here is the r_print of the 2 arrays -

Array ( [1] => [new Date(2011, 1 [2] => [new Date(2011, 2 [3] => [new Date(2011, 3 [4] => [new Date(2011, 4 )

Array ( [1] => , 599, undefined, undefined [2] => , 599, undefined, undefined [3] => , 599, undefined, undefined [4] => , 599, undefined, undefined [5] => , 599, undefined, undefined [6] => , 599, undefined, undefined [7] => , 599, undefined, undefined [8] => , 599, undefined, undefined )


I need to concatenate them (I think) but the 1st part and 2nd part will never be the same number of times. Ie: array 1 (month) could have 36 entries and the 2nd array (partner) will have any value bewteen 5 and 10.

I'm not even sure this is the best way to get this string but my end goal is to create the following Javascript as per Google Charts. As you can see I managed to create the columns by embeding my php code to create the a string with a php variable inside it. But the rows are more complicated. The example below has hardcoded values.


<script type='text/javascript' src='http://www.google.com/jsapi'></script>
<script type='text/javascript'>
google.load('visualization', '1', {'packages':['annotatedtimeline']});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = new google.visualization.DataTable();
data.addColumn('date', 'Date');
<?php
for($i=0; $i<3; ++$i){//repeat this as many times as there are partners
echo "data.addColumn('number', '" . substr($partner[$i],0,3) . "');";
echo "data.addColumn('string', 'title1');";
echo "data.addColumn('string', 'text1');";
}
?>
data.addRows([
[new Date(2011, 0), 599, undefined, undefined, 156, undefined, undefined, 232, undefined, undefined],
[new Date(2011, 1), 654, undefined, undefined, 156, undefined, undefined, 249, undefined, undefined],
[new Date(2011, 2), 700, undefined, undefined, 98, undefined, undefined, 343, undefined, undefined],
[new Date(2011, 3), 678, undefined, undefined, 123, undefined, undefined, 356, undefined, undefined],
[new Date(2011, 4), 699, undefined, undefined, 111, undefined, undefined, 378, undefined, undefined],
[new Date(2011, 5), 654, undefined, undefined, 156, undefined, undefined, 345, undefined, undefined],
[new Date(2011, 6), 767, undefined, undefined, 156, undefined, undefined, 367, undefined, undefined],
[new Date(2011, 7), 812, undefined, undefined, 98, undefined, undefined, 390, undefined, undefined],
[new Date(2011, 8), 789, undefined, undefined, 123, undefined, undefined, 421, undefined, undefined],
[new Date(2011, 9), 654, undefined, undefined, 156, undefined, undefined, 456, undefined, undefined],
[new Date(2011, 10), 856, undefined, undefined, 156, undefined, undefined, 412, undefined, undefined],
[new Date(2011, 11), 900, undefined, undefined, 98, undefined, undefined, 432, undefined, undefined],
[new Date(2012, 0), 934, undefined, undefined, 123, undefined, undefined, 444, undefined, undefined]


]);


var chart = new google.visualization.AnnotatedTimeLine(document.getElementById('chart_div'));
chart.draw(data, {displayAnnotations: true, displayLegendValues: true, displayAnnotationsFilter : false, displayAnnotations : false, dateFormat : 'MMM-yy', displayAnnotationsFilter : true, legendPosition : 'newRow', scaleType : 'maximized'});
}
</script>

Fou-Lu
01-29-2012, 05:57 PM
There is clearly something wrong with your data and handling. In that second array, every value is the same and do not fully match any of the values in question. Your second foreach does use a hard coded number though.
Looks like the only part I'm missing on the construction is $sResult .= $month; should be $sResult .= $month . ')'; or add the ) in the original construction.
Like I said, I personally think this will be easier to rebuild from raw data. Where is this all coming from in the first place?

kdalts
01-31-2012, 10:17 AM
Thanks for your help. To answer your question my values are in MySQl rather than the fixed values in my post.

This is what I have so far but my combined array must have 13 x 8 where 13 is the number of months and 8 is the number of partners - these will be variable so they are pulled from a database as the number of months increases.

Simplified version, I need to end up with the following strings -

[$Date_Val1, $partnerValue1, $partnerValue2, $partnerValue3,$partnerValue4, $partnerValue5, $partnerValue6, $partnerValue7, $partnerValue8],

[$Date_Val2, $partnerValue1, $partnerValue2, $partnerValue3,$partnerValue4, $partnerValue5, $partnerValue6, $partnerValue7, $partnerValue8],

ETC...repeated 13 times - 1 for each date


My months array is spot on and works perfectly... its' the partner array that is wrong and I think it's to do with my select clause and how the records are grouped. My code is almost there but I am getting 104 values in the partner array. The 104 comes from the fact that I am pulling 13 months and 8 partners (13 x 8 = 104) in the WHILE loop. When I change the Select clause to group only by partner I get my 8 values but I only get totals for the whole 13 months but I need them by month.

Maybe I'm probably completely on the wrong track here. In summary all I am trying to do is pull dispatch totals x partner for each month from 1 select clause and then create a string for my Javascript chart in the format shown above.

Here is what I have so far.. some vars have been added at the top which are normally passed from another page as this is my testing script.


<?php
include '../config.php'; //connect to the database
$region = "EMEA";
$dataset_partner = Array();
$year_month = Array();
$P1M1 = 500;
$num_partners = 8;
$sResult = '';


//DEAL WITH THE MONTHS FOR THE TREND DATA
$select_mth_sql = "SELECT DISTINCT Month FROM dispatches"; //months
$result_select_mth = mysql_query($select_mth_sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$num_months = mysql_num_rows($result_select_mth);

while($row = mysql_fetch_assoc ($result_select_mth)){
$year = date ( 'Y' , strtotime($row['Month'] )) . ", ";
$month = date ( 'm' , strtotime($row['Month'] ));
$year_month = $year . $month;
//Formats the date that the JS script uses - this works as I have checked
//that 13 months slips to next year perfectly

//for($i=0; $i<=$num_months; ++$i){//repeat this as many times as there are months
$dataset_month[] = "[new Date(" . $year_month . ")";//assign to dataset_month array - works perfectly

//}

}
//echo "This is the DATE Array<br>";
//print_r($dataset_month); //check and works
//echo "</p>";

//** DEAL WITH THE PARNERS TREND DATA ***************************
//Fetch records from database - issue with grouping needs resolved - I think
$query = "SELECT Dispatched_TPM, Month, Count(SERVICEORDERID), partners.shortname
FROM dispatches
INNER JOIN partners ON partner_name = Dispatched_TPM AND GEO = '$region'
WHERE Region='$region' AND STATUSTEXT = 'Completed'
GROUP BY Month, Dispatched_TPM
ORDER BY Month, Dispatched_TPM ASC";


$results = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);
$num_partners = mysql_num_rows($results);

while($row = mysql_fetch_assoc ($results)){
echo $row['Dispatched_TPM'] . " ";//echo can be removed - just testing to see return
echo $row['Count(SERVICEORDERID)'] . " ";//echo can be removed - just testing to see return
echo $row['Month']; //echo can be removed - just testing to see return
echo "<br>";//echo can be removed - just testing to see return
$dispatches[] = $row['Count(SERVICEORDERID)'];//assigned values to the array but only need 8 values - 1 per partner. Looping 104 times - this is the problem
$dataset_partner[] = $row['Count(SERVICEORDERID)'] . ", ";
}


//echo "</p>This is the PARTNER Array<br>";
//print_r($dataset_partner);



//NOW JOIN IT ALL TOGETHER TO CREATE THE JS SCRIPT
foreach ($dataset_month AS $sMonth)
{
$sResult .= $sMonth . ')';
$sResult .= implode('', $dataset_partner);
$sResult .= '],<br>' . PHP_EOL;
}
echo $sResult;

echo "<br>";


?>

Fou-Lu
01-31-2012, 02:39 PM
You definitely don't need more than the one query. 104 records / 13 = 8, which matches your description. Assuming the recordset contains exactly 8 records per month, then your second query is all you need. I assume since you are using strtotime that you have a datetime datatype for the month.
What is the relationship between this partners table and these results: 599, undefined, undefined, 156, undefined, undefined, 232, undefined, undefined? Given the 104 records, I assume that these are a NULL entry for some field in the table?

kdalts
01-31-2012, 02:59 PM
Hi Fou-Lu... thanks for replying...

Firstly, I can confirm that there will be a consistent number of partners for each month. I fixed this using the JOIN in the SELECT clause to ensure that only recognised approved partners were pulled.

Second part. There is no relationship right now between my tables and the JavaScript string. That's the bit I'm trying to figure out. This is the JS....
http://code.google.com/apis/chart/interactive/docs/gallery/annotatedtimeline.html#Configuration_Options

So, ignoring the 'undefined' I am trying to replace the fixed values (599, 156, 232 etc..) with the count[SERVICEORDERID] value for each partner. Make sense?

Fou-Lu
01-31-2012, 03:19 PM
No. I have three numbers in the above, and 5 undefined. What do these three numbers represent; surely they cannot all be the same number?

kdalts
01-31-2012, 04:53 PM
Hi Fou-Lu

The three numbers are hard coded right now, the numbers need to be replaced by Count(SERVICEORDERID). The 'undefined' text is part of the Javascript - it's undefined in this case because there is no requirement to place a label on the chart. So, 1 value and then 2 'undefined' strings for each partner consitutes 1 entry.

Partnervalue1, undefined, undefined,Partnervalue2, undefined, undefined,Partnervalue3, undefined, undefined

as per the http://code.google.com/apis/chart/in...ration_Options

The end result needs to look like this...

[$Date_Val1, $partnerValue1, undefined, undefined, $partnerValue2, undefined, undefined, $partnerValue3, undefined, undefined,$partnerValue4, undefined, undefined, $partnerValue5, undefined, undefined, $partnerValue6, undefined, undefined, $partnerValue7, undefined, undefined, $partnerValue8, undefined, undefined],

[$Date_Val2, $partnerValue1, undefined, undefined, $partnerValue2, undefined, undefined, $partnerValue3, undefined, undefined, $partnerValue4, undefined, undefined, $partnerValue5, undefined, undefined, $partnerValue6, undefined, undefined, $partnerValue7, undefined, undefined, $partnerValue8, undefined, undefined],

ETC...repeated 13 times - 1 for each date

Fou-Lu
01-31-2012, 05:46 PM
Still confused on the relationship between partnerValueX to the data in the database. Its a count, but does partnerValueX == partnerValueY indicating a shared count for all values represented by partnerValueX per record, or does it represent something distinct within the dataset itself? Also, the last post here has 3x the fields that were previously discussed at 24 instead of 8.
So from the hardcoded record:


[new Date(2011, 0), 599, undefined, undefined, 156, undefined, undefined, 232, undefined, undefined],

I'm trying to figure out what 599, 156, and 232 represent in relation to a single entry in the resultset.

kdalts
01-31-2012, 06:25 PM
partnerValueX = a distinct value (a count of all dispatches for this partner for the given month)

About the number of fields:
The months will increase - so this will vary (I have this bit covered)

Maybe this helps - this is how the table is laid out.... except there are over 30,000 records over currently 13 months and about 50 columns. I have no choice on how the database is structured as the data is imported from a corporate SAP system.

partner date serviceorderid
Apple 01/01/2011 50011211
Apple 01/01/2011 50011212
Apple 01/01/2011 50011213
Bannana 01/01/2011 50011222
Bannana 01/01/2011 50011223
Bannana 01/01/2011 50011224
Bannana 01/01/2011 50011225
Pear 01/01/2011 50011214
Pear 01/01/2011 50011215
Pear 01/01/2011 50011216
Pear 01/01/2011 50011217
Pear 01/01/2011 50011218
Pear 01/01/2011 50011219
Pear 01/01/2011 50011220
Pear 01/01/2011 50011221
Apple 01/02/2011 50011226
Apple 01/02/2011 50011227
Bannana 01/02/2011 50011232
Bannana 01/02/2011 50011233
Bannana 01/02/2011 50011234
Bannana 01/02/2011 50011235
Bannana 01/02/2011 50011236
Pear 01/02/2011 50011228
Pear 01/02/2011 50011229
Pear 01/02/2011 50011230
Pear 01/02/2011 50011231


(opps.. not sure how to create a tidy table here)

In this small example I have 3 partners (Apple, Bannana and Pear). There are only 2 months of data (01-Jan-11 and 01-Feb-11). For each parter x month I need to count the number of serviceorderid's

Result =
Apple | Jan-11 | 3
Bannana | Jan-11 | 4
Pear | Jan-11 | 8
Apple | Feb-11 | 2
Bannana | Feb-11 | 5
Pear | Feb-11 | 4


So I would need to create the following string....



[(11,1), 3, undefined, undefined, 4, undefined, undefined, 8, undefined, undefined],

[(11,2), 2, undefined, undefined, 5, undefined, undefined, 4, undefined, undefined],



Each string must have the partnervalue is the serviceorderid count for that month and they must be in the same order for each month.

Fou-Lu
01-31-2012, 06:47 PM
Right, so this is along the lines of what I thought, counts grouped by partner and then ordered by month. Just that each record pulls undefined hardcoded twice per record.
So given the query you currently have, you should literally end up with a result set as you have here:


Apple | Jan-11 | 3
Bannana | Jan-11 | 4
Pear | Jan-11 | 8
Apple | Feb-11 | 2
Bannana | Feb-11 | 5
Pear | Feb-11 | 4

Given some of the other queries here with strtotime in use, the month is in the format of a datetime datatype or possibly a string that is at least parsable by datetime.

A simple array capture and implode:


$aResults = array();
$iLastTime = false;
while ($row = mysql_fetch_assoc($results))
{
$dt = strtotime($row['Month']);
if ($dt != $iLastTime)
{
// These date records are complete.
if (false !== $iLastTime)
{
// Can't terminate a non open record.
$aResults[$dt] .= rtrim($aResults[$dt], ', ') . ']';
}
$iLastTime = $dt; // Change the last time to the new time
$aResults[$dt] = sprintf('[new Date(%d, %d),', date('Y', $dt), date('m', $dt));
}
$aResults[$dt] .= sprintf('%d, undefined, undefined,', $row['Count(SERVICEORDERID)']); // You may want to alias this or use mysql_fetch_array and pull from the field number instead
}

$sResults = implode(',' . PHP_EOL, $aResults);


Try that. One thing to note is that the datetime expects that everything on January 11th for example is all literally Jan-11 with either not time or 00:00. This is because I cheated and used the $dt from the strtotime as my associative index; if the numbers are different it will hack the resulting array to pieces so none of it makes sense.

kdalts
01-31-2012, 07:29 PM
Thanks again,,,

Still something not quite working...

I created a simple table like the one above (with apples and pears:)) and called it test2. Only 3 columns (partner, Month and SERVICEORDERID).

The error message I get is

Notice: Undefined offset: 1293840000 in W:\dev\wamp\www\NetApp\metrics\fou_lu.php on line 25


$query = "SELECT partner, Month, Count(SERVICEORDERID) FROM test2 GROUP BY partner ORDER BY Month ASC";


$results = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);

$aResults = array();
$iLastTime = false;
while ($row = mysql_fetch_assoc($results))
{
$dt = strtotime($row['Month']);
if ($dt != $iLastTime)
{
// These date records are complete.
if (false !== $iLastTime)
{
// Can't terminate a non open record.
$aResults[$dt] .= rtrim($aResults[$dt], ', ') . ']';
}
$iLastTime = $dt; // Change the last time to the new time
$aResults[$dt] = sprintf('[new Date(%d, %d),', date('Y', $dt), date('m', $dt));
}
$aResults[$dt] .= sprintf('%d, undefined, undefined,', $row['Count(SERVICEORDERID)']); // You may want to alias this or use mysql_fetch_array and pull from the field number instead
}

$sResults = implode(',' . PHP_EOL, $aResults);


Is my query correct? or am I missing something? I should let you know that the table for month field is a 'varchar' - not a date format because it fails to import the records if I set it to a datetime format. Does this make any difference? basically, the date is text.

Fou-Lu
01-31-2012, 08:25 PM
No, it won't make a difference for the date, since strtotime will parse Jan-11 as a valid date (and will assume this year), so a varchar is fine. PHP and SQL cannot work with the same datatypes anyway, but it guarantees nothing in regards to sorting dates when they are varchar types as it sorts via strings. This can be cast in SQL to force it to sort by datetime instead of by varchar. This is technically a problem since the string creation relies on the explicit ordering of the recordset. Use strtodate to deal with that, so you want: ORDER BY STR_TO_DATE(Month, '%b-%d') ASC from the looks of it.

The query appears to have the wrong group by. Group by month, and then partner, otherwise the total is only that of a partner.

The notice is because I used the wrong variable. Use this:


if (false !== $iLastTime)
{
// Can't terminate a non open record.
$aResults[$iLastTime] .= rtrim($aResults[$iLastTime], ', ') . ']';
}

$dt is extracted when the new record comes. So if it's currently Jan-11 and switches to Feb-11, then its $dt is that of Feb-11 when there is no current record to terminate. We need that to be Jan-11.


Oooohh I just realized this is Jan 2011, not January 11th. This will effect the results a little more (although, it will likely work but you cannot pull the Year out of it since it will assume its 2011 even if you use Jan-12). Dealing with it in SQL may be the best route. I'll pull the record via timestamp as well since that's less PHP code:


$query = "SELECT partner, UNIX_TIMESTAMP(STR_TO_DATE(Month, '%b-%y')) AS monthyear, Count(SERVICEORDERID) FROM test2 GROUP BY Month, partner ORDER BY STR_TO_DATE(Month, '%b-%y') ASC";


$results = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);

$aResults = array();
$iLastTime = false;
while ($row = mysql_fetch_assoc($results))
{
$dt = $row['monthyear'];
if ($dt != $iLastTime)
{
// These date records are complete.
if (false !== $iLastTime)
{
// Can't terminate a non open record.
$aResults[$iLastTime] .= rtrim($aResults[$iLastTime], ', ') . ']';
}
$iLastTime = $dt; // Change the last time to the new time
$aResults[$dt] = sprintf('[new Date(%d, %d),', date('Y', $dt), date('m', $dt));
}
$aResults[$dt] .= sprintf('%d, undefined, undefined,', $row['Count(SERVICEORDERID)']); // You may want to alias this or use mysql_fetch_array and pull from the field number instead
}

$sResults = implode(',' . PHP_EOL, $aResults);


Try that.

kdalts
02-01-2012, 12:50 PM
Hi Fou-Lu... wow ..this is so close - you're a star:thumbsup:

I needed to make a change to the SELECT date format (highlighted) to get the code to work (changed 'b' to 'd' and added the month...works fine

$query = "SELECT partner, UNIX_TIMESTAMP(STR_TO_DATE(Month, '%d-%M-%y')) AS monthyear, Count(SERVICEORDERID) FROM test2 GROUP BY Month, partner ORDER BY STR_TO_DATE(Month, '%d-%M-%y') ASC";



So there are 2 issues with the output...(I put a <br> in just to make it easy to read - I'll take this out once the code is complete..

The string output looks like this now...



[new Date(2011, 1),4, undefined, undefined,2, undefined, undefined,3, undefined, undefined,<--line is duplicated?
[new Date(2011, 1),4, undefined, undefined,2, undefined, undefined,3, undefined, undefined], <--this line is perfect
[new Date(2011, 2),1, undefined, undefined,2, undefined, undefined,2, undefined, undefined,<-- need to drop last comma and replace with'],'

The final 2 problems are -

The first line is repeated... there should only be 1 for each month
There needs to be a '],' at the end of each line which no comma after the last 'undefined'


I just couldn't figure out how to make these last 2 changes but that should complete the task....

Fou-Lu
02-01-2012, 03:40 PM
Right, I forgot about that last one. While is complete, so you cannot trim it off. Just do so outside of the loop before the implode:


if (($cnt = count($aResult)) > 0)
{
$last = &$aResult[$cnt - 1];
$last = rtrim($last, ', ') . ']';
}

The duplication is right here:


$aResults[$iLastTime] .= rtrim($aResults[$iLastTime], ', ') . ']';

Use = instead of .= otherwise it repeats the entire previous entry and appends the trimmed one. Methinks my intention was to just use .= ']';, but realized I needed to trim it off too.

kdalts
02-01-2012, 04:47 PM
Almost there;)

So the repetition is solved.

The final bracket is still not quite right. Using your code as below I'm getting -

Uninitialized string offset: 0 in W:\dev\wamp\www\NetApp\metrics\fou_loo.php on line 43

Assuming that the &$aResult was a typo and should be $aResult the full code now reads...?


$query = "SELECT partner, UNIX_TIMESTAMP(STR_TO_DATE(Month, '%d-%M-%y')) AS monthyear, Count(SERVICEORDERID) FROM test2 GROUP BY Month, partner ORDER BY STR_TO_DATE(Month, '%d-%M-%y') ASC";


$results = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);

$aResults = array();
$iLastTime = false;
while ($row = mysql_fetch_assoc($results))
{
$dt = $row['monthyear'];
if ($dt != $iLastTime)
{
// These date records are complete.
if (false !== $iLastTime)
{

// Can't terminate a non open record.
$aResults[$iLastTime] = rtrim($aResults[$iLastTime], ', ') . ']';


}

$iLastTime = $dt; // Change the last time to the new time
$aResults[$dt] = sprintf('<br>[new Date(%d, %d),', date('Y', $dt), date('m', $dt));

}

$aResults[$dt] .= sprintf('%d, undefined, undefined,', $row['Count(SERVICEORDERID)']); // You may want to alias this or use mysql_fetch_array and pull from the field number instead


}
if (($cnt = count($aResult)) > 0)
{
$last = $aResult[$cnt - 1];<--Uninitialized string offset: 0 in
$last = rtrim($last, ', ') . ']';
}
$sResults = implode(',' . PHP_EOL, $aResults);

Almost there!!!!

Fou-Lu
02-01-2012, 05:22 PM
Hmm, yes that's right. This is indexed by $dt not numerical.


if (count($aResult) > 0)
{
$last = &$aResult[array_pop(array_keys($aResult))];
$last = rtrim($last, ', ') . ']';
}


Another possible solution to keep it from being redundant is to not close it within the loop itself, and then walk the array after the loop and apply the trim and ] to the end.

kdalts
02-01-2012, 05:51 PM
Hey


The very last problem is still the last ',' for the last entry.

Current output looks like this...

[new Date(2011, 1),4, undefined, undefined,2, undefined, undefined,3, undefined, undefined],
[new Date(2011, 2),1, undefined, undefined,2, undefined, undefined,2, undefined, undefined,] <--need to remove ',' before the ']'

I get the idea of adding the final ']' outside the loop... fixed this by simply concatenation to the final output - thanks for the idea.


echo $sResults . "]";<--perfect

Just need to remove that final comma from the last row somehow. I understand (I think) your code but
if (count($aResult) > 0)
I think the 'if' is being ignored because $aResult is not numeric... it's a string at this stage... or am I missing something?

kdalts
02-01-2012, 06:08 PM
Almost there

Fou-Lu
02-01-2012, 08:13 PM
The if isn't checking for an integer comparison, its checking the count of items within the array. If its 0, it will generate an error since it will try fetching from a non existing offset.
You'll need to post the code you have now.

kdalts
02-01-2012, 08:26 PM
I got it:eek:

Many many thanks to Fou-Lu... I could not have got there without you. You rock man:thumbsup:

$iLastTime = $dt; // Change the last time to the new time
$aResults[$dt] = sprintf('<br>[new Date(%d, %d)', date('Y', $dt), date('m', $dt));

}

$aResults[$dt] .= sprintf('%d');
//echo $aResults[$dt];
$aResults[$dt] .= ", " . $row['Count(SERVICEORDERID)'] . ", undefined, undefined";

Basically, Just moved the ',' in front of the value instead of at the end of the line: :D

Thanks again

kdalts
02-02-2012, 12:01 PM
Oh dear... it's all gone pear shaped.

Help Fou-Lu:eek:

I plugged in the code to the full data and everything appeared to work well until I tested some of the data values.

Seems the dates are confused. Apr has become Mar and Oct is missing.

So, I went back to the test2 DB and added a new month for Apr (we were only testing up to Mar).

The dates that are returned are -


1293840000
1296518400
1298937600
1301612400


However, both 1298937600 and 1301612400 appear to be Mar so my output looks like this.


[new Date(2011, 1), 4, undefined, undefined, 2, undefined, undefined, 3, undefined, undefined],
[new Date(2011, 2), 1, undefined, undefined, 2, undefined, undefined, 2, undefined, undefined],
[new Date(2011, 3), 4, undefined, undefined, 4, undefined, undefined, 3, undefined, undefined],
[new Date(2011, 3), 1, undefined, undefined, 1, undefined, undefined, 1, undefined, undefined]

Now I am completely confused:confused:

Fou-Lu
02-02-2012, 03:45 PM
What exactly to the entries in the Month property look like?

kdalts
02-02-2012, 05:24 PM
Sorry Fou-Lu... you lost me there?

The month in the database is formated "01-Jan-11" through to "01-Apr-11"

When converted to UNIX_TIMESTAMP(STR_TO_DATE(Month, '%d-%M-%y') they become
1293840000
1296518400
1298937600
1301612400

is that what you mean?

Fou-Lu
02-02-2012, 06:49 PM
Yep. I'll double check when I get home, but from looking at this the format is wrong. %M in MySQL format is the full month; that should be %b for the abbreviated month. The api indicates that a bad format results in 0 though, so that definitely isn't the case here.

Fou-Lu
02-02-2012, 07:39 PM
Okay, so I see that MySQL is definitely lenient regarding %b and %M. It will accept the %b format of a %M.
I polled mine off of UTC format, but your timezone isn't correct for the last insertion record. This is what I retrieve:


mysql> select UNIX_TIMESTAMP(STR_TO_DATE('01-Jan-11', '%d-%M-%y')), UNIX_TIMESTAMP(STR_TO_DATE('01-Feb-11', '%d-%M-%y'))
, UNIX_TIMESTAMP(STR_TO_DATE('01-Mar-11', '%d-%M-%y')), UNIX_TIMESTAMP(STR_TO_DATE('01-Apr-11', '%d-%M-%y')) \G
*************************** 1. row ***************************
UNIX_TIMESTAMP(STR_TO_DATE('01-Jan-11', '%d-%M-%y')): 1293840000
UNIX_TIMESTAMP(STR_TO_DATE('01-Feb-11', '%d-%M-%y')): 1296518400
UNIX_TIMESTAMP(STR_TO_DATE('01-Mar-11', '%d-%M-%y')): 1298937600
UNIX_TIMESTAMP(STR_TO_DATE('01-Apr-11', '%d-%M-%y')): 1301616000

All match except my April, which results in 3600 more or 60 minutes. That rolls your date to 11PM the night before. I don't know why this result is in a different timezone then the rest.
You can try setting the timezone with date_default_timezone_set('UTC');, but in all honesty I don't think that will make a difference. The problem is your timezone has changed for only the last entry. Given these are strings parsed at runtime I can't see how this is even possible.

kdalts
02-02-2012, 08:23 PM
Ok.. so I can fudge this I guess. The date is only the date that the report is extracted... thats why they are all 1st of the month. I can simply add 1 day to each value and then run the script...

Thanks for checking it out.

Fou-Lu
02-02-2012, 09:09 PM
That's the problem, I'm not really certain why it is doing this. These are just strings, so its impossible for it to be an insertion issue regarding timestamps (and I'm quite sure thats a non issue anyway as its always in UTC), and when all records are pulled at the same time it doesn't make sense that only the last one is an hour behind. If every record were an hour behind, that would be fine.
For this, I have no explanation. What are the results if you plug this into your query browser / sql client or PHPMyAdmin query tool:


select UNIX_TIMESTAMP(STR_TO_DATE('01-Jan-11', '%d-%M-%y')), UNIX_TIMESTAMP(STR_TO_DATE('01-Feb-11', '%d-%M-%y'))
, UNIX_TIMESTAMP(STR_TO_DATE('01-Mar-11', '%d-%M-%y')), UNIX_TIMESTAMP(STR_TO_DATE('01-Apr-11', '%d-%M-%y')) \G

If they match mine (if all are different your timezone is different, execute SET time_zone='+0:00' first), execute this query:


SELECT `Month`, UNIX_TIMESTAMP(STR_TO_DATE(`Month`, '%d-%M-%y')) FROM test2

and post that result.

kdalts
02-16-2012, 12:40 PM
Well everything was working out fine... but now the whole project has crashed:(

I have now plugged in over 2 years of data and discovered that there are a few inconsistencies in the base data that cause my trend chart to fail - it simply does not draw.

There reason it does not draw is because it needs a consistent number of values for each trend line. For example, if there are 8 partners and 24 months then I need 24 values for 8 partners. If, in any one month, a partner value is missing then the trend line breaks and fails to draw the chart.

I need to go back to the drawingboard and start again.

If anyone has any thoughts on how best to approach this I would be delighted to here from you.

Here is the high-level project objective:

To create a metrics dashboard using google charts to create -

Monthly statistics
Trends


The chart for trends is here > http://code.google.com/apis/chart/interactive/docs/gallery/annotatedtimeline.html#Configuration_Options

Database = MySQl
Language = PhP

Dataset:
Partners, total dispatches by partner, total types of dispatches by partner, month of dispatch

Challenges:
1/ The data is an extract from a corporate SAP system - this cannot be modified
2/ There number of partners per each month is not consistent
3/ The number of types of dispatches irregular ie: 1 partner for 1 month may not have any dispatch by type.

For the trend line, which appears to be my main challenge, zero is acceptable but no value is not. Make sense?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum