mysql query to table

maanse · #1 (**permalink**) 12-12-11, 05:33 AM

hi guys im after a little help here, ive got a table that has ids, hours, and dates in and i want to be able to output a table with that information split into weeks based on the dates in the table and between 2 dates passed from a form.

PHP Code:


			
<?php 

    $date1 = '2011-10-28';

    $date2 = '2011-11-27'; 

    $ident = '3301';

    ?>

<?php

    $sql = "SELECT sites.site_name FROM sites WHERE sites.site_code = '$ident'";

    $result = mysql_query($sql) or die(mysql_error());

    while ($row = mysql_fetch_assoc($result)) {

    $site = $row['site_name'];

    }

?>

<?php include("../includes/header.php"); ?>

    

        <?php require_once("../includes/connection.php"); ?>

    

<?php                                                                                   

    include ('/includes/connection.php');

        

    $date_range = $date1 . " To " . $date2;

    echo $date_range;

    $sql = "SELECT * FROM dates WHERE day = 'Friday' AND date BETWEEN '$date1' AND '$date2'"; //get dates of fridays between 2 inputted dates

        $result = mysql_query($sql) or die(mysql_error());

        $num_rows = mysql_num_rows($result);

        echo "Number of dates collected:&nbsp;" . $num_rows. "<br />";

            while ($row = mysql_fetch_assoc($result)) {

                echo $row['date'] . "<br />";

                $date_array[]=$row['date'];                                         

            }

            

            $period1s = $date_array[0]." 00:00:00";

            $period1e = $date_array[1]." 00:00:00";    

            $end = strtotime ( '-1 day' , strtotime ( $period1e ) );

            $period1e = date('Y-m-d', $end)." 00:00:00";

            

            $period2s = $date_array[1]." 00:00:00";

            $period2e = $date_array[2]." 00:00:00";

            $end = strtotime ( '-1 day' , strtotime ( $period2e ) );

            $period2e = date('Y-m-d', $end)." 00:00:00";

            

            $period3s = $date_array[2]." 00:00:00";

            $period3e = $date_array[3]." 00:00:00";

            $end = strtotime ( '-1 day' , strtotime ( $period3e ) );

            $period3e = date('Y-m-d', $end)." 00:00:00";

            

            $period4s = $date_array[3]." 00:00:00";

            $period4e = $date_array[4]." 00:00:00";

            $end = strtotime ( '-1 day' , strtotime ( $period4e ) );

            $period4e = date('Y-m-d', $end)." 00:00:00";

        

            

            if (isset($date_array[5])) {

            

                $period5s = $date_array[4]." 00:00:00";

                $period5e = $date_array[5]." 00:00:00";

                $end = strtotime ( '-1 day' , strtotime ( $period5e ) );

                $period5e = date('Y-m-d', $end)." 00:00:00";

    

    $sql10 = "SELECT name, user_id

    , sum(hours_worked) AS sum_hours

    , location, sum(lunch) AS sum_lunch

    , sum(break) AS sum_break

    , sick, holiday

    , clock_status.pay_rate AS pay_rate 

    , amend_time >= '$period1s' AND amend_time <= '$period1e' AS week1

    , amend_time >= '$period2s' AND amend_time <= '$period2e' AS week2

    , amend_time >= '$period3s' AND amend_time <= '$period3e' AS week3

    , amend_time >= '$period4s' AND amend_time <= '$period4e' AS week4

    , amend_time >= '$period5s' AND amend_time <= '$period5e' AS week5

    FROM clock_status , staff                

    WHERE auth = 1 and clock_status.user_id = staff.staff_number

    and clock_status.location = '$ident'

    GROUP BY name";  

    $query10 = mysql_query($sql10) or die(mysql_error());

    

} else {

    

    $sql10 = "SELECT staff.name, clock_status.user_id

    , sum(clock_status.hours_worked) AS sum_hours

    , clock_status.hours_worked 

    , clock_status.amend_time >= date '$period1s' AND clock_status.amend_time <= date '$period1e' AS week1

    , clock_status.amend_time >= date '$period2s' AND clock_status.amend_time <= date '$period2e' AS week2

    , clock_status.amend_time >= date '$period3s' AND clock_status.amend_time <= date '$period3e' AS week3

    , clock_status.amend_time >= date '$period4s' AND clock_status.amend_time <= date '$period4e' AS week4

    FROM clock_status , staff                

    WHERE clock_status.auth = 1 and clock_status.user_id = staff.staff_number

    and clock_status.location = '$ident'

    GROUP BY staff.name";  

    $query10 = mysql_query($sql10) or die(mysql_error());

    

    }?>    

