[Resolved] making records dissapear from a web page

timmybuck

Hi guys,

Just looking for help to achieve this little task. I have two pages one is called tech.php and the other is details.php.
tech.php displays a list of jobs that the user has requested. there is a dynamic hyperlink for each job that links to details.php to give more details about the job and allow a technican to record the action he/she has taken to fix the the job. when the job is complete eg when the technican ticks the check box and submits it. the job should dissapear from tech.php.

can anyone help with this ?

below is details.php

<html>
<head>
</head>
<body>
<?php

require('dbconnect.php');
set_time_limit(0);
$jobId=$_GET['jobId'];

$sql = "select jobId, email, room, computers, description, date FROM requests_table

WHERE requests_table.jobId='$jobId'";

$result = mysql_query($sql);
echo "<H3><b>Faults and Requests</b></H3>";
echo "<table width=\"100%\">\n";

echo " <tr>\n";

echo " <th align=\"left\"><b>JobID</b></th>\n";

echo " <th align=\"left\"><b>Email</b></th>\n";

echo " <th align=\"left\"><b>Room</b></th>\n";

echo " <th align=\"left\"><b>Computers</b></th>\n";

echo " <th align=\"left\" ><b>Description</b></th>\n";
echo " <th align=\"left\"><b>Date&time</b></th>\n";

echo " </tr>\n";

$row = mysql_fetch_array($result);

echo '<td>'.$row['jobId'].'</td>';
echo '<td >'.$row['email'].'</td>';
echo '<td>'.$row['room'].'</td>';
echo ' <td>'.$row['computers'].'</td>'."\n";

echo ' <td>'.$row['description'].'</td>'."\n";
echo ' <td>'.$row['date'].'</td>'."\n";
echo '</tr>'."\n";

echo '</table>';
echo '<hr>';
?>

<?php
$color[0] = "FCFCFC";

$color[1] = " 34ADE5 ";

$jobId=$GET['jobId'];
$action_taken=$GET['action_taken'];
$Tech_initals=$_GET['Tech_intials'];

if($_POST["Submit1"]) {

$action_taken = addslashes($POST['action_taken']);
$Tech_initals = addslashes($POST['Tech_initals']);

$result = mysql_query("INSERT INTO tech_table (jobId, Tech_initals, action_taken, date)
VALUES ('$jobId', '$Tech_initals', '$action_taken', NOW())") or die("Unable to insert record: " . mysql_error());
}
$sql = "SELECT jobId, Tech_initals, action_taken, date FROM tech_table WHERE jobId='".$_GET['jobId']."'";
$result = mysql_query($sql);

echo "<H3><b>Job History</b></H3>";
echo "<table>\n";
echo " <tr>\n";

echo " <th><b>Tech Initals</b></th>\n";
echo " <th><b>Action taken</b></th>\n";

echo " <th><b>Date</b></th>\n";

echo " <th><b>Status of Job</b></th>\n";
echo " <th><b>Date of completion</b></th>\n";
echo " </tr>\n";

while ($row = mysql_fetch_array($result)) {

 $a = ($a+1)%2;  
echo '<tr bgcolor='.$color[$a] . ">\n";  
//echo '<td>'.$row['jobId'].'</td>';
echo ' <td>'.$row['Tech_initals'].'</td>'."\n";  
echo ' <td>'.$row['action_taken'].'</td>'."\n";  
echo ' <td>'.$row['date'].'</td>'."\n"; 
echo ' <td>'.$row['status_of_job'].'</td>'."\n"; 
echo ' <td>'.$row['compdate'].'</td>'."\n"; 
echo  '</tr>'."\n";                   }
echo '</table>';

?>
</table>
<?php

if (strlen($POST['email']) > 0 ) {
$sql = "SELECT email FROM requests_table WHERE jobId='".$GET['jobId']."'";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);

$to = $row['email'];
$from = "poo@ntlworld.com";
$message = $_POST['email'];
mail($to, "technical services", $message,
"From: {$from}\r\n"
."Reply-To: {$from}\r\n"
."X-Mailer: PHP/" . phpversion());
}
?>
<br>
<?php
echo '<hr>';
?>
<?php
echo "<H3><b> Technican Feedback</b></H3>";

