| Carl J. Hixon 2004-04-21, 2:32 am |
| I am trying to create a webpage that will track issues and responses for
projects. Basically, I'm trying to get organized. I could do this in
Access but really want to learn the php, mysql, html route.
Unfortunately, I need to learn all three at once plus apache etc. I
assure you that I'm working on it. I have a php book, html book, and I
printed the entire MySQL Reference Manual! Oh...and I'm reading them.
Would any of you gurus out there like to comment on my db structure. And
if you are really really nice, show me some code as to how the issue and
response forms could be coded? This is where I am really struggling
right now. Here is my db structure:
+--------------------------+
| Tables_in_IssueDetail |
+--------------------------+
| tblCategory |
| tblIssue |
| tblProject |
| tblResponse |
+--------------------------+
4 rows in set (0.05 sec
Table "tblCategory" is a lookup table that will be populated as I go along.
mysql> describe tblCategory;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| CatID | int(11) | | PRI | NULL | auto_increment |
| Category | text | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
Table "tblIssue" is a table of project issues or problems. <OpenDate> is
the date that the issue is raised to the project team. <CloseDate> close
date is the date the project team considers the issue closed. I plan to
use a select statement for NOT NULL to see what issue are open.
mysql> describe tblIssue;
+-----------+---------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+------------+----------------+
| IssueID | int(11) | | PRI | NULL | auto_increment |
| FKProject | int(11) | | | NULL | |
| IssueDesc | text | YES | | NULL | |
| OpenDate | date | | | 0000-00-00 | |
| CloseDate | date | YES | | NULL | |
+-----------+---------+------+-----+------------+----------------+ 5
rows in set (0.00 sec)
Table "tblProject" will probably be expanded to include a bunch of other
project information such as <BudgetLine>, <ProjManager>, <BudgetAmt>,
<Contingency>, etc...
mysql> describe tblProject;
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| ProjectID | int(11) | | PRI | NULL | auto_increment |
| ProjectName | text | YES | | NULL | |
| ProjectNumber | text | YES | | NULL | |
+---------------+---------+------+-----+---------+----------------+ 3
rows in set (0.03 sec)
Table "tblResponse" just stores the responses to particular issues. This
is a one to many relationship with Issues.
mysql> describe tblResponse;
+-----------+------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+------------+----------------+
| ResponseID| int(11) | | PRI | NULL | auto_increment |
| FKIssue | int(11) | | | NULL | |
| Reply | mediumtext | YES | | NULL | |
| ReplyDate | date | | | 0000-00-00 | |
+-----------+------------+------+-----+------------+----------------+ 4
rows in set (0.01 sec)
Untested Code to Show a list of the issues:
<start code>------------------------------------------------------------
<html>
<head>
<title> Issue Detail Report </title>
</head>
<body>
<?php
// Connect to the database server
$dbcnx = @mysql_connect('localhost', 'foouser', 'foopwd');
if (!$dbcnx){
die( '<p>Unable to connect to the' .
'database server at this time.</p>' );
}
// Select our database --> IssueDetail
if (! @mysql_select_db('IssueDetail') ) {
die( '<p>Unable to locate the IssueDetail ' .
'database at this time.</p>' );
}
?>
//OUTPUT List of Issues
<p>Here are all of the project issues as of
<?php
echo(date("l, F dS Y.") );
?></p>
<blockquote>
<?php
// Request all of the open issues for a given project.
$issues = @mysql_query('SELECT IssueDesc OpenDate FROM tblIssue ' .
'WHERE FKProject=1');
if (!$issues) {
die('<p>Error performing query: ' . mysql_error() .
'</p>');
}
// Display all of the open issues.
while ( $row = mysql_fetch_array($issue) ) {
echo('<li>' . $row['OpenDate IssueDesc'] . '</li>');
}
?>
</blockquote>
</body>
</html>
<Stop Code>------------------------------------------------------
In summary:
(1) How is my db structure?
(2) How might I code data entry forms for html?
(3) How does my code look for displaying a list of issues?
Asking for a LOT here...anything is greatly appreciated.
Thanks,
Carl
|