help mysql and php.. need to view this reports

[TrojanFrost]

hi guys.. i really tried many things to solve this problem.. but i really cant figure this out..

i have 2 tables actually its 3 tables but the other is just for the user's account so here it is:

employee table
ins_ID -> pk
emp_ID* -> unique key
last_Name*
first_Name*
middle_Name*
position
pos_Stat* this is enum which can be RnF or M
branch

sample:
ins_ID emp_ID last_Name first_Name middle_Name position
1 727 lname fname mname tester
2 826 lName fName mName tester
3 999 name nam na manager

pos_Stat branch
RnF loc1
RnF loc1
M loc1

RnF is for Rank and File and M for Managers

payment table
payment_ID -> pk
emp_ID*
date*
amount*

sample:
payment_ID emp_ID date amount
1 727 2007-05-26 10.00
2 727 2007-05-27 50.00
3 727 2007-05-28 60.00
5 826 2007-05-26 30.00
6 826 2007-05-27 90.00
8 826 2007-05-29 50.00
9 999 2007-05-26 50.00
10 999 2007-05-27 50.00
11 999 2007-05-28 50.00
12 999 2007-05-29 50.00


i've placed * on the important data's needed in the creation of the report.

now here's the sample report i needed to display after choosing the from and to date..

for example the user chose May 26, 2007 to May 29,2007
it should look like:

i didn't include the first name, last name, middle name its just an example anyways. But if needed it is located just after Emp No.
im using php with html tables here. Any suggestions, comments,
will gladly be appreciated ^^


Emp No. May26 May27 May28 May29 Total
727 10.00 50.00 60.00 20.00 140.00
826 30.00 90.00 10.00 50.00 180.00
999 50.00 50.00 50.00 50.00 200.00

Total - RnF 40.00 140.00 60.00 50.00 290.00
Total - M 50.00 50.00 50.00 50.00 200.00
Grand Total 90.00 190.00 110.00 100.00 520.00

up there is what i cant get.. please help anyone!! thanks..
mine has tons of mysql queries.. im so tired of it T_T

the report feature is the most important part of my system T_T

 

[Daeva]

could you possibly show the sql command you are using to query the database, in actual mySql syntax?

 

[TrojanFrost]

ok here is one thing ive come up with but its just the emp_ID last_name, first_name, middle_name and the dates

<?
$date1 = $_POST['date1'];
$date2 = $_POST['date2'];

mysql_connect("localhost", "admin", "******") or die (mysql_error());
mysql_select_db("chryslerappdb") or die (mysql_error());

$selectAll = mysql_query("Select * From payment Where date Between '$date1' and '$date2' Group By emp_ID") or die (mysql_error());

$dateQuery = mysql_query("Select Distinct date From payment Where date Between '$date1' and '$date2' Order by Date") or die (mysql_error());

$selectDay = mysql_query("Select DISTINCT DATE_FORMAT(date, '%M ' '%d') As Day From payment Where date Between '$date1' And '$date2' Order by date") or die (mysql_error());

?>
<table border = "1">
<tr>
<th>Emp</th>
<th colspan = "3">NAME OF EMPLOYEES</th>
<? while ($day = mysql_fetch_array($selectDay))
{
$dey = $day["Day"];
?>
<th rowspan = "2"><? echo $dey; ?></th>
<?
}
?>
<th rowspan = "2">Total</th>

</tr>

<tr>
<th>No.</th>
<th>Last Name</th>
<th>First Name</th>
<th>Middle Name</th>
</tr>

<tr>
<? while ($all = mysql_fetch_array($selectAll))
{
$emp_ID = $all["emp_ID"];
?>
<td><? echo $emp_ID; ?></td>
<?
$empQuery = mysql_query("Select last_Name, first_Name, middle_Name From employee Where emp_ID = $emp_ID") or die (mysql_error());

?>
<? while ($employee = mysql_fetch_array($empQuery))
{
?>
<td><? echo $employee["last_Name"]; ?></td>
<td><? echo $employee["first_Name"]; ?></td>
<td><? echo $employee["middle_Name"]; ?></td>
</tr>

<? }
}

?>
</table>

that results to something like:

Emp EMPLOYEE NAME
NO LN FN MN DATE DATE DATE DATE DATE DATE TOTAL
data data data data
data data data data
data data data data

but i cant figure out how i can manage to place the amount in there