Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The comments must have a record of the ticket ID for them to to be related so you should be able to grab the ID from the ticket and use a JOIN to grab the comments.</p> <p>I am doing something very similar with a Wordpress DB so I can only go by that layout. In wordpress the post has an ID which relates to the comment.</p> <p>So you should be able to do it with one query I think. That is if the design of the tables allows for this type of association – which it should.</p> <pre><code>SELECT tickets.ID, tickets.title, tickets.content, comments.content FROM tickets INNER JOIN comments ON comments.ticket_id = tickets.ID WHERE tickets.content LIKE '%the search term%' ORDER BY comments.comment_date </code></pre> <p>I only have a functional grasp of SQL so other guys on here may correct me in the right way to do this but I think it should work. The actual search element of this query is just for the example I don't know what it is you are actually searching.</p> <p>Revised:</p> <pre><code>$query = "SELECT tickets.ID ticket_id, tickets.title title, tickets.content content FROM tickets WHERE tickets.content LIKE '%the search term%' AND tickets.title LIKE '%the search term%'"; $tickets = $db-&gt;fetch_array($query); foreach ($tickets as $ticket) { echo "&lt;h1&gt;$ticket['title']&lt;/h1&gt;"; echo "&lt;h1&gt;$ticket['content']&lt;/h1&gt;"; $query = "SELECT comments.title title, comments.content content FROM comments WHERE comments.ID = {$ticket['ticket_id']} ORDER BY comments.date DESC LIMIT 5"; $comments = $db-&gt;fetch_array($query); if ($comments) { echo "&lt;div class="comments"&gt;"; foreach($comments as $comment) { echo "&lt;div class="comment"&gt;"; echo "&lt;h1&gt;$comment['title']&lt;/h1&gt;"; echo "&lt;h1&gt;$comment['content']&lt;/h1&gt;"; echo "&lt;/div&gt;"; } echo "&lt;/div&gt;"; } } </code></pre>
    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. USChris Rymer
    1. USChris Rymer
    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
    1. COJust realised I am pulling the ticket multiple times for no reason... Oliver has the right idea, break the query up by getting the ticket first and then grab the comments using the tickets ID. Unless you need to search the content of the comments too!
      singulars
      1. PO
      1. USChris Rymer
    2. COIf you do that it will only show 1 comments.content field, containing just one comment
      singulars
      1. PO
      1. USAlex
    3. COThe first query is wrong really I have just left it in to show the workings out and my misguided logic.
      singulars
      1. PO
      1. USChris Rymer
 

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