Query gives incomplete results

Databases
1

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>";
?>

produces:
2011-05-31 Tuesday Staff 31
2011-06-01 Wednesday Staff 31
2011-06-02 Thursday Staff 31
2011-06-03 Friday Mbr 107
Staff 31

I am lost trying to get the number of reported=1 and the percentage into the report. Thanks,

2

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.

3

I don’t know if this works, but maybe it will help.


$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) {

  echo "<tr><td class='date'>" . $date . "</td><td class='weekday'>" . $array[0]['dayofweek']. "</td>";

  $combined =0;
  foreach ($array as $single) {
    echo "<td class='source'>" . $sourcecodes[$single['source']] . "</td><td class='count'>" . $single['reported'] . "</td></tr>";
    $combined += $single['reported'];
  }

}

echo "</table>";
4 
$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

Darn close.

Andy

5

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>";

?>
6

Hi,

SQL is returning an error:

#1267 - Illegal mix of collations (latin1_general_ci,IMPLICIT) and (utf8_unicode_ci,COERCIBLE) for operation ‘=’

I eliminated white space around the equal signs but the error remains.

A search of that error gives a myriad of possibilities. Changing the columns to UTF-8 didn’t resolve the error.

Suggestions?

7

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?

8

Hi,

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:

2011-06-03 Friday 0 132
2011-06-02 Thursday 0 28
2011-05-31 Tuesday 0 28
2011-06-01 Wednesday 0 28

The way I have this implemented is inefficient. I will take another look at the table and see what restructuring is necessary.

Thanks for you help, Phoenix.

Andy