PHP / MYSQL query problem

[maanse]

Hi guys,

 

Im just after a little help here because i cant seem to get my script to work correctly. Basically i have a table full of data and i want to be able to extract a months worth of data but split that data into weeks from within the month.

 

This is my database structure:

-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 13, 2011 at 09:13 AM
-- Server version: 5.5.16
-- PHP Version: 5.3.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `time_man`
--

-- --------------------------------------------------------

--
-- Table structure for table `clock_status`
--

CREATE TABLE IF NOT EXISTS `clock_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(24) NOT NULL DEFAULT '0',
  `location` int(4) NOT NULL DEFAULT '0',
  `date_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `amend_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `hours_worked` varchar(25) NOT NULL DEFAULT '',
  `lunch` int(11) NOT NULL DEFAULT '0',
  `break` int(11) NOT NULL DEFAULT '0',
  `sick` char(3) NOT NULL DEFAULT 'NO',
  `holiday` char(3) NOT NULL DEFAULT 'NO',
  `comments` text NOT NULL,
  `auth` tinyint(1) NOT NULL DEFAULT '0',
  `auth_date` date NOT NULL,
  `paid` tinyint(1) NOT NULL DEFAULT '0',
  `pay_rate` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=734 ;

--
-- Dumping data for table `clock_status`
--

INSERT INTO `clock_status` (`id`, `user_id`, `location`, `date_time`, `status`, `amend_time`, `hours_worked`, `lunch`, `break`, `sick`, `holiday`, `comments`, `auth`, `auth_date`, `paid`, `pay_rate`) VALUES
(720, '653467435', 3301, '2011-10-12 04:00:00', 0, '2011-10-12 06:35:00', '9300', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'L'),
(721, '653467435', 3301, '2011-10-12 06:30:00', 0, '2011-10-12 11:25:00', '17700', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'L'),
(722, '5030341511007', 3301, '2011-10-13 17:45:00', 0, '2011-10-30 22:10:00', '15900', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'H'),
(723, '653467435', 3301, '2011-10-13 01:15:00', 0, '2011-10-13 07:15:00', '21600', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'L'),
(724, '653467435', 3301, '2011-10-13 03:15:00', 0, '2011-10-13 05:15:00', '7200', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'L'),
(725, '653467435', 3301, '2011-11-11 05:45:00', 0, '2011-11-11 13:20:00', '27300', 0, 0, 'NO', 'NO', '', 1, '2011-11-11', 0, 'L'),
(726, '653467435', 3301, '2011-11-11 01:15:00', 0, '2011-11-11 08:25:00', '3663', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'L'),
(727, '12345678954565456', 3301, '2011-11-11 05:45:00', 0, '2011-11-11 16:25:00', '3662', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'L'),
(728, '50226900', 3301, '2011-11-21 09:30:00', 0, '2011-11-21 17:00:00', '27000', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'H'),
(729, '5030341511007', 3301, '2011-11-22 09:30:00', 0, '2011-11-11 15:30:00', '21600', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'H'),
(730, '5030341511007', 3301, '2011-11-22 06:00:00', 0, '2011-11-17 09:20:00', '12000', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'H'),
(731, '653467435', 3301, '2011-11-22 06:45:00', 0, '2011-11-22 12:30:00', '20700', 0, 0, 'NO', 'NO', '', 1, '0000-00-00', 0, 'L'),
(732, '5030341511007', 3301, '2011-11-22 07:45:00', 0, '2011-11-22 15:15:00', '3661', 0, 0, 'NO', 'NO', '', 1, '0000-00-00', 0, 'H'),
(733, '653467435', 3301, '2011-11-22 04:45:00', 0, '2011-11-22 14:05:00', '33600', 0, 0, 'NO', 'NO', '', 1, '0000-00-00', 0, 'L');

-- --------------------------------------------------------


--
-- Table structure for table `staff`
--

CREATE TABLE IF NOT EXISTS `staff` (
  `person_id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL DEFAULT '',
  `staff_number` varchar(20) NOT NULL DEFAULT '0',
  `dob` date NOT NULL DEFAULT '0000-00-00',
  `pay_rate` char(1) NOT NULL DEFAULT '',
  `date_amended` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `expiry` text NOT NULL,
  PRIMARY KEY (`person_id`),
  UNIQUE KEY `staff_number` (`staff_number`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=53 ;

--
-- Dumping data for table `staff`
--

INSERT INTO `staff` (`person_id`, `name`, `staff_number`, `dob`, `pay_rate`, `date_amended`, `expiry`) VALUES
(16, 'Person Two', '3456', '1990-06-23', 'H', '2011-06-23 07:40:38', ''),
(17, 'Person Three', '5678', '1986-05-29', 'H', '2011-01-24 12:18:03', ''),
(15, 'Person One', '1234', '1995-11-29', 'L', '2011-01-18 16:59:53', ''),
(47, 'Person four', '50226900', '1980-04-23', 'H', '2011-06-17 10:50:50', ''),
(48, 'Person Two', '653467435', '1980-01-13', 'L', '0000-00-00 00:00:00', '01/02/1982'),
(50, 'Person One', '5030341511007', '1983-07-13', 'H', '0000-00-00 00:00:00', ''),
(52, 'Person Three', '12345678954565456', '2000-02-01', 'L', '0000-00-00 00:00:00', '01/03/2019');

-- --------------------------------------------------------


/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

This is the script so far...

 

<?php require_once("../includes/connection.php"); ?>
<?php 
$date1 = '2011-10-28';
$date2 = '2011-11-27'; 
$ident = '3301';

$sql10 = "SELECT WEEK(date_time) as Week, staff.name, clock_status.user_id, sum( clock_status.hours_worked ) AS sum_hours, clock_status.hours_worked, clock_status.amend_time
			FROM clock_status, staff
			WHERE amend_time >= '$date1' AND amend_time < '$date2' AND clock_status.auth =1
			AND clock_status.user_id = staff.staff_number
			AND clock_status.location = '3301'
			GROUP BY user_id
			ORDER BY Week, user_id";  
$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>
		<td>Week 1 hours here</td>
		<td>Week 2 hours here</td>
		<td>Week 3 hours here</td>
		<td>Week 4 hours here</td>
		<td>Week 5 hours here</td>
</tr>
<?php } ?>
</table>
<?php require("../includes/footer.php"); ?>

The table should list each person once, with the total hours, then the amount of hours in each week in a different column. As represented in my table.

 

If someone could help me on this issue i would be eternally grateful...

 

 

thanks in advance.