How to have multiple replies in posting app?

Question

Hi, this is a simple question.

With PHP and MySQL, I'm trying to build a bug reporting application.

Currently, the user can post bugs and a list of bugs is portrayed to the technician/administrator.

How do I make it so that many technician or administrators can reply to the report (thread?)

As in both mysql table layout and PHP coding?

The current MySQL table has layout of:

id          (Bug ID)
by          (person reporting it)
title       (Report Title)
content     (Contents of Report)
datetime    (Date/Time of report)
application (Application/page where problems happens)
priority    (Priority)
assigned    (Assigned To)
status      (Status of report/bug)

So no response column yet, but how do I achieve multiple post/responses with PHP and MySQLi?

Thanks

Answer 1

This would be a many-to-one relationship. You can either have:

response table

id (response id)
bugid (bug id)
columns related to the response

or

response table

id (response id)
columns related to the response

with

bugresponse table

responseid (response id)
bugid (bug id)
columns related to the bug-response relationship

where the second design can also handle many-to-many relationship (unlikely to be necessary in this case) and can also has some other benefits depending on your requirements.

Answer 2

You make another table with the responses. For instance with the layout

id (Response Id), 
responseTo (id of the Bug this is a response to),
by (person responding),
content (Contents of Response)

Where responeTo is the crucial field. Then when you want to view all the response to a bug you just select from the response table where responseTo = currentBugId.

Answer 3

What you usually do is you create a separate table for the responses. In that table you have one field that "points" to the first table. It could look like this:

TABLE responses
id         (Unique id)
bug_id     ("Pointer" to which bug this response "belongs to")
body       (Contents of response)

This way you can have many responses which all point back to one bug, and thus you have virtually created an "ownership" or "relation". It is customary to call a relation like the one above a "one to many" if we pretend we're in the bugs table, looking out. (And a "many to one" of we're in the responses table, looking back at the bugs table.)

Then, in PHP, when you want to retrieve all the responses belonging to a bug, you do something like this: (pseudo code)

$bug = SELECT * FROM bugs WHERE id = $some_id
$resps = SELECT * FROM responses WHERE bug_id = $bug['id'] ORDER BY created_at

Voilá! Now you have the bug and all of its responses, ordered by creation date. When you insert new responses you need to set bug_id to the appropriate value, of course.

Cheers!

Answer 4

Might I suggest you check out Eventum (http://eventum.mysql.org/wiki/index.php/Main_Page) instead of building your own? Eventum is PHP/MySQL powered bug tracking software that is quite powerful, and is a lot easier than building your own. I considered building one myself, but quickly realized adding all the useful features any good bug tracker has would take eons of extra work.