Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Ah yes, the typical problem of "set-grouping" that many people tend to encounter...</p> <p>When we talk about <em>joining</em> multiple records (comments) onto one row (event), the only way to reliably(as in accounting for variable amounts of rows) do this using MySQL is to use <code>GROUP_CONCAT()</code> which will group information across one or more rows into a single delimited string.</p> <p>You can also group information into a variable amount of actual <em>columns</em> using a feature called <code>PIVOT</code>, but unfortunately, <code>PIVOT</code> is not available in MySQL.</p> <p>Either way, you will need some application logic (exploding, etc.) to format and display the subset of comments for each ticket.</p> <p>As for the SQL, you can do something like the following:</p> <pre><code>SELECT a.ticket_id, a.ticket_title, a.date_created, SUBSTRING_INDEX(GROUP_CONCAT(CONCAT(LEFT(b.comment_txt, 150), '...', ':::', b.date_posted) ORDER BY b.date_posted DESC SEPARATOR '|||'), '|||', 5) AS comment_list FROM tickets a LEFT JOIN comments b ON a.ticket_id = b.ticket_id WHERE a.ticket_title LIKE '%search_term%' GROUP BY a.ticket_id </code></pre> <p>Everything here is fairly straightforward except for the 4th column in the <code>SELECT</code>... so let's break it down:</p> <p>At the inner-most part, we have <code>CONCAT()</code>. What this does is it concatenates the fields of each comment together, so that you are able to get multiple attributes of each comment (such as the date, the actual text, and perhaps the ID, etc...).</p> <p>After the <code>CONCAT()</code> alone, a single comment might look something like:</p> <pre><code>Lorem Ipsum dolor sit amet consecteur...:::2012-06-21 00:00:00 </code></pre> <p>The <code>:::</code> is one of the delimiters that you would <code>explode()</code> on to separate out each attribute.</p> <p>Moving outwards, the <code>GROUP_CONCAT()</code> then concatenates each <strong><em>row</em></strong> together. At this point, we are basically concatenating concatenations. In addition, the most recent comments appear at the beginning of the string due to <code>ORDER BY b.date_posted</code> within the function.</p> <p>The comment list might look like:</p> <pre><code>Lorem Ipsum dolor sit amet consecteur...:::2012-06-21 00:00:00|||Cras aliquam neque quam, eget facilisis nulla...:::2012-06-18 00:00:00 </code></pre> <p>The <code>|||</code> is the delimiter you would use to separate out each comment.</p> <p>Moving out further, the <code>SUBSTRING_INDEX</code> only selects the first five comments. Since we ordered the comments by most recent first, it's essentially only selecting the five most recent comments in each ticket.</p> <p>Then in your PHP code, you could do roughly:</p> <pre><code>foreach($tickets as $ticket) { // First check if the ticket has comments. Value will be NULL if not. if(!empty($ticket['comment_list'])) { foreach(explode('|||', $ticket['comment_list']) as $comment) { $attributes = explode(':::', $comment); $comment_preview = $attributes[0]; // Get first attribute $date_posted = $attributes[1]; // Get second attribute } } } </code></pre> <p>I use those particular delimiters because commas can be present in fields like the title, etc. and you would not want your script to separate the string in the wrong places. This possibility of erroneous separation is one of the major downsides to using <code>GROUP_CONCAT()</code>, so you must decide which delimiters would be best to use based on the unlikelihood that they would be present within the field values.</p>
    singulars
    1. This table or related slice is empty.
    1. POMySQL: Link multiple records from table B, onto single record of table A in one row
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USZane Bien
    1. USZane Bien
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for you suggest Zane, this is what I was referring to in my question, but thanks for writing it out more clean. Thing is, this ticket system is to be used for interaction between developer and high-level support desks, so the comments are bound to contain pieces of code here and there. I think it's only a matter of time until someone put my delimiter string into a comment. I'm going to wait if someone can come up with a nicer solution, if not I'll accept this answer. Thanks!
      singulars
      1. PO
      1. USAlex
    2. COYou could store your comment text values as Base64, which means there will never be a clash with the delimiters if you use . which is not a valid Base64 character. You could also roll your own encoding which filters out whichever delimiter you are looking at using.
      singulars
      1. PO
      1. USGustav Bertram
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload