Go Back   Computer Forums > Web Design | Website Development > Web Programming
Click Here to Login
Join Computer forums Today


Reply
 
Thread Tools Search this Thread Display Modes
 
Old 05-08-2007, 11:53 AM   #1
Beta Member
 
TrojanFrost's Avatar
 
Join Date: Apr 2007
Posts: 2
Exclamation help mysql and php.. need to view this reports

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
__________________

TrojanFrost is offline   Reply With Quote
Old 05-08-2007, 05:13 PM   #2
In Runtime
 
Daeva's Avatar
 
Join Date: Dec 2005
Posts: 407
Send a message via AIM to Daeva Send a message via MSN to Daeva Send a message via Yahoo to Daeva
Default Re: help mysql and php.. need to view this reports

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

__________________
**Official Self-proclaimed glorified excessive (insert additional adjectives here) post editor/modifier.
Edit = Best feature ever
http://www.twitter.com/xDaevax
Daeva is offline   Reply With Quote
Old 05-08-2007, 10:46 PM   #3
Beta Member
 
TrojanFrost's Avatar
 
Join Date: Apr 2007
Posts: 2
Default Re: help mysql and php.. need to view this reports

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
TrojanFrost is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off



All times are GMT -5. The time now is 05:06 PM.


Powered by vBulletin® Version 3.8.8 Beta 4
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Friendly URLs by vBSEO 3.6.0