Page 1 of 1

[SOLVED] using two tables and I just don't get it

Posted: Thu Aug 18, 2005 11:06 am
by brookie
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:

Code: Select all

task_number (primary key)
task_id (task name)
date_start
employee_start
date_finished
employee_finish
notes
adminticket_tbl columns:

Code: 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_information
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: 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>&nbsp;</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">&nbsp;</td>
        <td bgcolor="#FFEDD4">&nbsp;</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">&nbsp;</td>
        <td bgcolor="#FFEDD4"><input name="Submit" type="Submit" value="Update"></td>
        <td bgcolor="#FFEDD4">&nbsp;</td>
      </tr>
      <tr>
        <td class="pdheader">&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
    </table>
       </form>
<?
}
}
?>
code for ticketinfo.php

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">&nbsp;</td>
        <td>&nbsp;</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">&nbsp;</td>
        <td><input name="Submit" type="Submit" value="Update"></td>
      </tr>
      <tr>
        <td class="pdheader">&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
    </table>
       </form>
<?
}
}
?>
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

Code: Select all

and

Code: 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]

Posted: Thu Aug 18, 2005 4:06 pm
by korto
About the ticketinfo.php
I assume I am not getting all the picture here but since you are doing (php $query = " SELECT * FROM `adminticket_tbl`") isn it expected that it will return all records?

Posted: Thu Aug 18, 2005 4:26 pm
by brookie
yes, but I don't know how to just get that one particular row from the other page. I know I need a mysql_fetch_row, but 'nothing' is being passed from the initial page to the second page (ticketinfo.php) from those href links...I know that first page needs something, but i don't know what and my php book is as useful as a bottle cap right now. I am using $_GET on ticketinfo.php.

Again, I am no php programmer, but just trying to learn.

Posted: Thu Aug 18, 2005 4:40 pm
by korto
//get the stack_id from the url parameter
$stack_id = $_GET['$task_id']

//modify the query accordingly
SELECT * FROM `adminticket_tbl WHERE stack_id = $stack_id

Posted: Fri Aug 19, 2005 11:56 am
by brookie
I got the first part to work. It returns just the one trouble ticket...checked a few of them, and it seems to be working.

In my index.php

Code: Select all

<? $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>";
}
} ?>
and then in ticketinfo.php (I am using the $task_id = $_GET['$task_id'] at the top)

Code: Select all

<?php $query = "SELECT * FROM adminticket_tbl WHERE ticket_id = '$ticket_id'";
$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'];
?>
I saw that the value of the ticket ID was being passed, but forgot that it needs to be just WHERE ticket_id = '$ticket_id'

Now I think i may be able to get the add new ticket to work. Off to try it.

Posted: Fri Aug 19, 2005 12:55 pm
by brookie
Solved...both items now work as I want.

Code: Select all

<?php echo "<a href='troubleticket.php?task_id=".$task['task_id']."'>Add New Ticket</a>" ?>