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
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!
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.
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.