<table style="border: 1 solid black;">

    <tr>

        <th>Staff No</th>

        <th>Name</th>

        <th>Sum hours</th>

        <th>Week 1</th>

        <th>Week 2</th>

        <th>Week 3</th>

        <th>Week 4</th>

        <th>Week 5</th>

    </tr>

    <?php while ($row = mysql_fetch_assoc($query10)) { ?>

    <tr>

        <td><?php echo $row['user_id'] ;?></td>

        <td><?php echo $row['name'] ;?></td>

        <td><?php echo $row['sum_hours'] ;?></td>

            <?php

            if ($row['hours_worked'] && $row['week1']){

            $sum_week1 = $row['hours_worked'];

            ?>

        <?php echo "<td>" .$sum_week1. "</td>" ;

            } else { echo '<td>0</td>'; }?>

            <?php

            if ($row['hours_worked'] && $row['week2']){

            $sum_week2 = $row['hours_worked'];

            ?>

        <?php echo "<td>" .$sum_week2. "</td>" ;

            } else { echo '<td>0</td>'; }?>

            <?php

            if ($row['sum_hours'] && $row['week3']){

            $sum_week3 = $row['sum_hours'];

            ?>

        <?php echo "<td>" .$sum_week3. "</td>" ;

            } else { echo '<td>0</td>'; }?>

            <?php

            if ($row['sum_hours'] && $row['week4']){

            $sum_week4 = $row['sum_hours'];

            ?>

        <?php echo "<td>" .$sum_week4. "</td>" ;

            } else { echo '<td>0</td>'; }?>

            <?php

            if ($row['sum_hours'] && $row['week5']){

            $sum_week5 = $row['sum_hours'];

            ?>

        <?php echo "<td>" .$sum_week5. "</td>" ;

            } else { echo '<td>0</td>'; }?>

    </tr>

    <?php } ?>

</table>

This is what im using at the moment but its not displaying correctly. This is how i want it to display:
xl.png

Any ideas how i can get that layout..... my brain is hurting now....

any help will be greatly appreciated...

alxkls · #2 (**permalink**) 12-12-11, 02:58 PM

from what i understand you need

Code:

GROUP BY WEEK(date_field,[mode])

check it out here

maanse · #3 (**permalink**) 12-13-11, 02:57 AM

hi i appreciate the reply, however i have a few issues with using that, firstly i cant seem to work out the PHP bit to go with it so i still cant get what i want and secondly the Week selector doesn't let me start a week from friday....

jjjohn · #4 (**permalink**) 12-21-11, 07:32 PM

Did you get your formatting working yet?
From the look of your code example,it appears that your formatting info is not being seen.
What does the actual print out look like?
My suggestion is to wrap your script in an html page,but keep named as php.
Start your page with...
<?php
put your require_once file
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head><title>some title </title></head>
<body bgColor="#ffffff'>
<font face="Times"size="3"color="#000000">

<php /*continue now with the rest of your script.when you come to
formatting areas such as your table setup info,do not close the php,just leave
it open and use echo combined with single ' parenthesis to enclose the table info.
At the very bottom of your script,after you close it ,then close the </body></html>
You may have to try it a few times to get your table data tags enclosed just right.
I hope this was any help to you.