如何在发布应用程序时有多个回复？

Question

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

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!