Missing rows when using rowGrouping

jsbenson

I'm getting unexpected results when trying to group a fairly easy query.

Here's a link and how to get to the section:
http://vfwtest.byethost7.com/

Log in as "johndoe@gmail.com" pw-"password"
On the login window at the bottom, click the "CSR" button
The second tab (Summary) is the culprit.

Here's my SQL ("table_id" is used as my <tr> row id and is not part of the result set):

SELECT
    C.table_id,
    CONCAT(T.letter,'. ',T.service_type,
        CASE
            WHEN T.description IS NOT NULL THEN CONCAT(': ',T.description)
            ELSE ''
        END) AS type,
    P.program,
    C.members,
    C.hours,
    C.mileage,
    C.exp_don,
    C.`desc`,
    DATE_FORMAT(C.entry_date,'%c/%d/%Y') AS entry_date,
    C.`status`
FROM csr C
INNER JOIN lt_csr_type T ON C.type_id = T.table_id
INNER JOIN lt_csr_programs P ON P.csr_type_id = T.table_id AND C.program_id = P.table_id
WHERE C.post_id = 109
ORDER BY T.letter ASC, P.sort ASC;

Here's a call to the php:
http://vfwtest.byethost7.com/php/csr_summary.php?user_id=1877

Here's a screenshot of the results:
https://drive.google.com/file/d/0B9q28uX1UUM1RHgtVFVyLXFFLUk/view?usp=sharing

And here's my initialization:

var oTable=$('#smt_csr_summary_tbl').dataTable({
    "sScrollY": "100%",
    "sScrollX": "100%",
    "sScrollXInner": "100%",
    "bPaginate": false,
    "bScrollCollapse": true,
    "aaSorting": [[1,'asc']],
    "aoColumnDefs":[
                    {sName:"type", aTargets:[0]},
                    {sName:"program",sWidth:"200px",aTargets:[1]},
                    {sName:"members",sWidth:"40px",aTargets:[2]},
                    {sName:"hours",sWidth:"40px",aTargets:[3]},
                    {sName:"mileage",sWidth:"40px",aTargets:[4]},
                    {sName:"exp_don",sWidth:"60px",aTargets:[5]},
                    {sName:"desc",sWidth:"100px",aTargets:[6]},
                    {sName:"entry_date",sWidth:"60px",aTargets:[7]},
                    {sName:"status",sWidth:"40px",aTargets:[8]}
                ]
        
            }).rowGrouping({
                bExpandableGrouping:true
            }).makeEditable({
                sAddDeleteToolbarSelector:'',
                sUpdateURL:$this.options.url,
                oUpdateParameters: { 
                    "action":"update"
                },
                fnOnEdited:function(status){       
                },
                placeholder:"",
            });

I've been fighting with this for a few hours now and can't seem to pin down the issue.

Thanks for any help.

jsbenson

I think I found the issue. Looks like my table isn't being generated correctly in my php file. Here's the table I get...

<table id='smt_csr_summary_tbl' cellpadding='0' border='0' class='display pretty'>
<thead>
  <tr>
    <th class='service_type'>Type</th>
    <th class='program'>Program</th>
    <th class='members'>Members</th>
    <th class='hours'>Hours</th>
    <th class='mileage'>Mileage</th>
    <th class='exp_don'>Exp/Don</th>
    <th class='desc'>Description</th>
    <th class='entryDate'>Entry Date</th>
    <th class='status'>Status</th>
  </tr>
</thead>
<tbody>
  <tr data-table_id='37><td>A. Community Involvement: NEIGHBORHOOD, HIGHWAY, RECYCLING or OTHER</td><td>Organize or Assist in Blood Drive</td><td>10</td><td>10</td><td>100</td><td>1000.00</td><td>test 1</td><td>10/13/2015</td><td>Pending</td></tr><tr data-table_id='41>
    <td>A. Community Involvement: NEIGHBORHOOD, HIGHWAY, RECYCLING or OTHER</td>
    <td>CPR Class</td>
    <td>20</td>
    <td>20</td>
    <td>20</td>
    <td>200.00</td>
    <td>lkjghlkjgh</td>
    <td>10/13/2015</td>
    <td>Pending</td>
  </tr>
  <tr data-table_id='40><td>B. Cooperation With Other Organizations</td><td>Veterans Counsel</td><td>15</td><td>2</td><td>100</td><td>13.00</td><td>mjjhfgkj</td><td>10/13/2015</td><td>Pending</td></tr><tr data-table_id='39>
    <td>B. Cooperation With Other Organizations</td>
    <td>Veterans Counsel</td>
    <td>50</td>
    <td>55</td>
    <td>9888</td>
    <td>2000.00</td>
    <td>oikjghl</td>
    <td>10/13/2015</td>
    <td>Pending</td>
  </tr>
  <tr data-table_id='38><td>B. Cooperation With Other Organizations</td><td>Veterans Counsel</td><td>20</td><td>20</td><td>20</td><td>200.00</td><td>test 2</td><td>10/13/2015</td><td>Pending</td></tr><tr data-table_id='44>
    <td>C. Aid To Others (Rehabilitation): Nursing Home, Hospital, Seniors and Personal/Family Tragedies/Illnesses</td>
    <td>Hospital, Nursing Home Volunteers</td>
    <td>10</td>
    <td>10</td>
    <td>10</td>
    <td>100.00</td>
    <td></td>
    <td>10/15/2015</td>
    <td>Pending</td>
  </tr>
  <tr data-table_id='43><td>C. Aid To Others (Rehabilitation): Nursing Home, Hospital, Seniors and Personal/Family Tragedies/Illnesses</td><td>Hospital, Nursing Home Volunteers</td><td>12</td><td>20</td><td>300</td><td>2000.00</td><td>New test description</td><td>10/14/2015</td><td>Pending</td></tr><tr data-table_id='42>
    <td>C. Aid To Others (Rehabilitation): Nursing Home, Hospital, Seniors and Personal/Family Tragedies/Illnesses</td>
    <td>Operation Uplink / MAP</td>
    <td>555</td>
    <td>200</td>
    <td>464</td>
    <td>4444.00</td>
    <td>oiugkug</td>
    <td>10/13/2015</td>
    <td>Pending</td>
  </tr>
</tbody>
</table>

ThomD

Access to the live site is nice, but what's not working?

tangerine

Your tbody rows are badly formed.

<tr data-table_id='37>

jsbenson

Yep, just found that :-) I've corrected it and now it looks great. I've uploaded the php if you want to take another peek.

jsbenson

Now I get to add the second grouping (Program)