Click here to Skip to main content
15,885,309 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
hi i am getting multiply records for only one user ...i need that per row i can have only one user record and on next line next user
here is the code.
http://imageupload.org/?di=4129865848716[^link to sql image]



<<pre lang="xml">table >
<?php
$depsql = "SELECT hs_hr_leave.leave_date,hs_hr_leave.leave_status,hs_hr_leave.leave_comments,hs_hr_compstructtree.title,hs_hr_leavetype.leave_type_name,hs_hr_leavetype.leave_color,hs_hr_employee.emp_lastname,hs_hr_employee.emp_firstname, hs_hr_employee.emp_middle_name FROM hs_hr_leave INNER JOIN hs_hr_leavetype ON hs_hr_leave.leave_type_id = hs_hr_leavetype.leave_type_id INNER JOIN hs_hr_employee ON hs_hr_leave.employee_id = hs_hr_employee.employee_id INNER JOIN hs_hr_compstructtree ON hs_hr_leave.emp_depment = hs_hr_compstructtree.dept_id WHERE hs_hr_leave.emp_depment = 1";
$deprest = mysql_query($depsql);
if(mysql_num_rows($deprest) == 0){
echo "Sorry. No records found";
}else
{
while ($row = mysql_fetch_array($deprest, MYSQL_NUM)) {
    $leave_date      = $row[0];
    $leave_status    = $row[1];
    $leave_comments  = $row[2];
    $demp_title      = $row[3];
    $leave_type_name = $row[4];
    $leave_color     = $row[5];
    $emp_lname       = $row[6];
    $emp_fname       = $row[7];
    $emp_mname       = $row[8];
    $dbD = date("d", strtotime($row[0]));
    $dbM = date("m", strtotime($row[0]));
    $dbY = date("Y", strtotime($row[0]));
    $dbuser = date("Y-m-d", mktime(0, 0, 0, $dbM, $dbD, $dbY));
    $userDates[$dbuser] = array('comments' => $leave_comments, 'status' => $leave_status,'color' => $leave_color,'deptitle' => $demp_title,'emplname' => $emp_lname);
}
foreach($userDates[$dbuser] as $E) { ?>
<tr>
<td width="18%" colspan="2"><strong><?php echo $userDates[$dbuser]['emplname'] ?></strong></td>
<td width="12%" colspan="2"><strong><?php echo $userDates[$dbuser]['deptitle'] ?></strong></td>
<?php
    $num = cal_days_in_month(CAL_GREGORIAN, $DMonth, $DYear); // 31
    $arrydats = array_fill (1,$num,"");
    $i=0;
                foreach($arrydats as $d)
                {
                    $i++;
                    $dates = date("Y-m-d", mktime(0, 0, 0, $DMonth,$i, $DYear));
                    $checkdate = strtotime($dates);
                                 if(isset($userDates[$dates]))
                                   {
                                    echo "<td  width='26' title='".$userDates[$dates]['comments']."' height='20px' bgcolor='#".$userDates[$dates]['color']."' align='center' valign='middle' >";
                                    if($userDates[$dates]['status'] == -1)
                                        { echo "R"; }
                                    elseif ($userDates[$dates]['status'] == 0 )
                                        { echo "C"; }
                                    elseif ($userDates[$dates]['status'] == 1 )
                                        { echo "PA"; }
                                    elseif ($userDates[$dates]['status'] == 2 )
                                        { echo "A"; }
                                    elseif ($userDates[$dates]['status'] == 3 )
                                        { echo "T"; }
                                    elseif ($userDates[$dates]['status'] == 4 )
                                        { echo "W"; }
                                    elseif ($userDates[$dates]['status'] == 5 )
                                        { echo "SH"; }
                                        }
                                    else {
                                            echo "<td width='26' height='20px' align='center' valign='middle' >".$i."</td>";
                                            }

                }
}
?>
</tr>
</table>

http://imageupload.org/?di=14129862588513[^here you can see the screeshot]
Posted
Updated 25-Feb-11 7:33am
v8

1 solution

Your sql query is where your issue lies. You need to refactor that so that your joins only result in 1 to 1 relationships with the parent tables. You need to figure out which of the subtables being joined are causing the extra rows and then find a way to constrain that part of the query to a single row.
 
Share this answer
 
Comments
Espen Harlinn 25-Feb-11 10:32am    
Good point, my 5
vicky87 25-Feb-11 13:36pm    
i have add the screen short form my sql how i have linked the table ..and fields which are marked as blue are required to display data ...is there thing wrong with it ....
Costica U 25-Feb-11 17:50pm    
It's likely that you have duplicates by employee_id in hs_hr_leave for the same emp_depment
vicky87 27-Feb-11 16:01pm    
user is selecting year , month and department
i want that for each employee it shows that date when he/she was on leave ... i am getting the data but it is duplicate ...i have tried different quires but none is working expect this one...i have linked the screen short about this sql...how they are linked to generate sql code.
vicky87 5-Mar-11 6:48am    
any help...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900