?>
<br>
<form name="details" method="post" action= "<? echo $_SERVER['PHP_SELF'] . "?jobId=" . $jobId; ?>">
<table width="791" height="160" border="0">
<tr>
<input type="hidden" name="email">
<th width="61" scope="row">TechId</th>
<td width="245"><strong>Action taken </strong></td>
<td width="116"><strong>Status of job </strong></td>
<td width="120"> </td>
<td width="126"> </td>
<td width="97"> </td>
</tr>
<tr>
<th scope="row"><input name="Tech_initals" type="text" size="2" maxlength="2"></th>
<td><textarea name="action_taken" cols="30" rows="3"></textarea></td>
<td><input type="checkbox" name="comp" value="1"></td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<th scope="row"> </th>
<td><strong>Email comment </strong></td>

</tr>
<tr>
  <th scope="row">&nbsp;</th>
  <td><textarea name="email" cols="30" rows="3"></textarea></td>

</tr>

</tr>
<tr>
  <th scope="row">&nbsp;</th>
  <td><input type="submit" name="Submit1" value="Submit"></td>

</tr>

</table>
</form>

</body>
</html>

devinemke

i'm not entirely clear on your data relation but what makes a job "complete"? if any actions is taken or just certain actions. anyway, i suggest that you do one of two things:

write a function that fetches the last action taken on a job and based on what that action is return a job status that you can use to display either pending jobs or old jobs.
add a new field to the requests_table that indicates that the job is complete or not (perhaps just a boolean) and use that field in your WHERE clause when fetching records. this field would need to be updated based on new actions being added.

option 1 is technically more "normalized" but would require more hits to the db, this may be cumbersome when fetching large numbers of jobs. option 2 would be fewer hits to the db when getting jobs but is bit more accident prone since you could have the wrong status based on the actions taken. it all depends on how many different places new records can be added.

timmybuck

Thanks for the feedback devinemke

Allow me to clarify, a technican fills in the action_taken text area with what he has done eg replaced hard drive. and then the next technican can do his job which maybe eg install software. once all the jobs are complete, they tick the check box to indicate the job is complete and can email the user to let them know.

I'm trying to work out out how to get the check box feature working so that when its ticked and submitted the job on the tech.php page disappears.

the type of check box is value is a tinyint, maybe i could have something like if the checkbos ix ticked do not display job on tech.php

this is what i need help on eg the code or design, if there is a better way

devinemke

ok then, you should go with option 2 in my original post. add a new field to requests_table called something like "complete" which could simple be a CHAR(1) type. make this field default to NULL. when a user checks the complete checkbox on the form update the complete field in the table to be "Y". the basic idea is that NULL = incomplete and Y = complete (again, it's just a boolean field). now when you do your select query add this to you WHERE clause:

SELECT jobId, email, room, computers, description, date FROM requests_table WHERE jobId='$jobId' AND complete IS NULL

this will only pull incomplete jobs, thus the completed jobs will "dissapear".

mySQL does not have a specific BOOLEAN type, most people either use CHAR(1)[Y or NULL] or TINYINT[1 or 0].

as a side note: somethimes when i design db structures like this i will use a date type instead of a boolean, this has the added value of knowing when the job was completed. the basic idea would be the same: if the date_completed field is NULL then the job is still open, if there is a date then the job is done.

timmybuck

thanks

timmybuck

ok im just think more about it , tell me what you about this.
I'm thinking it might be tricky to update the reuqests_table complete field becuase the check box is in the same form as the where the technican comments go. and that form updates the tech_table, so im wonderig if it would be a good idea to have a column in the tech_table called 'complete' of tinyint type and 'completedate' of type date so that we could do something like

if complete is not null in tech_table then get jobId
and not display the record that belong to that jobId.

does that make sense anyone

tech_table

jobId
Tech_initals

action_taken
date

email_comment

complete

completedate

requests_table

jobId
name
email
room
computers
description
date

devinemke

let's back up. here is my suggestion for a modfied data structure:

requests_table
jobId
name
email
room
computers
description
date
complete (Y or NULL)

tech_table
jobId
Tech_initals
action_taken
date
email_comment

on the form where the user can add a new action to the tech_table add a checkbox called "complete". when the form is submit do 1 INSERT query to the tech_table and (if the checkbox was chaecked) 1 UPDATE query to the requests_table that changes to the "complete" field from NULL to "Y".
make sense?

again you could use a DATE field instead of a CHAR or INT if you want to store the date of completion.

timmybuck

hi ok

i have created the two statements and instead of using the char, i am gonna use the time and date to indicate a complete job.

$result=mysql_query('INSERT INTO 'techtable'( 'complete' )'. " VALUES ( NOW())")or die('Unable to insert record. '.mysql_error());

$query = mysql_query()"update requests_table set complete = now() where jobid = '" . addslashes($_GET['jobid']) . "';";

My question is if i insert in to the techtable then that would mean i have a complete column of type date in the tech_table too as well as the requests_table right.

isnt that right repating data