SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Apr 2003
    Posts
    332
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query gives incomplete results

    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:
    Code:
    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:
    Code:
    <?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. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I don't know if this works, but maybe it will help.

    PHP Code:
    $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. #4
    SitePoint Addict
    Join Date
    Apr 2003
    Posts
    332
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    $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. #5
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?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>";

    ?>
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  6. #6
    SitePoint Addict
    Join Date
    Apr 2003
    Posts
    332
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    SitePoint Addict
    Join Date
    Apr 2003
    Posts
    332
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •