I have a table that displays a list of tasks for a client. Each task has the ability to have multiple notes. Tasks and notes are kept in separate tables, but brought together in this table by a sub-query that does a group_concat to be able to show the many-to-one relationship between a task and the notes for that task. The problem is that lengthy notes do not show the full text. The field in the notes table contains the full text that is needed. It is a blob data type and the data length is 1191 bytes. My research tells me that doing this select with the group_concat makes the returning Note field over 1024 bytes, which is the limit. And indeed the 'group_concat_max_len' is set to 1024. I have been instructed by my boss that rather than increase the data limit, I should figure out how to work within the limit. Can anyone give me some suggestions on how to modify this select statement to allow for the one-to-many statement but not limit the data length?
<div class="portlet light bordered">
<div class="portlet-title">
<div class="col-md-8">
<div class="caption font-dark">
<i class="fa fa-tasks font-dark"></i>
<span class="caption-subject bold uppercase">Task List</span>
</div>
</div>
</div>
<div class="portlet-body">
<table class="table table-striped table-bordered table-hover dt-responsive"
width="100%" id="sample_3" cellspacing="0" width="100%">
<thead>
<tr>
<th class="all">Task Step</th>
<th class="all">Envr</th>
<th class="all">Task Name</th>
<th class="all">Task Description</th>
<th class="none">Notes:</th>
<th class="all">Due Date</th>
<th class="all">Current Status</th>
</tr>
</thead>
<tbody>
<?php
include "../includes/DBConn.php";
$sql = "SELECT ID, FID , TaskCategory, Environment, TaskName, TaskDescription,
CreateDate, Cast(date_format(DueDate, \"%m-%d-%Y\") as char) as DueDate,
CompletedDate, CompletedBy, CurrStatus,
(SELECT GROUP_CONCAT(Cast(date_format(DATE(CreateDate),\"%m-%d-%Y\") as
char), \" <strong>\" , Cast(TIME(CreateDate) as char) , \"</strong> : \" ,
Notes ORDER BY CreateDate ASC SEPARATOR '<br>')
FROM arc.notes B where A.id = B.taskid
) as Notes
FROM arc.task A
WHERE FID = '$FID'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$TaskID = $row['ID'];
$TaskStep = $row['TaskCategory'] . $row['ID'];
$Environment = $row['Environment'];
$TaskName = $row['TaskName'];
$TaskDescription = $row['TaskDescription'];
$Notes = $row['Notes'];
$DueDate = $row['DueDate'];
$CompleteDate = $row['CompletedDate'];
$CurrStatus = $row['CurrStatus'];
?>
<tr>
<td><?php echo "$TaskStep "; if(!empty($Notes)) { echo " <i class=\"fa fa-
sticky-note\"></i>";} ?></td>
<td><?php echo "$Environment"; ?></td>
<td><?php echo "$TaskName"; ?></td>
<td><?php echo "$TaskDescription"; ?></td>
<td><a class="btn btn-circle btn-xs red-mint" data-toggle="modal"
href="#newNote<?php echo$TaskID;?>"><i class="fa fa-plus"></i>Add Note
</a>
<br>
<?php echo $Notes; ?>
</td>
<td><?php echo "$DueDate"; ?></td>
<td><?php switch ($CurrStatus) {
case (0): echo "<strong><a class=\"btn btn-xs btn-primary btn-
block\" data-toggle=\"modal\" href=\"#StatusChange" .
$TaskStep . "\">AWAITING ACTION</strong>";
break;
case (1): echo "<strong><a class=\"btn btn-xs btn-success btn-
block\" data-toggle=\"modal\" href=\"#StatusChange" .
$TaskStep . "\">IN PROGRESS</strong>";
break;
case (2): echo "<strong><a class=\"btn btn-xs grey-gallery btn-
block\" data-toggle=\"modal\" href=\"#StatusChange" .
$TaskStep . "\">COMPLETED</strong>";
break;
default: echo "No Status";
}
?>
</td>
</tbody>
</div>
</div>
</div>
