Search the Community

Hello There, Anyone wants to help me how to achive this thing on PHP? I have zero knowledge on Pibot table for PHP Mysqli and minimal knowledge on programming. Here's the link for output And below is the Table Attendance: -- phpMyAdmin SQL Dump -- version 5.2.0 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1 -- Generation Time: Jun 01, 2022 at 10:29 AM -- Server version: 10.4.24-MariaDB -- PHP Version: 8.1.6 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; 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 utf8mb4 */; -- -- Database: `mcgibn` -- -- -------------------------------------------------------- -- -- Table structure for table `attendance` -- CREATE TABLE `attendance` ( `atid` int(11) NOT NULL, `memid` int(11) NOT NULL, `serid` int(11) NOT NULL, `calid` int(11) NOT NULL, `entrydate` timestamp NOT NULL DEFAULT current_timestamp(), `month` varchar(30) CHARACTER SET latin1 NOT NULL, `year` varchar(5) DEFAULT NULL, `createdat` timestamp NOT NULL DEFAULT current_timestamp(), `updatedat` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `viewfrom` int(11) NOT NULL, `astatus` int(11) NOT NULL, `stype` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `attendance` -- INSERT INTO `attendance` (`atid`, `memid`, `serid`, `calid`, `entrydate`, `month`, `year`, `createdat`, `updatedat`, `viewfrom`, `astatus`, `stype`) VALUES (32, 37, 1, 1, '2022-05-31 12:55:28', 'May', '2022', '2022-05-31 12:55:28', '2022-05-31 12:55:28', 0, 1, 2), (33, 37, 2, 1, '2022-05-31 12:55:36', 'May', '2022', '2022-05-31 12:55:36', '2022-05-31 12:55:36', 0, 1, 1), (34, 37, 3, 1, '2022-05-31 12:55:43', 'May', '2022', '2022-05-31 12:55:43', '2022-06-01 03:28:55', 0, 1, 1), (35, 37, 1, 0, '2022-05-31 12:56:15', 'Jun', '2022', '2022-05-31 12:56:15', '2022-06-01 01:17:35', 0, 1, 2), (36, 37, 2, 1, '2022-05-31 12:56:25', 'Jun', '2022', '2022-05-31 12:56:25', '2022-06-01 01:17:35', 0, 1, 1), (37, 37, 3, 1, '2022-05-31 12:56:40', 'Jun', '2022', '2022-05-31 12:56:40', '2022-06-01 01:17:35', 0, 1, 1), (38, 37, 1, 1, '2022-05-31 12:57:24', 'May', '2023', '2022-05-31 12:57:24', '2022-05-31 12:57:24', 0, 1, 2), (39, 37, 2, 1, '2022-05-31 12:57:37', 'May', '2023', '2022-05-31 12:57:37', '2022-05-31 12:57:37', 0, 1, 1), (40, 37, 3, 1, '2022-05-31 12:57:46', 'May', '2023', '2022-05-31 12:57:46', '2022-05-31 12:57:46', 0, 1, 1), (41, 37, 1, 1, '2022-05-31 12:57:57', 'Jun', '2023', '2022-05-31 12:57:57', '2022-06-01 01:17:35', 0, 1, 2), (42, 37, 2, 1, '2022-05-31 12:58:08', 'Jun', '2023', '2022-05-31 12:58:08', '2022-06-01 01:17:35', 0, 1, 1), (43, 37, 3, 1, '2022-05-31 12:58:18', 'Jun', '2023', '2022-05-31 12:58:18', '2022-06-01 01:12:40', 0, 1, 1), (44, 49, 1, 1, '2022-05-31 12:58:32', 'May', '2022', '2022-05-31 12:58:32', '2022-05-31 12:58:32', 0, 1, 2), (45, 49, 2, 1, '2022-05-31 12:58:40', 'May', '2022', '2022-05-31 12:58:40', '2022-05-31 12:58:40', 0, 1, 1), (47, 49, 1, 1, '2022-05-31 12:59:01', 'Jun', '2022', '2022-05-31 12:59:01', '0000-00-00 00:00:00', 0, 1, 2), (48, 49, 2, 1, '2022-05-31 12:59:14', 'Jun', '2022', '2022-05-31 12:59:14', '2022-06-01 01:12:40', 0, 1, 1), (49, 49, 3, 1, '2022-05-31 12:59:31', 'Jun', '2022', '2022-05-31 12:59:31', '2022-06-01 01:12:40', 0, 1, 1), (50, 49, 1, 1, '2022-05-31 12:59:41', 'May', '2023', '2022-05-31 12:59:41', '2022-05-31 12:59:41', 0, 1, 2), (51, 49, 2, 1, '2022-05-31 12:59:49', 'May', '2023', '2022-05-31 12:59:49', '2022-05-31 12:59:49', 0, 1, 1), (52, 49, 3, 1, '2022-05-31 13:00:05', 'May', '2023', '2022-05-31 13:00:05', '2022-05-31 13:00:05', 0, 1, 1), (53, 49, 1, 1, '2022-05-31 13:00:21', 'Jun', '2023', '2022-05-31 13:00:21', '2022-06-01 01:12:40', 0, 1, 2), (54, 49, 2, 1, '2022-05-31 13:00:32', 'Jun', '2023', '2022-05-31 13:00:32', '2022-06-01 01:12:40', 0, 1, 1), (55, 49, 3, 1, '2022-05-31 13:00:43', 'Jun', '2023', '2022-05-31 13:00:43', '2022-06-01 01:12:40', 0, 1, 1), (56, 88, 1, 1, '2022-05-31 13:01:25', 'May', '2022', '2022-05-31 13:01:25', '2022-05-31 13:01:25', 0, 1, 2), (57, 88, 2, 1, '2022-05-31 13:01:30', 'May', '2022', '2022-05-31 13:01:30', '2022-05-31 13:01:30', 0, 1, 1), (58, 88, 3, 1, '2022-05-31 13:01:37', 'May', '2022', '2022-05-31 13:01:37', '2022-05-31 13:01:37', 0, 1, 1), (59, 88, 1, 1, '2022-05-31 13:01:45', 'Jun', '2022', '2022-05-31 13:01:45', '2022-06-01 01:27:00', 0, 1, 2), (60, 88, 2, 1, '2022-05-31 13:01:52', 'Jun', '2022', '2022-05-31 13:01:52', '2022-06-01 01:12:40', 0, 1, 1), (62, 88, 1, 1, '2022-05-31 13:02:37', 'Jun', '2023', '2022-05-31 13:02:37', '2022-06-01 01:27:07', 0, 1, 2), (63, 88, 2, 1, '2022-05-31 13:02:54', 'Jun', '2023', '2022-05-31 13:02:54', '2022-06-01 01:12:40', 0, 1, 1), (64, 88, 3, 1, '2022-05-31 13:03:04', 'Jun', '2023', '2022-05-31 13:03:04', '2022-06-01 01:27:10', 0, 1, 1), (65, 88, 1, 1, '2022-05-31 13:03:14', 'May', '2023', '2022-05-31 13:03:14', '2022-05-31 13:03:14', 0, 1, 2), (66, 88, 2, 1, '2022-05-31 13:03:22', 'May', '2023', '2022-05-31 13:03:22', '2022-05-31 13:03:22', 0, 1, 1), (67, 88, 3, 1, '2022-05-31 13:03:31', 'May', '2023', '2022-05-31 13:03:31', '2022-05-31 13:03:31', 0, 1, 1), (68, 89, 1, 1, '2022-05-31 13:04:21', 'May', '2022', '2022-05-31 13:04:21', '2022-05-31 13:04:21', 0, 1, 2), (69, 89, 2, 1, '2022-05-31 13:04:34', 'May', '2022', '2022-05-31 13:04:34', '2022-05-31 13:04:34', 0, 1, 1), (70, 89, 3, 1, '2022-05-31 13:04:40', 'May', '2022', '2022-05-31 13:04:40', '2022-05-31 13:04:40', 0, 1, 1), (71, 89, 1, 1, '2022-05-31 13:04:49', 'Jun', '2022', '2022-05-31 13:04:49', '2022-06-01 01:12:40', 0, 1, 2), (72, 89, 2, 1, '2022-05-31 13:04:56', 'Jun', '2022', '2022-05-31 13:04:56', '2022-06-01 01:12:40', 0, 1, 1), (73, 89, 3, 1, '2022-05-31 13:05:14', 'Jun', '2022', '2022-05-31 13:05:14', '2022-06-01 01:10:41', 0, 1, 1), (74, 89, 1, 1, '2022-05-31 13:05:24', 'May', '2023', '2022-05-31 13:05:24', '2022-05-31 13:05:24', 0, 1, 2), (75, 89, 2, 1, '2022-05-31 13:05:31', 'May', '2023', '2022-05-31 13:05:31', '2022-05-31 13:05:31', 0, 1, 1), (76, 89, 3, 1, '2022-05-31 13:05:41', 'May', '2023', '2022-05-31 13:05:41', '2022-05-31 13:05:41', 0, 1, 1), (77, 89, 1, 1, '2022-05-31 13:05:51', 'Jun', '2023', '2022-05-31 13:05:51', '2022-06-01 01:12:40', 0, 1, 2), (78, 89, 2, 1, '2022-05-31 13:05:59', 'Jun', '2023', '2022-05-31 13:05:59', '0000-00-00 00:00:00', 0, 1, 1), (79, 89, 3, 1, '2022-05-31 13:06:09', 'Jun', '2023', '2022-05-31 13:06:09', '2022-06-01 01:27:12', 0, 1, 1), (80, 84, 1, 1, '2022-05-31 13:07:19', 'May', '2022', '2022-05-31 13:07:19', '2022-06-01 03:40:25', 0, 1, 2), (81, 84, 2, 1, '2022-05-31 13:07:24', 'May', '2022', '2022-05-31 13:07:24', '2022-05-31 13:07:24', 0, 1, 1), (82, 84, 3, 1, '2022-05-31 13:07:31', 'May', '2022', '2022-05-31 13:07:31', '2022-06-01 03:42:52', 0, 1, 1), (83, 84, 1, 1, '2022-05-31 13:07:38', 'Jun', '2022', '2022-05-31 13:07:38', '2022-06-01 01:12:40', 0, 1, 2), (84, 84, 2, 1, '2022-05-31 13:07:45', 'Jun', '2022', '2022-05-31 13:07:45', '2022-06-01 01:27:14', 0, 1, 1), (85, 84, 3, 1, '2022-05-31 13:07:53', 'Jun', '2022', '2022-05-31 13:07:53', '2022-06-01 01:27:16', 0, 1, 1), (86, 84, 1, 1, '2022-05-31 13:08:12', 'May', '2023', '2022-05-31 13:08:12', '2022-05-31 13:08:12', 0, 1, 2), (87, 84, 2, 1, '2022-05-31 13:08:23', 'May', '2023', '2022-05-31 13:08:23', '2022-05-31 13:08:23', 0, 1, 1), (88, 84, 3, 1, '2022-05-31 13:08:30', 'May', '2023', '2022-05-31 13:08:30', '2022-05-31 13:08:30', 0, 1, 1), (89, 84, 1, 1, '2022-05-31 13:08:44', 'Jun', '2023', '2022-05-31 13:08:44', '2022-06-01 01:27:20', 0, 1, 2), (90, 84, 2, 1, '2022-05-31 13:08:58', 'Jun', '2023', '2022-05-31 13:08:58', '2022-06-01 01:27:22', 0, 1, 1), (91, 84, 3, 1, '2022-05-31 13:09:06', 'Jun', '2023', '2022-05-31 13:09:06', '2022-06-01 01:27:26', 0, 1, 1), (92, 121, 1, 1, '2022-05-31 13:09:59', 'May', '2022', '2022-05-31 13:09:59', '2022-05-31 13:09:59', 0, 1, 2), (93, 121, 2, 1, '2022-05-31 13:10:06', 'May', '2022', '2022-05-31 13:10:06', '2022-05-31 13:10:06', 0, 1, 1), (94, 121, 3, 1, '2022-05-31 13:10:13', 'May', '2022', '2022-05-31 13:10:13', '2022-05-31 13:10:13', 0, 1, 1), (95, 121, 1, 1, '2022-05-31 13:10:20', 'Jun', '2022', '2022-05-31 13:10:20', '2022-06-01 01:27:27', 0, 1, 2), (96, 121, 2, 1, '2022-05-31 13:10:29', 'Jun', '2022', '2022-05-31 13:10:29', '2022-06-01 01:27:29', 0, 1, 1), (97, 121, 3, 1, '2022-05-31 13:10:38', 'Jun', '2022', '2022-05-31 13:10:38', '2022-06-01 01:27:30', 0, 1, 1), (98, 121, 1, 1, '2022-05-31 13:10:50', 'May', '2023', '2022-05-31 13:10:50', '2022-05-31 13:10:50', 0, 1, 2), (99, 121, 2, 1, '2022-05-31 13:10:57', 'May', '2023', '2022-05-31 13:10:57', '2022-05-31 13:10:57', 0, 1, 1), (100, 121, 3, 1, '2022-05-31 13:11:11', 'May', '2023', '2022-05-31 13:11:11', '2022-05-31 13:11:11', 0, 1, 1), (101, 107, 1, 1, '2022-05-31 13:11:34', 'May', '2022', '2022-05-31 13:11:34', '2022-05-31 13:11:34', 0, 1, 2), (102, 107, 2, 1, '2022-05-31 13:11:39', 'May', '2022', '2022-05-31 13:11:39', '2022-05-31 13:11:39', 0, 1, 1), (103, 107, 3, 1, '2022-05-31 13:11:44', 'May', '2022', '2022-05-31 13:11:44', '2022-05-31 13:11:44', 0, 1, 1), (104, 107, 1, 1, '2022-05-31 13:11:51', 'Jun', '2022', '2022-05-31 13:11:51', '2022-06-01 01:10:41', 0, 1, 2), (105, 107, 2, 1, '2022-05-31 13:12:05', 'Jun', '2022', '2022-05-31 13:12:05', '2022-06-01 01:27:32', 0, 1, 1), (106, 107, 3, 1, '2022-05-31 13:12:14', 'Jun', '2022', '2022-05-31 13:12:14', '2022-06-01 07:57:46', 0, 1, 1), (107, 107, 1, 1, '2022-05-31 13:12:28', 'May', '2023', '2022-05-31 13:12:28', '2022-05-31 13:12:28', 0, 1, 2), (108, 107, 2, 1, '2022-05-31 13:12:37', 'May', '2023', '2022-05-31 13:12:37', '2022-05-31 13:12:37', 0, 1, 1), (109, 107, 3, 1, '2022-05-31 13:12:45', 'May', '2023', '2022-05-31 13:12:45', '2022-05-31 13:12:45', 0, 1, 1), (110, 107, 1, 1, '2022-05-31 13:12:56', 'Jun', '2023', '2022-05-31 13:12:56', '2022-06-01 01:27:36', 0, 1, 2), (111, 107, 2, 1, '2022-05-31 13:13:05', 'Jun', '2023', '2022-05-31 13:13:05', '2022-06-01 01:27:38', 0, 1, 1), (112, 107, 3, 1, '2022-05-31 13:17:28', 'Jun', '2023', '2022-05-31 13:17:28', '2022-06-01 01:27:40', 0, 1, 1), (113, 113, 1, 1, '2022-05-31 13:18:57', 'May', '2022', '2022-05-31 13:18:57', '2022-05-31 13:18:57', 0, 1, 1), (114, 113, 2, 1, '2022-05-31 13:19:04', 'May', '2022', '2022-05-31 13:19:04', '2022-05-31 13:19:04', 0, 1, 1), (115, 113, 3, 1, '2022-05-31 13:19:10', 'May', '2022', '2022-05-31 13:19:10', '2022-05-31 13:19:10', 0, 1, 1), (116, 113, 1, 1, '2022-05-31 13:19:18', 'Jun', '2022', '2022-05-31 13:19:18', '2022-06-01 01:10:41', 0, 1, 1), (117, 113, 2, 1, '2022-05-31 13:19:24', 'Jun', '2022', '2022-05-31 13:19:24', '2022-06-01 01:27:41', 0, 1, 1), (118, 113, 3, 1, '2022-05-31 13:19:31', 'Jun', '2022', '2022-05-31 13:19:31', '2022-06-01 07:57:43', 0, 1, 1), (119, 113, 1, 1, '2022-05-31 13:19:41', 'May', '2023', '2022-05-31 13:19:41', '2022-05-31 13:19:41', 0, 1, 1), (120, 113, 2, 1, '2022-05-31 13:19:48', 'May', '2023', '2022-05-31 13:19:48', '2022-05-31 13:19:48', 0, 1, 1), (121, 113, 3, 1, '2022-05-31 13:19:54', 'May', '2023', '2022-05-31 13:19:54', '2022-05-31 13:19:54', 0, 1, 1), (122, 113, 1, 1, '2022-05-31 13:20:04', 'Jun', '2023', '2022-05-31 13:20:04', '2022-06-01 01:27:45', 0, 1, 1), (123, 113, 2, 1, '2022-05-31 13:20:13', 'Jun', '2023', '2022-05-31 13:20:13', '2022-06-01 01:27:49', 0, 1, 1), (124, 113, 3, 1, '2022-05-31 13:20:22', 'Jun', '2023', '2022-05-31 13:20:22', '2022-06-01 01:27:53', 0, 1, 1), (125, 119, 1, 1, '2022-05-31 13:21:12', 'May', '2022', '2022-05-31 13:21:12', '2022-05-31 13:21:12', 0, 1, 1), (126, 119, 2, 1, '2022-05-31 13:21:18', 'May', '2022', '2022-05-31 13:21:18', '2022-05-31 13:21:18', 0, 1, 1), (127, 119, 3, 1, '2022-05-31 13:21:29', 'May', '2022', '2022-05-31 13:21:29', '2022-05-31 13:21:29', 0, 1, 1), (128, 119, 1, 1, '2022-05-31 13:21:45', 'Jun', '2022', '2022-05-31 13:21:45', '2022-06-01 01:27:54', 0, 1, 1), (129, 119, 2, 1, '2022-05-31 13:21:53', 'Jun', '2022', '2022-05-31 13:21:53', '2022-06-01 01:27:56', 0, 1, 1), (130, 119, 3, 1, '2022-05-31 13:22:16', 'Jun', '2022', '2022-05-31 13:22:16', '2022-06-01 01:28:00', 0, 1, 1), (131, 119, 1, 1, '2022-05-31 13:22:24', 'May', '2023', '2022-05-31 13:22:24', '2022-05-31 13:22:24', 0, 1, 1), (132, 119, 2, 1, '2022-05-31 13:22:30', 'May', '2023', '2022-05-31 13:22:30', '2022-05-31 13:22:30', 0, 1, 1), (133, 119, 3, 1, '2022-05-31 13:22:36', 'May', '2023', '2022-05-31 13:22:36', '2022-05-31 13:22:36', 0, 1, 1), (134, 119, 1, 1, '2022-05-31 13:22:45', 'Jun', '2023', '2022-05-31 13:22:45', '2022-06-01 01:28:03', 0, 1, 1), (135, 119, 2, 1, '2022-05-31 13:22:53', 'Jun', '2023', '2022-05-31 13:22:53', '2022-06-01 01:28:04', 0, 1, 1), (136, 119, 3, 1, '2022-05-31 13:23:10', 'Jun', '2023', '2022-05-31 13:23:10', '2022-06-01 01:28:08', 0, 1, 1), (137, 13, 1, 1, '2022-05-31 13:40:26', 'May', '2022', '2022-05-31 13:40:26', '2022-05-31 13:40:26', 0, 1, 1), (138, 13, 2, 1, '2022-05-31 13:40:31', 'May', '2022', '2022-05-31 13:40:31', '2022-05-31 13:40:31', 0, 1, 1), (139, 13, 3, 1, '2022-05-31 13:40:36', 'May', '2022', '2022-05-31 13:40:36', '2022-05-31 13:40:36', 0, 1, 1), (140, 13, 1, 1, '2022-05-31 13:40:42', 'Jun', '2022', '2022-05-31 13:40:42', '2022-06-01 01:28:10', 0, 1, 1), (141, 13, 2, 1, '2022-05-31 13:40:53', 'Jun', '2022', '2022-05-31 13:40:53', '2022-06-01 01:28:11', 0, 1, 1), (142, 13, 3, 1, '2022-05-31 13:41:11', 'Jun', '2022', '2022-05-31 13:41:11', '2022-06-01 03:41:46', 0, 1, 1); -- -- Indexes for dumped tables -- -- -- Indexes for table `attendance` -- ALTER TABLE `attendance` ADD PRIMARY KEY (`atid`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `attendance` -- ALTER TABLE `attendance` MODIFY `atid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=143; COMMIT; /*!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 */; I followed this code below and it has different results and can't really do it myself, <?php if(isset($_POST['search'])) { $search = $_POST['search'] ?? ''; $year = $_POST['year'] ?? ''; $month = $_POST['month'] ?? ''; $sql = "SELECT DISTINCT entrydate FROM attendance ORDER BY entrydate "; $res = $link->query($sql); // mysqli query while ($row = $res->fetch_row()) { $dates[] = $row[0]; } /*********************************** * Table headings * ************************************/ $emptyRow = array_fill_keys($dates,''); // format dates foreach ($dates as $k=>$v) { $dates[$k] = date('d-M', strtotime($v)); } $heads = "<table class='table table-stripped table-bordered'>\n"; $heads .= "<tr><th>Name</th><th>" . join('</th><th>', $dates) . "</th></tr>\n"; /*********************************** * Main data * ************************************/ $sql = "SELECT entrydate, memid, astatus FROM attendance ORDER BY memid"; $res = $link->query($sql); $curname=''; $tdata = ''; while (list($d, $sn, $s) = $res->fetch_row()) { if ($curname != $sn) { if ($curname) { $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n"; } $rowdata = $emptyRow; $curname = $sn; } $rowdata[$d] = $s; } $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n"; $tdata .= "</table\n"; } ?> <?php echo $heads; echo $tdata; ?> Notes: memid = Member name serid = Services like (PM, PBB, WS) calid = 1st Week, 2nd Week and so-on. entrydate = TimeIn date. Hoping that there are someone who's able to help a newbie like me. Thank you and appreciate that.

Hello gang, I'm looking for help with my query SELECT IFNULL(worker_id,'Totals') AS Operator, -- outer query labels rollup row sums.2012-11-26, sums.2012-11-27, sums.2012-11-28, -- and calculates horizontal sums sums.2012-11-26 + sums.2012-11-27 + sums.2012-11-28 AS Sums FROM ( -- inner query groups by employee SELECT -- with an expression for each column worker_id, SUM(IF(production_date=2012-11-26,production_net,0)) As '11-26-2012', SUM(IF(production_date=2012-11-27,production_net,0)) As '11-27-2012', SUM(IF(production_date=2012-11-28,production_net,0)) As '11-28-2012' FROM production GROUP BY worker_id WITH ROLLUP ) AS sums This is my attempt to mimic the logic found here http://www.artfulsof...rytip.php?id=78 What I keep getting is errors like this "Unknown column 'sums.2012-11-26' in 'field list'" Which usually I assumed, meant I had not properly encapsulated the header names so I tried (sums.'2012-11-26' and 'sums.'2012-11-26' as well as variations with back ticks and no luck whatsoever. Any help is very much appreciated.. This is how the data is stored in the database id |production_date| production_net| worker_id -------------------------------------------------- 1 |2013-01-10 | 390 | MMahe -------------------------------------------------- 2 |2013-01-10 | 400 | RMaloney -------------------------------------------------- 3 |2013-01-11 | 460 | JBurris -------------------------------------------------- 4 |2013-01-11 | 210 | MMahe -------------------------------------------------- 5 |2013-01-14 | 285 | LTaylor -------------------------------------------------- I'm wanting the results to look something like this.. operator| 2013-01-10| 2013-01-11| 2013-01-14| Sums | ---------------------------------------------------- MMahe | 390 | 210 | | 600 | ---------------------------------------------------- RMaloney| 400 | | | 400 | ---------------------------------------------------- JBurris | | 460 | | 460 | ---------------------------------------------------- LTaylor | | | 285 | 285 | ---------------------------------------------------- Totals | 790 | 670 | 285 |1745 |

Hello I want to create attendance sheet on which date are printed as column and name of student/staff as column database is as shown CREATE TABLE IF NOT EXISTS `attendance` ( `date` varchar(500) DEFAULT NULL, `time` varchar(1000) DEFAULT NULL, `staffname` varchar(1000) DEFAULT NULL, `id` int(11) DEFAULT NULL, `role` varchar(1000) NOT NULL, `status` varchar(1) DEFAULT NULL ) but when I querying the table repeating the names of student and staff which not gives me report as expected here I attaching code also <table align="letf" style="margin-left: 0px; border: 1px solid black; border-spacing: 0px;" width="8"> <th style="border: 1px solid black; text-align: center;">Date</th> <?php $sql133="select distinct date from attendance"; $sql_row133=mysqli_query($dbConn,$sql133); while($sql_res133=mysqli_fetch_assoc($sql_row133)) { $date=$sql_res133["date"]; ?> <th style="border: 1px solid black; text-align: center;"> <?php echo $date; ?> </th> <?php $a=$date; $sql13=" SELECT atten.date,atten.time,atten.staffname,atten.id, atten.status, supst.id, supst.staffname FROM (examcenter.attendance atten INNER JOIN examcenter.supportstaff supst ON atten.id = supst.id) where atten.date='$a' group by supst.staffname,supst.id ORDER BY atten.id ASC "; $sql_row13=mysqli_query($dbConn,$sql13); while($sql_res13=mysqli_fetch_assoc($sql_row13)) { $staffname=$sql_res13["staffname"]; $status=$sql_res13["status"]; ?> <tr> <td><?php echo $staffname; ?></td> <td><?php echo $status; ?></td> <?php } } ?> </table> please guide what do to create a report as expected Name/Date 12-11-2013 13-11-2013 16-11-2013 Student name1 P A A Staffname 1 P P A awaiting valuable reply