Group & collapse elements based on field value

[cmayo]

No clue where would be the best place to post this as I imagine multiple technologies could be involved.

I have PHP script generating a page based on values in a MySQL database, and I can't seem to wrap my head around how to code a feature I want. If someone could give me a nudge in the right direction, I'd sure appreciate it.

The raw output looks something like this:

 Date        Time      Reading
 04/09/2015  06:02PM   40375
 04/09/2015  09:22AM   40365
 04/09/2015  07:06AM   40351
 04/08/2015  11:19AM   40349
 04/05/2015  05:29PM   40283

If I have multiple entries for a date, all but the first line for that date would be output in a hidden div below the first date, and a plus sign (or some other icon) would appear next to the first date that the user can click to display the hidden other lines for that date:

 Date        Time      Reading
+04/09/2015  06:02PM   40375
 04/08/2015  11:19AM   40349
 04/05/2015  05:29PM   40283

After clicking the plus sign to show the hidden 04/09 events, the hidden lines appear and the plus sign changes to a minus which the user can click to re-hide the extra 04/09 events:

 Date        Time      Reading
-04/09/2015  06:02PM   40375
 04/09/2015  09:22AM   40365
 04/09/2015  07:06AM   40351
 04/08/2015  11:19AM   40349
 04/05/2015  05:29PM   40283

One problem (I think) is that I won't know I have multiple entries for a date until I'm looping through the database results, so this all has to be done on the fly, programatically speaking, unless someone knows a cool way to walk the DOM after the page is output and do the grouping/collapsing at that time.

Any thoughts?

 

[cmayo]

Never mind - I managed a kludge.

 

[jpadie]

Care to share?

Easiest seems to be to create a multidimensional object but I am keen to see what you have come up with!

 

[cmayo]

My solution is a bit clumsy but before outputting the data to the browser, I'm putting the database results into an associative array keyed on the record date. That way, as I'm outputting the data, I know in advance whether I have multiple entries for a date. If so, I output a display toggle link on the first record and output the extra records in a hidden div

$rowdata
Array
(
    [2015-04-09] => Array
        (
            [0] => Array
                (
                    [reading_id] => 1638
                    [reading_date] => 2015-04-09
                )

            [1] => Array
                (
                    [reading_id] => 1637
                    [reading_date] => 2015-04-09
                )

            [2] => Array
                (
                    [reading_id] => 1636
                    [reading_date] => 2015-04-09
                )

        )

    [2015-04-08] => Array
        (
            [0] => Array
                (
                    [reading_id] => 1630
                    [reading_date] => 2015-04-08
                )

        )

    [2015-04-05] => Array
        (
            [0] => Array
                (
                    [reading_id] => 1629
                    [reading_date] => 2015-04-05
                )

        )
)

At output time, I loop through each date and ouput the records

while(list($rowdate, $rowdata) = each($disp_row))
{

	// Output first element for each date
	output_reading($rowdata[0], $rowdata[0]['reading_id'], ', count($rowdata));
	
	// If multiple entries for a date
	if(count($rowdata) > 1)
	{
		// Open a hidden div
		print "<div id='{$rowdata[0]['reading_date']}' style='display: none'>\n";
	
		// Print the rest of the entries
		for($i=1;$i<count($rowdata);$i++)
		{
			output_reading($rowdata[$i], $rowdata[0]['reading_id'], '#eee');
		}

		// Close the hidden div
		print "</div>\n";
	}
}

The $count argument flags the output function to create the display toggle for the first record

function output_reading($row, $base_id, $bgcolor, $count=1)
{
    if($count > 1)
    {
        // If multiple entries exist for this date (given in $count), output a link to toggle the 
        // display status for the remaining records
        $expand = "<a style='outline: 0;' href='#' onClick='toggle(\"{$row['reading_date']}\",\"$base_id\"); return false;'><span id='$base_id'><img src='/images/right_arrow.png' width=16 height=16 /></span></a>";
    }
    
    /* Output the row */

}

And Javascript does the rest

<script>
function toggle(the_id, base_id)
{
	if(document.getElementById(the_id).style.display == "none")
	{
		document.getElementById(the_id).style.display = "block";
		document.getElementById(base_id).innerHTML = "<img src='/images/down_arrow.png' />";
	} else {
		document.getElementById(the_id).style.display = "none";
		document.getElementById(base_id).innerHTML = "<img src='/images/right_arrow.png' />";
	}
}
</script>

Inelegant but functional.

 

[jpadie]

i'd do it very similarly in php. that's what I meant by a multidimensional array.

then I'd deliver it as json and let a javascript widget do the DOM interaction.
something like this class would do very well (although it would only take five minutes to write your own anyway).

the easiest way to build the original array is this

$data = array();
while ($row = mysql_fetch_array($result)):
 if(!isset($data[$row[0]]))$data[$row[0]] = array();
 $data[$row[0]][] = $row['reading_id'];
endwhile;
$data = json_encode($data);

$data = "<ul class='collapsible'>";
$readingDate = '';
while ($row = mysql_fetch_array($result)):
 if($readingDate == ''):
    $readingDate = $row['reading_date'];
    $data .= "<li>{$readingDate}<ul class='collapsible'>";
 elseif($readingDate !== $row['readingDate']):
    $readingDate = $row['readingDate'];
    $data .= "</ul><li>{$readingDate}<ul class='collapsible'>";
 endif;
 $data .= "<li>{$row['reading_id']}</li>";
endwhile;
$data .= "</ul></li></ul>

 

[cmayo]

Thanks for that, jpadie! My elective coding time is gone for a couple days, but I'll try your code when I get back to it.