I have a help desk system in which I specified some default sort settings and what not. I've recently added drop down boxes to this, in hopes that a user can change the view settings for themselves.
The assigned to: works fine, but I'm not sure how the person prior to me did this. I'm specifically trying to get the STATUS drop down box to change the status based on the selection.
The table the status pulls from is it_progress and the field name is progrid (id field) and progname (name of status).
Any help to get me pointed in the right direction would be greatly apprecaited.
<LINK rel="stylesheet" type="text/css" href="../../css/style.css">
<form method="post" action="" name="itform">
<?php
include("../../db_connection/connectToDB.php");
include('../../admin_includes/functions.php');
include('functions.php');
connectToDB();
$respid = getRespid($ckname);
$amIit = getItAccess($respid);
//error_reporting(E_ALL);
//ini_set("display_errors", 1);
if ($_GET['dir']=="ASC")
{
$NextDirection = "DESC"; //This will be used to construct the link to determine the sorting direction for the next column.
$SortDirection = "ASC";
}
else
{
$NextDirection = "ASC";
$SortDirection = "DESC";
}
$column = preg_replace("/[^0-9A-Za-z\.\_]/","",$_GET['col']);
if (!$column)
$column = "it_jobs.jobid";
$whereLimitStatus = " it_jobs.status = '1' ";
if(isset($itAssigned) && $itAssigned != '')
{
$whereItRep = " AND it_jobs.resp1 = '".$itAssigned."' ";
}
if(isset($searchRequests) && $searchRequests != 'Search...' && $searchRequests != '')
{
$keyword_array = explode(" ", $searchRequests);
foreach($keyword_array as $keyword)
{
$whereTrump .= /*$variableAnd.*/"(it_jobs.srno LIKE '%".$keyword."%' OR ";
$whereTrump .= "it_jobs.descr LIKE '%".$keyword."%' OR ";
$whereTrump .= "it_jobs.submitteddate LIKE '%".$keyword."%' OR ";
$whereTrump .= "it_jobs.closeddate LIKE '%".$keyword."%' OR ";
$whereTrump .= "it_progress.progname LIKE '%".$keyword."%' OR ";
$whereTrump .= "it_jobs.category LIKE '%".$keyword."%' OR ";
$whereTrump .= "it_category.category LIKE '%".$keyword."%' OR ";
$whereTrump .= "it_jobs.submittedby LIKE '%".$keyword."%' OR ";
$whereTrump .= "it_comments.comment LIKE '%".$keyword."%' OR ";
$whereTrump .= "it_jobs.resp1 LIKE '%".$keyword."%') AND";
}
$fieldValue = $searchRequests;
}
else
{
$whereTrump = "";
$fieldValue = 'Search...';
}
$query = "
SELECT it_jobs.srno,
it_jobs.jobid,
it_jobs.descr,
it_jobs.status,
it_jobs.submitteddate,
it_jobs.closeddate,
it_jobs.category,
it_category.category,
it_category.catgroup,
it_jobs.pri,
it_jobs.submittedby,
it_progress.progrid,
it_progress.progname,
it_jobs.resp1,
pr_resp.respid,
pr_resp.ckname
FROM it_jobs
LEFT JOIN pr_resp ON it_jobs.resp1 = pr_resp.respid
LEFT JOIN it_progress ON it_jobs.status = it_progress.progrid
LEFT JOIN it_category ON it_jobs.category = it_category.catid
LEFT JOIN it_comments ON it_jobs.jobid = it_comments.jobid
WHERE $whereTrump
$whereLimitStatus
$whereItRep
GROUP BY it_jobs.jobid
ORDER BY it_jobs.pri ASC,$column $SortDirection ";
$result= mysql_query($query) or die (mysql_error());
$selectedField = '&searchRequests='.$fieldValue;
$num = mysql_num_rows($result);
echo "<br /><br />" .$query ."<br /><br /><br />";
if ($column=="")
{
$column = "it_jobs.srno";
}
?>
<table border="0" cellspacing='0' cellpadding='0' width="98%">
<tr>
<td class='tblheader'><span class="bigboldwhitefont">Service Desk Records Total: (<?=number_format($num)?>)</span></td>
<td class='tblheader' align="right"><span class="searchtext"<input type="text" name="searchRequests" value="<?=$fieldValue?>" onFocus="clearText(this)" ></td>
</tr>
</table>
<table border="0" cellspacing='0' cellpadding='0' width="98%" align="center">
<tr>
<td class='drkdrkbrown' width='24%'><span class="whiteboldtext">Assigned to: </span>
<select name="itAssigned" onChange="javascript:this.form.submit();">
<option value="">Select</option>
<?php
$sqlIt_rep = "SELECT respid, ckname FROM pr_resp WHERE itresp = '1' ORDER BY ckname";
$resIt_rep = mysql_query($sqlIt_rep);
$numIt_rep = mysql_num_rows($resIt_rep);
for($i=0;$i<$numIt_rep;++$i)
{
$sel = "";
$respID_rep = mysql_result($resIt_rep,$i,0);
$ckname_rep = mysql_result($resIt_rep,$i,1);
if($itAssigned == $respID_rep){$sel="SELECTED";}
echo '<option '.$sel.' value="'.$respID_rep.'">'.$ckname_rep.'</option>';
}
?>
</select>
</td>
<td class='drkdrkbrown' width='24%'><span class="whiteboldtext">Status: </span>
<select name="orderby" onchange="this.form.submit();">
<option value="">Select</option>
<option value="Open">Open</option>
<option value="Closed">Closed</option>
<option value="In Progress">In Progress</option>
<option value="Awaiting Approval">Awaiting Approval</option>
</select>
</td>
<td class='drkdrkbrown' width='24%'><span class="whiteboldtext">Category Group: </span>
<select name="itcategory" onChange="javascript:this.form.submit();">
<option value="">Select</option>
<?php
$statusquery = "SELECT catgroup FROM it_category GROUP BY catgroup ORDER BY catgroup";
$statusresult = mysql_query($statusquery);
while ($row = mysql_fetch_assoc($statusresult))
{
echo "<option value='" . $row['catgroup'] . "'>" . $row['catgroup'] . "</option>";
}
?>
</select>
</td>
<td class='drkdrkbrown' width='24%'><span class="whiteboldtext">Priority: </span>
<select name="itpriority" onChange="javascript:this.form.submit();">
<option value="">Select</option>
<?php
$statusquery = "SELECT pri FROM it_jobs GROUP BY pri ORDER BY pri";
$statusresult = mysql_query($statusquery);
while ($row = mysql_fetch_assoc($statusresult))
{
$priority = $row['pri'];
if ($priority == "0")
{
echo "Brand New";
}
if ($priority == "1")
{
echo "Urgent";
}
echo "<option value='" . $priority . "'>" . $priority . "</option>";
}
?>
</select>
</td>
</tr>
</table>
<?php
echo "
<table border='0' cellspacing='0' cellpadding='0' width='98%' align='center'>
<tr>
<td class='drkbrown' align='left'><a href='?dir=$NextDirection&col=it_jobs.jobid' class='drkbrownfont'>Request No.</a></td>
<td class='drkbrown' align='left'><a href='?dir=$NextDirection&col=it_jobs.descr' class='drkbrownfont'>Issue Title</a></td>
<td class='drkbrown' align='left'><a href='?dir=$NextDirection&col=it_jobs.pri' class='drkbrownfont'>Urgency</a></td>
<td class='drkbrown' align='left'><a href='?dir=$NextDirection&col=it_jobs.submittedby' class='drkbrownfont'>Submitter</td>
<td class='drkbrown' align='left'><a href='?dir=$NextDirection&col=pr_resp.ckname' class='drkbrownfont'>IT Assignee</td>
<td class='drkbrown' align='left'><a href='?dir=$NextDirection&col=it_category.catgroup' class='drkbrownfont'>Issue Type</td>
<td class='drkbrown' align='left'><a href='?dir=$NextDirection&col=it_category.category' class='drkbrownfont'>Category</td>
<td class='drkbrown' align='left'><a href='?dir=$NextDirection&col=it_jobs.submitteddate' class='drkbrownfont'>Date Created</td>
<td class='drkbrown' align='left'><a href='?dir=$NextDirection&col=it_progress.progname' class='drkbrownfont'>Status</td>
</tr>";
while ($row = mysql_fetch_assoc($result))
{
$jobnumber = $row['srno'];
$jobid = $row['jobid'];
$jobdescription = $row['descr'];
$jobstatus = $row['progname'];
$submitteddate = date("F, j Y",strtotime($row['submitteddate']));
$closeddate = $row['closeddate'];
$category = $row['category'];
$catgroup = $row['catgroup'];
$priority = $row['pri'];
$submittedby = $row['submittedby'];
$assignee = $row['ckname'];
$trcolor = ($i++ %2 == 0)?'#ebedd4' : '#fdffe6';
switch($priority)
{
case "0":
$priority = "Brand New";
$image = "<img src='../../images/new.png' border='0' />";
$color = 'class="brandnew"';
break;
case "1":
$priority = "Urgent";
$image = "<img src='../../images/urgent.png' border='0' />";
$color = 'class="urgent"';
break;
case "2":
$priority = "High";
$image = "<img src='../../images/highpriority.png' border='0' />";
$color = 'class="high"';
break;
case "3":
$priority = "Medium";
$image = "<img src='../../images/mediumpriority.png' border='0' />";
$color = 'class="medium"';
break;
case "4":
$priority = "Low";
$image = "<img src='../../images/lowpriority.png' border='0' />";
$color = 'class="low"';
break;
case "5":
$priority = "Cosmetic";
$image = "<img src='../../images/lowpriority.png' border='0' />";
$color = 'class="cosmetic"';
break;
}
echo "<tr bgcolor=".$trcolor.">";
echo '<td align="left" class="altcolortrs"><a href="itServiceTicketDetail.php?jobid='.getJobID($jobnumber,2).'" class="drkbrownfont">'.$jobnumber.'</a></td>';
echo '<td align="left" class="altcolortrs"><a href="itServiceTicketDetail.php?jobid='.getJobID($jobnumber,2).'" class="drkbrownfont">'.$jobdescription.'</a></td>';
echo '<td align="left">'.$image.'</td>';
echo '<td align="left" class="altcolortrs"><a href="itServiceTicketDetail.php?jobid='.getJobID($jobnumber,2).'" class="drkbrownfont">'.$submittedby.'</a></td>';
echo '<td align="left" class="altcolortrs"><a href="itServiceTicketDetail.php?jobid='.getJobID($jobnumber,2).'" class="drkbrownfont">'.$assignee.'</a></td>';
echo '<td align="left" class="altcolortrs"><a href="itServiceTicketDetail.php?jobid='.getJobID($jobnumber,2).'" class="drkbrownfont">'.$catgroup.'</a></td>';
echo '<td align="left" class="altcolortrs"><a href="itServiceTicketDetail.php?jobid='.getJobID($jobnumber,2).'" class="drkbrownfont">'.$category.'</a></td>';
echo '<td align="left" class="altcolortrs"><a href="itServiceTicketDetail.php?jobid='.getJobID($jobnumber,2).'" class="drkbrownfont">'.$submitteddate.'</a></td>';
echo '<td align="left" class="altcolortrs"><a href="itServiceTicketDetail.php?jobid='.getJobID($jobnumber,2).'" class="drkbrownfont">'.$jobstatus.'</a></td>';
echo '</tr>';
}
echo "</table>";
?>
</body>
</html>