Graphing data

[doddsey_65]

Im trying to get some data from the database so i can add it to a graph. The graph points are generated via $datay which is an array built via the database query. The query pulls all the info from the topics table, if the month of which the topic was created changed it adds a new point to the graph with a value of the amount of topics within the month.

 

Based on my database values:

 

$datay[0] should be 1

$datay[1] should be 4

$datay[2] should be 5

 

$datay = array();
$datax = array();
$db = db_pdo::$instance;

$sql = "SELECT * FROM ".TBL_PREFIX."topics
        ORDER BY t_time_posted DESC";
    
$sth = $db->query($sql);

$row = $sth->fetchAll();
$count = 0;
foreach($row as $key => $val)
{
    $date[$key] = date('m', $row[$key]['t_time_posted']);

    if(date('m', $row[$key]['t_time_posted']) != $date[$key-1])
    {
        $count++;
        $datay[] = $count;
        $count = $key;
    }

    $_count = count($row)-$count;

    $datay[0] = $_count;
}    

 

unfortunatly i am not getting those results. instead i get

 

$datay[0] = 1

$datay[1] = 1

$datay[2] = 6

 

This means i should have 8 topics in total, but i have 10.

 

Anyone have any ideas? Its nothing to do with the graph, just the way i am adding data to the datay array.

 

Thanks

 

[phpSensei]

This script doesn't make sense.

[doddsey_65]

thanks for the helpful comment

[phpSensei]

so you can't see what so redundant with this part of your script?

 

$date[$key] = date('m', $row[$key]['t_time_posted']);

    if(date('m', $row[$key]['t_time_posted']) != $date[$key-1])

[doddsey_65]

not really thats why im asking for help, ive spent hours on this now, ive tried to leave it and move on but i cant, its bugging me too much. The code you pointed out means if the date changes then run the code below.

[phpSensei]

Yeah but your just grabbing the first row of t_time_posted and comparing it against every other row's in your sql table. Are you comparing it to the current month? Or is that your goal here...

[doddsey_65]

the goal is to select all topics grouped by the month they were posted in then take the amount in the month and add it to the datay array

[phpSensei]

Sounds good, can you post the a screen shot of your sql table? I understand what you are trying to do, but I can type a better solution for you.

[doddsey_65]

sure, here it is. hope you can help.

 

[attachment deleted by admin]

[phpSensei]

Alright, the first thing I would do is change the sql query to this

 

$sql = "SELECT * FROM ".TBL_PREFIX."topics
        GROUP BY YEAR (t_time_posted) , MONTH(t_time_posted)";

 

because all you have is timestamps..

 

Now are you comparing each row based on what given date?

[doddsey_65]

just the last 6 months.

 

wouldnt i need to use FROM_UNIXTIME(t_time_posted)) since the dates are stored as UNIX timestamps?

[phpSensei]

You know what use this example or you can do the from_unix, then you can grab the date format however you want in the loop

 

select DATE_FORMAT(t_time_posted,"%Y-%m") as created_month FROM ".TBL_PREFIX."topics GROUP BY created_month

 

Here's another method other from mine

 

http://www.epochconverter.com/programming/mysql-from-unixtime.php

[doddsey_65]

thanks for the help so far, ive used this:

 

$sql = "SELECT COUNT(*), t_time_posted FROM ".TBL_PREFIX."topics
        GROUP BY MONTH(FROM_UNIXTIME(t_time_posted))";
    
$sth = $db->query($sql);

$row = $sth->fetchAll();
$count = 0;
foreach($row as $key => $val)
{
    $datay[] = $row[$key]['COUNT(*)'];
    //$datax[] = date('M',$row[$key]['t_time_posted']);
    // should have 1 in may, 4 in july and 5 in august
}    

 

which works in the graph, but since im only getting dates from the time_posted, the months which have 0 topics are missed out. How would i go about including them?

 

 

[phpSensei]

Are they coming out blank, or just not being included overall?

[doddsey_65]

They arent being included. Even if there were no topics within a month i still want to display that month. For example the data i have so far is:

 

May: 1 topic

July: 4 topics

Aug: 5 topics

 

But i want to include June, march and april as this will make up 6 months back from the current month.

 

[phpSensei]

do a mysql_num_rows and see if it prints out more than 3, because we aren't filtering out any data in the sql....

 

can you show me the code where you are printing these out?

[doddsey_65]

It prints out 3, because the topics in the database only span 3 months(may, july and august)

 

Im thinking i may have to do somthing like a for loop to fill in the blanks but thats where im stuck

 

$sql = "SELECT COUNT(*), t_time_posted FROM ".TBL_PREFIX."topics
        GROUP BY MONTH(FROM_UNIXTIME(t_time_posted))";
    
$sth = $db->query($sql);

$row = $sth->fetchAll();
$count = 0;
foreach($row as $key => $val)
{
    $datay[$key] = $row[$key]['COUNT(*)'];
    $datax[$key] = date('M',$row[$key]['t_time_posted']);
    echo $key;
    // should have 1 in may, 4 in july and 5 in august
}    

[phpSensei]

Okay I see now, yeah its almost like filling in a calendar's blanks. You need to count the number of blanks inbetween, and fill them in with a loop.

[doddsey_65]

and thats where im stuck

[phpSensei]

Shouldn't be too hard, just check if JUNE exists in the SQL table, if not print "June: 0 Topics"

[doddsey_65]

Thanks for all your hard work, ive got it working now with:

 

$sql = "SELECT COUNT(*), t_time_posted FROM ".TBL_PREFIX."topics
        GROUP BY MONTH(FROM_UNIXTIME(t_time_posted))";
    
$sth = $db->query($sql);

$row = $sth->fetchAll();
$count = 0;
foreach($row as $key => $val)
{
    $datay[date('n',$row[$key]['t_time_posted'])-1] = $row[$key]['COUNT(*)'];
    $datax[] = date('M',$row[$key]['t_time_posted']);
    
    // should have 1 in may, 4 in july and 5 in august
}    

for($i=1; $i<=12; $i++)
{
    if(empty($datay[$i]))
    {
        $datay[$i] = 0;
    }
}

ksort($datay);

//dump($datay);

foreach($datay as $key => $val)
{
    $labels[] = date("M", mktime(0, 0, 0, ($key)));
}

 

[phpSensei]

No Problem man, glad you got it working.

 

Mark as solved <3