PHP / MySQL Help

[joejoebiggs]

Hello,

I am in real need of some help. My table (responses) is laid out like:

id - repsonse id

emp_num - employee number

ans1 - rating 1-10

dtaken - date submitted

wtaken - week of the year submitted

 

I am trying to poll the database to return surveys answered during a given date, then I need it to give me the average rating for each week during that given period.

 

Any insight into this would be very helpful as I am pulling my hair out!

[killerb]

Dunno if this is the easiest or most elegant but it will achieve your requirements. You may need to tweak for performance on large data sets. This is untested, written quick for demonstration of how I have solved this sort of problem before:

 

<?php
$query = "select * from .. where dtaken >= :start_date and dtaken <= :end_date order by wtaken ASC, dtaken ASC"
$bind = array('start_date'=>$start_date, 'end_date'=>$end_date);
$rows = $DB->fetchAll($query, $bind);
$week_stats = array();
foreach($rows as $row){
    if(!array_key_exists($row['wtaken'], $week_stats)){
        $week_stats[$row['wtaken'] = array(
            'total' => 0,
            'num' => 0
        );
    }
    // store the total of all averages for calculating averages
    $week_stats[$row['wtaken']]['total'] += $row['ans1'];

    // count number of answers for calculating averages
    $week_stats[$row['wtaken']]['num']++;
}

// calculate the averages
foreach($week_stats as $stat){
    $week_stats['average'] = $stat['total']/$stat['num'];
}
var_dump($week_stats);

[joejoebiggs]

Thanks for the help, however, when I try running this I get am HTTP 500 error. Any ideas?

[fenway]

Turn on php errors.

[roopurt18]

Your wtaken column is redundant as you can always calculate the week of the year based off your dtaken column.

 

This might work:

<?php
$start_tm = date( 'Y-m-d H:i:s', strtotime( '6/1/2009' ) );
$end_tm = date( 'Y-m-d H:i:s', strtotime( '1/1/2010' ) );
$select_stmt = "
    SELECT
        r.`id`,
        r.`emp_num`,
        r.`ans1`,
        r.`dtaken`,
        r.`wtaken`
    FROM `responses` AS `r`
    INNER JOIN (
        SELECT
            DATE_FORMAT( `dtaken`, '%U' ) AS `week_of_year`,
            COUNT(*) AS `number_of_responses`,
            AVG( ans1 ) AS `average_rating`
        FROM `responses`
        WHERE `dtaken` BETWEEN '{$start_tm}' AND '{$end_tm}'
        GROUP BY DATE_FORMAT( `dtaken`, '%U' )
    ) AS `s` ON DATE_FORMAT( r.`dtaken`, '%U' )=s.`week_of_year`
    WHERE r.`dtaken` BETWEEN '{$start_tm}' AND '{$end_tm}'
";
$q = mysql_query( $select_stmt );
if( ! $q ) throw new Exception( 'Select error: ' . mysql_error() );
?>

[joejoebiggs]

killerb, I get the error

Parse error: syntax error, unexpected T_VARIABLE in /var/www/html/results/date_rpt.php on line 8

 

Here is my full code.

<?
// Make a MySQL Connection
mysql_connect("localhost", "******", "*******") or die(mysql_error());
mysql_select_db("survey") or die(mysql_error());
$start_date = $_GET['date_st'];
$end_date = $_GET['date_e'];
$query = "select * from .. where dtaken >= :start_date and dtaken <= :end_date order by wtaken ASC, dtaken ASC"
$bind = array('start_date'=>$start_date, 'end_date'=>$end_date);
$rows = $DB->fetchAll($query, $bind);
$week_stats = array();
foreach($rows as $row){
    if(!array_key_exists($row['wtaken'], $week_stats)){
        $week_stats[$row['wtaken'] = array(
            'total' => 0,
            'num' => 0
        );
    }
    // store the total of all averages for calculating averages
    $week_stats[$row['wtaken']]['total'] += $row['ans1'];

    // count number of answers for calculating averages
    $week_stats[$row['wtaken']]['num']++;
}

// calculate the averages
foreach($week_stats as $stat){
    $week_stats['average'] = $stat['total']/$stat['num'];
}
var_dump($week_stats);
?>

[killerb]

Parse errors mean the code is invalid PHP format, something is missing and it invalidates the PHP code so it cannot be interpreted:

 

Needs a semicolon after , dtaken ASC"

 

Needs a right square bracket after $week_stats[$row['wtaken']

[joejoebiggs]

Thanks for the update. I am now getting an unexpected T_IF on line 14. Any ideas?

[killerb]

No can't help without getting into debugging (process of identifying cause of problem), therefore chargeable work. Sorry, not taking on small jobs presently.

[Kryptix]

Thanks for the update. I am now getting an unexpected T_IF on line 14. Any ideas?

This is nothing to do with MySQL.

[joejoebiggs]

Thanks for the update. I am now getting an unexpected T_IF on line 14. Any ideas?

This is nothing to do with MySQL.

1. Thanks for the helpful insight

2. This was moved here by a mod, so if you don't like it here, go talk to them.

[roopurt18]

Did you figure out what was causing the parse error?

[joejoebiggs]

Not yet...

[fenway]

If you're still having trouble, this thread really should be in the PHP Coding Help forum... let's wait and see.