need to add grouping in my data table

[scarezekiel]

grouping - i need to add an option where i can select ALL of the branches

-then my table will show all list of employees' leaves from all the branches..

-to make it easier to view all the leave details when i select ALL..

-instead of having to preview all the branches 1 by 1.

-do i need to modify my view on my database or just modify my codes..what codes should i use.

-Please tell me where and what do i need to change.

 

 

 

<h3><FORM name ="reportbutton" method="post" align="left" action="index.php?view=report"> <strong>Leave Application Listing </strong>|  <Input type = "Submit" Name = "Submit1" Class="button" VALUE = "Back To Report">  </h3> </FORM>
				<br><br>
<form name="leavebutton" method="post" action="index.php?view=report_leave">
<?
$b=$_GET['b'];
$m=$_GET['m'];
$y=$_GET['y'];

if  ($b <> '' && $m <> '' && $y <> '') {

$branch=$b;
$leavemonth=$m;
$leaveyear=$y;
}
else
{
$branch=$_POST['branch'];
$leavemonth=$_POST['leavemonth'];
$leaveyear=$_POST['leaveyear'];
}



$connection=mysql_connect("$server", "$username", "$password")
or die("Could not establish connection");
mysql_select_db($database_name, $connection)
or die ("Could not select database");


   
    $query="SELECT * from tblworkgroup";
    $result=mysql_query($query);?>


       	  Branch 
        <select name="branch" >        
        <? while($row=mysql_fetch_array($result)) { ?>
   		 	<option value= "<?=$row['WorkGroupID']?>" <? if ($branch==$row['WorkGroupID']){ echo 'selected'; } ?> ><?=$row['WorkGroupName']?></option>
        <? } ?>
        </select>
           </td>
          </tr>
       <br><br><br>
		  Month   
	<select name="leavemonth" >
	<option value = "1" <? if ($leavemonth==1){ echo 'selected'; } ?>>January</option>
	<option value = "2" <? if ($leavemonth==2){ echo 'selected'; } ?>>February</option>
	<option value = "3" <? if ($leavemonth==3){ echo 'selected'; } ?>>March</option>
	<option value = "4" <? if ($leavemonth==4){ echo 'selected'; } ?>>April</option>
	<option value = "5" <? if ($leavemonth==5){ echo 'selected'; } ?>>May</option>
	<option value = "6" <? if ($leavemonth==6){ echo 'selected'; } ?>>June</option>
	<option value = "7" <? if ($leavemonth==7){ echo 'selected'; } ?>>July</option>
	<option value = "8" <? if ($leavemonth=={ echo 'selected'; } ?>>August</option>
	<option value = "9" <? if ($leavemonth==9){ echo 'selected'; } ?>>September</option>
	<option value = "10" <? if ($leavemonth==10){ echo 'selected'; } ?>>October</option>
	<option value = "11" <? if ($leavemonth==11){ echo 'selected'; } ?>>November</option>
	<option value = "12" <? if ($leavemonth==12){ echo 'selected'; } ?>>December</option> 
	</select>

	   
	Year   
	<select name="leaveyear" >
	<option value = "2010" 	<? if ($leaveyear==2010) { echo 'selected';} ?>>2010</option>
	<option value = "2011"	<? if ($leaveyear==2011) { echo 'selected';} ?>>2011</option>
	<option value = "2012"	<? if ($leaveyear==2012) { echo 'selected';} ?>>2012</option>
	<option value = "2013"	<? if ($leaveyear==2013) { echo 'selected';} ?>>2013</option>
	<option value = "2014"	<? if ($leaveyear==2014) { echo 'selected';} ?>>2014</option>
	<option value = "2015"	<? if ($leaveyear==2015) { echo 'selected';} ?>>2015</option>
	<option value = "2016"	<? if ($leaveyear==2016) { echo 'selected';} ?>>2016</option>
	<option value = "2017"	<? if ($leaveyear==2017) { echo 'selected';} ?>>2017</option>

	</select>


		  

<input type='submit' name='submit' value='Preview' Class="button" onclick='return validate()'>
   
   </div>

</form>			


<br /><br />

<div id="box" valign="top">
                	<h3> <strong>Leave Application Details</strong> &nbsp</h3> 
				<br><br>
	<FORM name="printbutton" method="post" align="left" action="report_print.php" target="_blank">
		<input type="hidden" name="leavemonth" value="<?php echo $leavemonth; ?>">
		<input type="hidden" name="leaveyear" value="<?php echo $leaveyear; ?>">
		<input type="hidden" name="branch" value="<?php echo $branch;?>">
		<Input type = "Submit" Name = "Submit1" Class="button" VALUE = "Print Leave Details">
	</form>					
                	<table width="80%" align="center" >
					<thead> 
<tr>
<th width="700px" align="left">Name</a></th>
                            	
<th width="500px" align="center"></a>Application</th>

<th width="500px" align="center"></a>Date</th>
                           
<th width="500x" align="right"></a>Reason</th>
</tr>
</thead>
<tbody>
<?
/*echo "branch:".$branch.'<br />';
echo "leavemonth:".$leavemonth.'<br />';
echo "leaveyear:".$leaveyear.'<br />';*/
//--------------------------------------------------
if ($leavemonth =='' ||  $leaveyear =='' || $branch == '') {
	// one value missing, so don't display		
}
else
{
	// start display data
	$rs = mysql_query( " call vwleavereport('$leavemonth', '$leaveyear', $branchid, 0);");
	$query="SELECT *, 
			MONTH(tblleaveapplication.DateFrom) as DFMonth,
			MONTH(tblleaveapplication.DateTo) as DTMonth,
			YEAR(tblleaveapplication.DateFrom) as DFYear
			FROM `tblleaveapplication`
			LEFT JOIN `tblemployee`
			ON tblleaveapplication.employeeid = tblemployee.id
			WHERE WorkGroupID = '".$branch."'				
			AND (
			(MONTH(tblleaveapplication.DateFrom)=".$leavemonth." AND YEAR(tblleaveapplication.DateFrom)=".$leaveyear.")
			OR
			(MONTH(tblleaveapplication.DateTo)=".$leavemonth." AND YEAR(tblleaveapplication.DateFrom)=".$leaveyear.")
			)
			";

	$result = mysql_query($query);
	while($row=mysql_fetch_array($result))
	{
		echo '<tr>';
			echo '<td>'.$row['EmployeeName'].'</td>';
			echo '<td>'.$row['DateFrom'].'</td>';
			echo '<td>'.$row['DateTo'].'</td>';
			echo '<td>'.$row['Purpose'].'</td>';				
		echo '</tr>';			
	}

}
?>

</table>

[blacknight]

manualy add the select all branches to the dropdown list give it the value of all

then this is where it gets a lil complicated

this would be your new $query statement

 

$query="SELECT *,
MONTH(tblleaveapplication.DateFrom) as DFMonth,
MONTH(tblleaveapplication.DateTo) as DTMonth,
YEAR(tblleaveapplication.DateFrom) as DFYear
FROM `tblleaveapplication`
LEFT JOIN `tblemployee`
ON tblleaveapplication.employeeid = tblemployee.id";
if ($branch != 'ALL')
{
$query ."WHERE WorkGroupID = '".$branch."' AND";
}
$query ."(
(MONTH(tblleaveapplication.DateFrom)=".$leavemonth." AND YEAR(tblleaveapplication.DateFrom)=".$leaveyear.")
OR
(MONTH(tblleaveapplication.DateTo)=".$leavemonth." AND YEAR(tblleaveapplication.DateFrom)=".$leaveyear.")
)";

[scarezekiel]

thx..on the dropdown part..what code should i use..cause i tried <option>ALL</option>..

 

it didnt worked..

 

can you elaborate more on that..

[blacknight]

yea i made an error in muy code lol

change

$query ."WHERE WorkGroupID = '".$branch."' AND";

to

$query ."WorkGroupID = '".$branch."' AND";

 

then change

ON tblleaveapplication.employeeid = tblemployee.id";

to

ON tblleaveapplication.employeeid = tblemployee.id WHERE ";

 

and as fopr the dropdown befor your while statement add

<option value= "ALL" <? if ($branch=='ALL'){ echo 'selected'; } ?> >All Branches</option>

 

and a side note dont use shorttags .. like <?  it can lead to to issues with servers allways use <?php

[scarezekiel]

okay..thx bro