I wish to compose an aggregate report for each day a lunch is served. It should reflect the count by category of number eligible, number served and percentage of participation.
The table:
CREATE TABLE `lunch` (
`date` DATE NOT NULL DEFAULT '0000-00-00',
`name` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'latin1_general_ci',
`source` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`reported` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`date`, `name`)
This query:
<?php
error_reporting(E_ALL);
$rs=mysql_select_db('local');
echo "<table>";
$sql="SELECT DISTINCT(`date`),DAYNAME(date) FROM `lunch` ORDER BY `date`";
$request=mysql_query($sql);
$combined='';
WHILE($row=mysql_fetch_array($request)){
$date=$row['date'];
$DayOfWeek=$row['DAYNAME(date)'];
echo "<tr><td class='date'>" . $date . "</td><td class='weekday'>" . $DayOfWeek . "</td>";
$sql2="SELECT `source`,COUNT(`reported`) FROM lunch
WHERE `date` = '$date'
GROUP BY `source`";
$request2=mysql_query($sql2);
WHILE($row=mysql_fetch_array($request2)){
$reported=$row['COUNT(`reported`)'];
if(($row['source']=='c')){$source="Mbr";}
if(($row['source']=='s')){$source="Staff";}
if(($row['source']=='v')){$source="Vol";}
echo "<td class='source'>" . $source . "</td><td class='count'>" . $reported . "</td></tr>";//<td class='combined'>" . $combined . "</td></tr>";
}//end request2
}//end request
echo "</table>";
?>
Please get rid of all of your PHP code. Post your MySQL code only.
Note too, that if you are running an SQL query inside a loop (application code based) of another query then you are likely needing a JOIN or UNION to produce your results.
$sql = "
SELECT
`date`,
DAYOFWEEK(`date`) AS `dayofweek`,
source,
SUM(reported) AS `reported`
FROM
lunch
GROUP BY
date,
source
ORDER BY
date
";
$result = mysql_query($sql);
$data = array();
while ($row = mysql_fetch_array($result)) {
$data[$row['date']] = array('dayofweek' => $row['dayofweek'], 'source' => $row['source'], 'reported' => $row['reported']);
}
$sourcecodes = array('c' => 'Mbr', 's' => 'Staff', 'v' => 'Vol');
echo "<table>";
foreach ($data as $date => $array) {
//line 32 follows
echo "<tr><td class='date'>" . $date . "</td><td class='weekday'>" . $array[0]['dayofweek']. "</td>";
$combined =0;
foreach ($array as $single) {
//line 36 follows
echo "<td class='source'>" . $sourcecodes[$single['source']] . "</td><td class='count'>" . $single['reported'] . "</td></tr>";
$combined += $single['reported'];
}
}
echo "</table>";
Results are:
Notice: Undefined index: 3 on line 36
Notice: Undefined index: 2 on line 36
Notice: Undefined offset: 0 on line 32
Notice: Undefined index: 4 on line 36
Notice: Undefined index: 2 on line 36
Notice: Undefined offset: 0 on line 32
Notice: Undefined index: 5 on line 36
Notice: Undefined index: 2 on line 36
Notice: Undefined offset: 0 on line 32
Notice: Undefined index: 6 on line 36
Notice: Undefined index: 2 i on line 36
2011-05-31 3
Staff s
2
2011-06-01 4
Staff s
2
2011-06-02 5
Staff s
2
2011-06-03 6
Staff s
2
What beautiful code!
Ran the query alone and returned:
date dayofweek source reported
2011-05-31 3 s 28
2011-06-01 4 s 28
2011-06-02 5 s 28
2011-06-03 6 c 104
2011-06-03 6 s 28
Please run the query on it’s own against the table, the contents of the WHERE clause may well belong in a HAVING clause (I’m fairly new to using GROUP BY clauses myself). Please note the below has not been tested
<?php
error_reporting(E_ALL);
// connection to database goes here
$sql="
SELECT
date AS lunch_date
, DAYNAME(`date`) AS lunch_day
, source = CASE
WHEN source = 'c' THEN 'Mbr'
WHEN source = 's' THEN 'Staff'
ELSE 'Vol'
END AS lunch_source
, COUNT(source) AS num_lunches
FROM
lunch
WHERE
reported = 1
GROUP BY
lunch_day
, lunch_source
";
$result=mysql_query($sql);
echo "<table>";
while ($row=mysql_fetch_array($result) {
echo " <tr>";
echo " <td>{$row['lunch_date']}</td>";
echo " <td>{$row['lunch_day']}</td>";
echo " <td>{$row['lunch_source']}</td>";
echo " <td>{$row['num_lunches']}</td>";
echo " </tr>";
}
echo "</table>";
?>
hmm, does not give me that error locally when testing the query against an empty table. Was you running the query via the MySQL command line or via phpMyAdmin? If you was running it via your own script, did you use mysql_set_charset() right after establishing the connection to MySQL (and what charset did you use?
The query was entered directly into phpmyadmin and Heidi, both giving the error code above. When I add my database info and run the php, I receive no error. It returns: