[SOLVED] using two tables and I just don't get it
Posted: Thu Aug 18, 2005 11:06 am
Hi, I am learning php and trying to develop my first app. I have gotten quite a bit to work, but not all works the way I want it to...and now I think I took the wrong route on mapping out my database tables. All help is greatly appreciated.
In general, I created two tables in my database, admin_tbl (about primary tasks) and adminticket_tbl (trouble tickets assigned to primary tasks.)
admin_tbl columns:
adminticket_tbl columns:
How I chose to do this project is have them go to the main page, which displays all tasks and within each task, displays all open trouble tickets. On this page, one can make changes to task, and select update or select an a href link to open each trouble ticket, view all open trouble tickets, and add a trouble ticket to that particular task. None of which work right, I will explain after my code.
Code for index.php (just the main code for the table, not the code that connects the database at top, etc.
code for ticketinfo.php
troubleticket.php is just a form, where I want to echo the task_id to add a new trouble ticket.
My main issues:
1. ticketinfo.php is an href link. I only want that ONE particular ticket to that one task to open up...but it lists ALL tickets assigned to ALL tasks. This is where I am confused about the relationships in the database. I know I am doing something terribly wrong, could be that I am using an array??.
2. when I select Add New Ticket (goes to troubleticket.php (also an href link,) the value of that particular task_id is not being passed, I get Resource Id #4 instead.
Is there a better way to map out my database? Is my code whacked?
thanks
feyd | Please use
In general, I created two tables in my database, admin_tbl (about primary tasks) and adminticket_tbl (trouble tickets assigned to primary tasks.)
admin_tbl columns:
Code: Select all
task_number (primary key)
task_id (task name)
date_start
employee_start
date_finished
employee_finish
notesCode: Select all
ticket_id (primary key)
task_id (should somehow be a relationship to task_number from admin_tbl, but I named it the same as task_id from that table...might be a problem?)
ticket_shortname (title of problem)
ticket_date_opened
ticket_date_modified
ticket_date_closed
ticket_assigned_to
ticket_assigned_by
ticket_closed_by
ticket_informationCode for index.php (just the main code for the table, not the code that connects the database at top, etc.
Code: Select all
<?php $query = " SELECT * FROM `admin_tbl`";
$result = mysql_query($query);
if($result)
{
while ( $task = mysql_fetch_array($result) )
{
$task_number=$task['task_number'];
$task_id=$task['task_id'];
$date_start=$task['date_start'];
$employee_start=$task['employee_start'];
$date_finish=$task['date_finish'];
$employee_finish=$task['employee_finish'];
$notes=$task['notes'];
?>
<form action="update.php" method="post">
<input type="hidden" name="task_id" value="<?php echo $task_id; ?>">
<table width="100%" border="0" cellspacing="0" cellpadding="5">
<tr>
<td width="12%" bgcolor="#666666" class="pdheaderwhite">File <? echo $task_number; ?>:</td>
<td width="27%" bgcolor="#666666" class="pdheaderwhite"><? echo $task_id; ?></td>
<td width="61%" bgcolor="#CCCCCC" class="pdheader">Trouble Tickets</td>
</tr>
<tr>
<td bgcolor="#FFEDD4" class="pdtext">Date Started:</td>
<td bgcolor="#FFEDD4"><input type="text" name="date_start" value="<? echo $date_start; ?>"></td>
<td rowspan="5" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="3" class="pdtext">
<tr>
<td><strong>Title</strong>
<td><strong>Assigned To:</strong></td>
<td><strong>Date Opened</strong></td>
</tr>
<? $sql = "SELECT ticket_id, ticket_shortname, ticket_assigned_to, ticket_date_opened FROM adminticket_tbl WHERE task_id = '$task_id' AND ticket_date_closed IS NULL";
$res = mysql_query($sql) or die (mysql_error());
if($res)
{
while (list($task_id, $name, $assign, $date) = mysql_fetch_row($res))
{
echo"<tr><td><a href='ticketinfo.php?ticket_id=$task_id'>$name</a>
<td>$assign</td>
<td>$date</td></tr>";
}
} ?>
</table>
<br>
<table width="100%" border="0" cellspacing="0" cellpadding="3">
<tr>
<td class="pdtext"><p><?php echo "<a href='troubleticket.php'>Add New ticket </A>";?> </p>
<p>View All Open Tickets</p>
</td>
</tr>
</table>
<p> </p></td>
</tr>
<tr>
<td bgcolor="#FFEDD4" class="pdtext">Employee Responsible:</td>
<td bgcolor="#FFEDD4"><input type="text" name="employee_start" value="<? echo $employee_start; ?>"></td>
</tr>
<tr>
<td bgcolor="#FFEDD4" class="pdtext">Date Completed</td>
<td bgcolor="#FFEDD4"><input type="text" name="date_finish" value="<? echo $date_finish; ?>"></td>
</tr>
<tr>
<td bgcolor="#FFEDD4" class="pdtext">Employee Resonsible:</td>
<td bgcolor="#FFEDD4"><input type="text" name="employee_finish" value="<? echo $employee_finish; ?>"></td>
</tr>
<tr>
<td bgcolor="#FFEDD4" class="pdheader"> </td>
<td bgcolor="#FFEDD4"> </td>
</tr>
<tr>
<td bgcolor="#FFEDD4" class="pdtext">Notes:</td>
<td colspan="2" bgcolor="#FFEDD4"><p>
<textarea name="notes" cols="60" rows="5"><? echo $notes; ?></textarea>
</p>
</td>
</tr>
<tr>
<td bgcolor="#FFEDD4" class="pdheader"> </td>
<td bgcolor="#FFEDD4"><input name="Submit" type="Submit" value="Update"></td>
<td bgcolor="#FFEDD4"> </td>
</tr>
<tr>
<td class="pdheader"> </td>
<td> </td>
<td> </td>
</tr>
</table>
</form>
<?
}
}
?>Code: Select all
<?php $query = " SELECT * FROM `adminticket_tbl`";
$result = mysql_query($query);
if($result)
{
while ( $task = mysql_fetch_array($result) )
{
$ticket_id=$task['ticket_id'];
$task_id=$task['task_id'];
$ticket_status=$task['ticket_status'];
$ticket_shortname=$task['ticket_shortname'];
$ticket_date_opened=$task['ticket_date_opened'];
$ticket_date_modified=$task['ticket_date_modified'];
$ticket_date_closed=$task['ticket_date_closed'];
$ticket_assigned_to=$task['ticket_assigned_to'];
$ticket_assigned_by=$task['ticket_assigned_by'];
$ticket_closed_by=$task['ticket_closed_by'];
$ticket_information=$task['ticket_information'];
?>
<form action="maketicket.php" method="post">
<input type="hidden" name="task_id" value="<?php echo $task_id; ?>">
<input type="hidden" name="ticket_status" value="open">
<table width="100%" border="0" cellspacing="0" cellpadding="5">
<tr>
<td width="24%" class="pdheader">File:</td>
<td width="76%" class="pdheader"><? echo $task_id; ?></td>
</tr>
<tr>
<td class="pdtext">Title:</td>
<td><input type="text" name="date_start" value="<? echo $ticket_shortname; ?>"></td>
</tr>
<tr>
<td class="pdtext">Assigned by:</td>
<td><input type="text" name="employee_start" value="<? echo $ticket_assigned_by; ?>"></td>
</tr>
<tr>
<td class="pdtext">Assigned to:</td>
<td><input type="text" name="date_finish" value="<? echo $ticket_assigned_to; ?>"></td>
</tr>
<tr>
<td class="pdtext">Date:</td>
<td><input type="text" name="employee_finish" value="<? echo $ticket_date_start; ?>"></td>
</tr>
<tr>
<td class="pdheader"> </td>
<td> </td>
</tr>
<tr>
<td class="pdtext">Notes:</td>
<td><p>
<textarea name="notes" cols="60" rows="5"><? echo $ticket_information; ?></textarea>
</p>
</td>
</tr>
<tr>
<td class="pdheader"> </td>
<td><input name="Submit" type="Submit" value="Update"></td>
</tr>
<tr>
<td class="pdheader"> </td>
<td> </td>
</tr>
</table>
</form>
<?
}
}
?>My main issues:
1. ticketinfo.php is an href link. I only want that ONE particular ticket to that one task to open up...but it lists ALL tickets assigned to ALL tasks. This is where I am confused about the relationships in the database. I know I am doing something terribly wrong, could be that I am using an array??.
2. when I select Add New Ticket (goes to troubleticket.php (also an href link,) the value of that particular task_id is not being passed, I get Resource Id #4 instead.
Is there a better way to map out my database? Is my code whacked?
thanks
feyd | Please use
Code: Select all
andCode: Select all
tags where approriate when posting code. Read [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]