0

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&amp;col=it_jobs.jobid' class='drkbrownfont'>Request No.</a></td>
				<td class='drkbrown' align='left'><a href='?dir=$NextDirection&amp;col=it_jobs.descr' class='drkbrownfont'>Issue Title</a></td>
				<td class='drkbrown' align='left'><a href='?dir=$NextDirection&amp;col=it_jobs.pri' class='drkbrownfont'>Urgency</a></td>
				<td class='drkbrown' align='left'><a href='?dir=$NextDirection&amp;col=it_jobs.submittedby' class='drkbrownfont'>Submitter</td>
				<td class='drkbrown' align='left'><a href='?dir=$NextDirection&amp;col=pr_resp.ckname' class='drkbrownfont'>IT Assignee</td>
				<td class='drkbrown' align='left'><a href='?dir=$NextDirection&amp;col=it_category.catgroup' class='drkbrownfont'>Issue Type</td>
				<td class='drkbrown' align='left'><a href='?dir=$NextDirection&amp;col=it_category.category' class='drkbrownfont'>Category</td>
				<td class='drkbrown' align='left'><a href='?dir=$NextDirection&amp;col=it_jobs.submitteddate' class='drkbrownfont'>Date Created</td>
				<td class='drkbrown' align='left'><a href='?dir=$NextDirection&amp;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>
2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by sutt0n
0

If I'm understanding you correctly, you're pulling from the database successfully, and you're just wanting to update the selected option in the database (via SQL) once it is selected?

If that's so, then I would either:

A) Use AJAX to do this.
B) Throw a form submission handler above the HTML code.

For the form submission handler, here's how I would do this:

<?php
// Check if form has been submitted by checking the form name's existence
if (isset($_POST['itform']))
{
    // Run update code here
}
?>

For the AJAX, you'll have to look into it yourself if you haven't already. I use SACK AJAX - it's probably not the best, but it's something I'm comfortable with for now.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.