Pivot Table or Cross Tab in PHP using MYSQLI for attendance

Zinn · June 6, 2022

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.

Edited June 6, 2022 by Zinn
add more details

Barand · June 6, 2022

I have recreated your attendance table and have run your code. I now know what you don't want. (109 columns labelled "31-May")

Perhaps you could tell us what you do want?

Zinn · June 7, 2022

Hi Sir @Barand

Here's the thing I want to achieve, and I do belived that the code you've recreated was your code as well way back 2014 from this post:

https://forums.phpfreaks.com/topic/285398-pivot-table-or-cross-tab-in-php-using-mysql-for-attendance/

I tried it and modified but it but it has different results than expected.

Here's the code:

                           <?php 
                           
                              if(isset($_POST['search']))
                              {
                                $search = $_POST['search'] ?? '';
                                $year = $_POST['year'] ?? '';
                                $month = $_POST['month'] ?? '';
                                
                                 $sql = "SELECT DISTINCT entrydate 
                                       FROM attendance where stype = 1 and year='$year' and month = '$month'
                                       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 border='1'>\n";
                                 $heads .= "<tr><th>Name</th><th>" . join('</th><th>', $dates) . "</th></tr>\n";

                                 /***********************************
                                 * Main data                        *
                                 ************************************/
                                 $sql = "SELECT entrydate, memid, astatus
                                       FROM attendance
                                       where stype = 1 and year='$year' and month = '$month'
                                       ORDER BY memid";
                                 $res = $link->query($sql);
                                 $curname='';
                                 $tdata = '';
                                 $rowdata = '';
                                 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";
                                                               
                              }
                                 ?>

                              </table>
                              <?php
                                 echo $heads;
                                 echo $tdata;
                              ?>

And here's the result:

But I want to acheived like belo w that Entrydate is supposed to be dynamic and placed as a Column header for table.

bymonth.PNG.583f26b8ad2779bf36717e0c852f6357.PNG

Notes:

memid = Member name
serid = Services like (PM, PBB, WS)
calid = 1st Week, 2nd Week and so-on.
entrydate = TimeIn date.

Thank you and hope you could help.

Barand · June 7, 2022

There is no "Group #" in your data - does that come from a member table along with the name?

image.png.f4450ec09183dcbb5506a3fc6076886c.png

Are those the combinations of date/serid that occur in the data? (So if there 7 services D1/D2/D3/PM/WS/PBB/LS there could potentially be up to 49 columns for each week, or can a serid occur a maximium of once per week?).

Dynamic headings created from your supplied test data would be

image.png.d0b2f1e860d7290aaa5db2f463a8dafd.png

What is the purpose of calid column? The date gives you the week number. (Same goes for your month and year columns)

Zinn · June 7, 2022

Hi Sir @Barand

Appreciate your help and feedback.

Yes the Group # is in member table along with the name, but I did not put the GID or Groupid in attendance table.

calid is the Calendar ID which are (1st week / 2nd Week / 3rd week and etc)

WS - Stand as worship service every Saturday morning
PM - Stand as Prayer meeting every Wednesday
PBB - Stand as Thanks Giving every saturday Night.

However D1/D2/D3 - Will only happen every quarter of the year, so in one year (it's a 3 days thanksgiving)

So Serid or Service ID will only occur once a week (serid here is join from Service table)

image.png.1c06208da5026310c868a80a852948a5.png Yes this can be a potential output, however the date here is not exacly all on May 31 cause I've added these data on May 31 that date.

Zinn · June 7, 2022

Hi Sir @Barand

By the way each week, there's only potential 3 colums for services (PM/WS/PBB); but since a month contains 5 weeks sometimes, there's are 18 or less expected columns every month.

Except for D1/D2/D3 cause this will happen only every quarter of the month.

For LS just don't mind.

Thank you so much sir.

Zinn · June 8, 2022

Hi Sir @Barand

I've added the Group already on my database and deleted some fields on it.

-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jun 08, 2022 at 11:27 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,
  `grid` varchar(10) CHARACTER SET latin1 NOT NULL,
  `entrydate` timestamp NOT NULL DEFAULT current_timestamp(),
  `month` varchar(30) CHARACTER SET latin1 NOT NULL,
  `year` varchar(5) DEFAULT NULL,
  `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`, `grid`, `entrydate`, `month`, `year`, `updatedat`, `viewfrom`, `astatus`, `stype`) VALUES
(32, 37, 1, 1, '', '2022-05-31 12:55:28', 'May', '2022', '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', 0, 1, 1),
(34, 37, 3, 1, '', '2022-05-31 12:55:43', 'May', '2022', '2022-06-01 03:28:55', 0, 1, 1),
(35, 37, 1, 0, '', '2022-05-31 12:56:15', 'Jun', '2022', '2022-06-01 01:17:35', 0, 1, 2),
(36, 37, 2, 1, '', '2022-05-31 12:56:25', 'Jun', '2022', '2022-06-01 01:17:35', 0, 1, 1),
(37, 37, 3, 1, '', '2022-05-31 12:56:40', 'Jun', '2022', '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', 0, 1, 2),
(39, 37, 2, 1, '', '2022-05-31 12:57:37', 'May', '2023', '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', 0, 1, 1),
(41, 37, 1, 1, '', '2022-05-31 12:57:57', 'Jun', '2023', '2022-06-01 01:17:35', 0, 1, 2),
(42, 37, 2, 1, '', '2022-05-31 12:58:08', 'Jun', '2023', '2022-06-01 01:17:35', 0, 1, 1),
(43, 37, 3, 1, '', '2022-05-31 12:58:18', 'Jun', '2023', '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', 0, 1, 2),
(45, 49, 2, 1, '', '2022-05-31 12:58:40', 'May', '2022', '2022-05-31 12:58:40', 0, 1, 1),
(47, 49, 1, 1, '', '2022-05-31 12:59:01', 'Jun', '2022', '0000-00-00 00:00:00', 0, 1, 2),
(48, 49, 2, 1, '', '2022-05-31 12:59:14', 'Jun', '2022', '2022-06-01 01:12:40', 0, 1, 1),
(49, 49, 3, 1, '', '2022-05-18 12:59:31', 'Jun', '2022', '2022-06-06 08:45:28', 0, 1, 1),
(50, 49, 1, 1, '', '2022-05-31 12:59:41', 'May', '2023', '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', 0, 1, 1),
(52, 49, 3, 1, '', '2022-05-31 13:00:05', 'May', '2023', '2022-05-31 13:00:05', 0, 1, 1),
(53, 49, 1, 1, '', '2022-05-31 13:00:21', 'Jun', '2023', '2022-06-01 01:12:40', 0, 1, 2),
(54, 49, 2, 1, '', '2022-05-31 13:00:32', 'Jun', '2023', '2022-06-01 01:12:40', 0, 1, 1),
(55, 49, 3, 1, '', '2022-05-31 13:00:43', 'Jun', '2023', '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', 0, 1, 2),
(57, 88, 2, 1, '', '2022-05-31 13:01:30', 'May', '2022', '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', 0, 1, 1),
(59, 88, 1, 1, '', '2022-05-31 13:01:45', 'Jun', '2022', '2022-06-01 01:27:00', 0, 1, 2),
(60, 88, 2, 1, '', '2022-05-31 13:01:52', 'Jun', '2022', '2022-06-01 01:12:40', 0, 1, 1),
(62, 88, 1, 1, '', '2022-05-31 13:02:37', 'Jun', '2023', '2022-06-01 01:27:07', 0, 1, 2),
(63, 88, 2, 1, '', '2022-05-31 13:02:54', 'Jun', '2023', '2022-06-01 01:12:40', 0, 1, 1),
(64, 88, 3, 1, '', '2022-05-31 13:03:04', 'Jun', '2023', '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', 0, 1, 2),
(66, 88, 2, 1, '', '2022-05-31 13:03:22', 'May', '2023', '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', 0, 1, 1),
(68, 89, 1, 1, '', '2022-05-31 13:04:21', 'May', '2022', '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', 0, 1, 1),
(70, 89, 3, 1, '', '2022-05-31 13:04:40', 'May', '2022', '2022-05-31 13:04:40', 0, 1, 1),
(71, 89, 1, 1, '', '2022-05-31 13:04:49', 'Jun', '2022', '2022-06-01 01:12:40', 0, 1, 2),
(72, 89, 2, 1, '', '2022-05-31 13:04:56', 'Jun', '2022', '2022-06-01 01:12:40', 0, 1, 1),
(73, 89, 3, 1, '', '2022-05-18 13:05:14', 'Jun', '2022', '2022-06-06 08:45:15', 0, 1, 1),
(74, 89, 1, 1, '', '2022-05-31 13:05:24', 'May', '2023', '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', 0, 1, 1),
(76, 89, 3, 1, '', '2022-05-31 13:05:41', 'May', '2023', '2022-05-31 13:05:41', 0, 1, 1),
(77, 89, 1, 1, '', '2022-05-31 13:05:51', 'Jun', '2023', '2022-06-01 01:12:40', 0, 1, 2),
(78, 89, 2, 1, '', '2022-05-31 13:05:59', 'Jun', '2023', '0000-00-00 00:00:00', 0, 1, 1),
(79, 89, 3, 1, '', '2022-05-31 13:06:09', 'Jun', '2023', '2022-06-01 01:27:12', 0, 1, 1),
(80, 84, 1, 1, '', '2022-05-31 13:07:19', 'May', '2022', '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', 0, 1, 1),
(82, 84, 3, 1, '', '2022-05-31 13:07:31', 'May', '2022', '2022-06-01 03:42:52', 0, 1, 1),
(83, 84, 1, 1, '', '2022-05-31 13:07:38', 'Jun', '2022', '2022-06-01 01:12:40', 0, 1, 2),
(84, 84, 2, 1, '', '2022-05-31 13:07:45', 'Jun', '2022', '2022-06-01 01:27:14', 0, 1, 1),
(85, 84, 3, 1, '', '2022-05-31 13:07:53', 'Jun', '2022', '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', 0, 1, 2),
(87, 84, 2, 1, '', '2022-05-31 13:08:23', 'May', '2023', '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', 0, 1, 1),
(89, 84, 1, 1, '', '2022-05-31 13:08:44', 'Jun', '2023', '2022-06-01 01:27:20', 0, 1, 2),
(90, 84, 2, 1, '', '2022-05-31 13:08:58', 'Jun', '2023', '2022-06-01 01:27:22', 0, 1, 1),
(91, 84, 3, 1, '', '2022-05-31 13:09:06', 'Jun', '2023', '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', 0, 1, 2),
(93, 121, 2, 1, '', '2022-05-31 13:10:06', 'May', '2022', '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', 0, 1, 1),
(95, 121, 1, 1, '', '2022-05-31 13:10:20', 'Jun', '2022', '2022-06-01 01:27:27', 0, 1, 2),
(96, 121, 2, 1, '', '2022-05-31 13:10:29', 'Jun', '2022', '2022-06-01 08:54:39', 1, 1, 1),
(97, 121, 3, 1, '', '2022-05-31 13:10:38', 'Jun', '2022', '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', 0, 1, 2),
(99, 121, 2, 1, '', '2022-05-31 13:10:57', 'May', '2023', '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', 0, 1, 1),
(101, 107, 1, 1, '', '2022-05-31 13:11:34', 'May', '2022', '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', 0, 1, 1),
(103, 107, 3, 1, '', '2022-05-31 13:11:44', 'May', '2022', '2022-05-31 13:11:44', 0, 1, 1),
(104, 107, 1, 1, '', '2022-05-31 13:11:51', 'Jun', '2022', '2022-06-01 01:10:41', 0, 1, 2),
(105, 107, 2, 1, '', '2022-05-31 13:12:05', 'Jun', '2022', '2022-06-01 01:27:32', 0, 1, 1),
(106, 107, 3, 1, '', '2022-05-28 13:12:14', 'Jun', '2022', '2022-06-06 08:45:05', 0, 1, 1),
(107, 107, 1, 1, '', '2022-05-31 13:12:28', 'May', '2023', '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', 0, 1, 1),
(109, 107, 3, 1, '', '2022-05-31 13:12:45', 'May', '2023', '2022-05-31 13:12:45', 0, 1, 1),
(110, 107, 1, 1, '', '2022-05-31 13:12:56', 'Jun', '2023', '2022-06-01 01:27:36', 0, 1, 2),
(111, 107, 2, 1, '', '2022-05-31 13:13:05', 'Jun', '2023', '2022-06-01 01:27:38', 0, 1, 1),
(112, 107, 3, 1, '', '2022-05-31 13:17:28', 'Jun', '2023', '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', 0, 1, 1),
(114, 113, 2, 1, '', '2022-05-31 13:19:04', 'May', '2022', '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', 0, 1, 1),
(116, 113, 1, 1, '', '2022-05-31 13:19:18', 'Jun', '2022', '2022-06-01 01:10:41', 0, 1, 1),
(117, 113, 2, 1, '', '2022-05-31 13:19:24', 'Jun', '2022', '2022-06-01 01:27:41', 0, 1, 1),
(118, 113, 3, 1, '', '2022-05-31 13:19:31', 'Jun', '2022', '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', 0, 1, 1),
(120, 113, 2, 1, '', '2022-05-31 13:19:48', 'May', '2023', '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', 0, 1, 1),
(122, 113, 1, 1, '', '2022-05-31 13:20:04', 'Jun', '2023', '2022-06-01 01:27:45', 0, 1, 1),
(123, 113, 2, 1, '', '2022-05-31 13:20:13', 'Jun', '2023', '2022-06-01 01:27:49', 0, 1, 1),
(124, 113, 3, 1, '', '2022-05-31 13:20:22', 'Jun', '2023', '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', 0, 1, 1),
(126, 119, 2, 1, '', '2022-05-31 13:21:18', 'May', '2022', '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', 0, 1, 1),
(128, 119, 1, 1, '', '2022-05-31 13:21:45', 'Jun', '2022', '2022-06-01 01:27:54', 0, 1, 1),
(129, 119, 2, 1, '', '2022-05-31 13:21:53', 'Jun', '2022', '2022-06-01 01:27:56', 0, 1, 1),
(130, 119, 3, 1, '', '2022-05-31 13:22:16', 'Jun', '2022', '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', 0, 1, 1),
(132, 119, 2, 1, '', '2022-05-31 13:22:30', 'May', '2023', '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', 0, 1, 1),
(134, 119, 1, 1, '', '2022-05-31 13:22:45', 'Jun', '2023', '2022-06-01 01:28:03', 0, 1, 1),
(135, 119, 2, 1, '', '2022-05-31 13:22:53', 'Jun', '2023', '2022-06-01 01:28:04', 0, 1, 1),
(136, 119, 3, 1, '', '2022-05-31 13:23:10', 'Jun', '2023', '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', 0, 1, 1),
(138, 13, 2, 1, '', '2022-05-31 13:40:31', 'May', '2022', '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', 0, 1, 1),
(140, 13, 1, 1, '', '2022-05-31 13:40:42', 'Jun', '2022', '2022-06-01 01:28:10', 0, 1, 1),
(141, 13, 2, 1, '', '2022-05-31 13:40:53', 'Jun', '2022', '2022-06-01 01:28:11', 0, 1, 1),
(142, 13, 3, 1, '', '2022-05-27 13:41:11', 'Jun', '2022', '2022-06-06 08:44:57', 0, 1, 1),
(143, 116, 2, 1, '', '2022-06-08 02:15:40', 'Jun', '2022', '2022-06-08 03:33:25', 0, 1, 2),
(144, 47, 2, 1, '', '2022-06-08 02:37:21', 'Jun', '2022', '2022-06-08 03:33:21', 0, 1, 3),
(145, 50, 2, 1, '', '2022-06-08 02:39:45', 'Jun', '2022', '2022-06-08 03:33:19', 0, 1, 3),
(146, 104, 2, 1, '', '2022-06-08 02:46:37', 'Jun', '2022', '2022-06-08 03:33:17', 0, 1, 3),
(147, 27, 2, 1, '', '2022-06-08 03:16:47', 'Jun', '2022', '2022-06-08 03:33:14', 0, 1, 3),
(148, 104, 1, 1, '', '2022-06-08 03:32:02', 'Jun', '2022', '2022-06-08 03:32:02', 0, 1, 2),
(149, 28, 2, 1, '', '2022-06-08 03:34:00', 'Jun', '2022', '2022-06-08 03:34:00', 0, 1, 1),
(150, 106, 3, 1, '', '2022-06-08 03:34:53', 'Jun', '2022', '2022-06-08 03:34:53', 0, 1, 1),
(151, 99, 1, 1, '', '2022-06-08 03:36:44', 'Jun', '2022', '2022-06-08 03:36:53', 1, 1, 2),
(152, 76, 2, 1, '', '2022-06-08 03:50:48', 'Jun', '2022', '2022-06-08 03:50:48', 0, 1, 1),
(153, 28, 1, 1, '', '2022-06-08 04:19:41', 'Jun', '2022', '2022-06-08 04:19:41', 0, 1, 2),
(155, 106, 1, 1, '', '2022-06-08 05:46:10', 'Jun', '2022', '2022-06-08 05:46:10', 0, 1, 2),
(156, 77, 2, 1, 'G7', '2022-06-08 06:07:28', 'Jun', '2022', '2022-06-08 06:07:28', 0, 1, 1),
(157, 102, 2, 1, 'G9', '2022-06-08 09:27:22', 'Jun', '2022', '2022-06-08 09:27:22', 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=158;
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 */;

Just for your reference.

Thank you very much for your help and well appreciated.

Barand · June 8, 2022

Here's my attempt.

This the data I used. Some of my column names may differ from yours, so adjust the queries accordingly.

attendance                          member                               service
+-----------+-----------+           +----------+-------------+           +--------------+-------------+
| Field     | Type      |           | Field    | Type        |           | Field        | Type        |
+-----------+-----------+           +----------+-------------+           +--------------+-------------+
| atid      | int(11)   |           | memid    | int(11)     |           | serid        | int(11)     |
| memid     | int(11)   |           | mem_name | varchar(45) |           | service_name | varchar(45) |
| serid     | int(11)   |           | group_no | int(11)     |           | service_code | varchar(3)  |
| entrydate | timestamp |           +----------+-------------+           +--------------+-------------+
| createdat | timestamp |
| updatedat | timestamp |
| viewfrom  | int(11)   |
| astatus   | int(11)   |
| stype     | int(11)   |
+-----------+-----------+

Output

Code

<?php
include 'db_inc.php';        ###  CREATE YOUR OWN
$pdo = pdoConnect('test');   ###  PDO CONNCTION HERE

$month = $_GET['month'] ?? date('n')-1;
$year  = $_GET['year'] ?? date('Y');
$day1 = "{$year}-{$month}-01";
$dt = new DateTime($day1);
$monthname = $dt->format('F');

################################################################################
#                                                                              #
#  BUILD REPORT HEADINGS FOR SELECTED MONTH                                    #
#                                                                              #
################################################################################

$res = $pdo->prepare("SELECT DISTINCT
                           weekofyear(entrydate) - weekofyear(?) + 1 as wk_no
                         , date_format(entrydate, '%e/%c') as edate
                         , concat(date_format(entrydate, '%d'), ' ', service_code) as dayser
                         , service_code
                      FROM attendance
                         JOIN service s USING (serid)
                      WHERE month(entrydate) = ?
                      ORDER BY entrydate, serid
                     ");
$res->execute([ $day1, $month ]);

$rows = $res->fetchAll();
$empty = array_fill_keys(array_column($rows, 'dayser'), [ 'wk'=>0, 'att'=>0 ]);
$hdata = [];
foreach ($rows as $r) {
    if (!isset($hdata[$r['wk_no']])) {
        $hdata[$r['wk_no']] = [ 'dates' => [],
                                'servs' => []
                              ];
    }
    $hdata[$r['wk_no']]['dates'][] = $r['edate'];
    $hdata[$r['wk_no']]['servs'][] = $r['service_code'];
    $empty[$r['dayser']]['wk'] = $r['wk_no'];
}

$thead = "<tr><th rowspan='3' class='namecol'>Name</th>";
foreach ($hdata as $w => $wdata) {
    $cs = count($wdata['dates']);
    $cls = $w % 2 ? 'oddwk' : '';
    $thead .= "<th class='$cls' colspan='$cs'>WEEK $w</th>";
}
$thead .= "</tr>\n<tr>";
foreach ($hdata as $w => $wdata) {
    foreach ($wdata['dates'] as $d) {
        $cls = $w % 2 ? 'oddwk' : '';
        $thead .= "<th class='$cls'>$d</th>";
    }
}
$thead .= "</tr>\n<tr>";
foreach ($hdata as $w => $wdata) {
    foreach ($wdata['servs'] as $d) {
        $cls = $w % 2 ? 'oddgrn' : 'evengrn';
        $thead .= "<th class='$cls'>$d</th>";
    }
}
$thead .= "</tr>\n";

################################################################################
#                                                                              #
#  BUILD DATA ROWS FOR SELECTED MONTH                                          #
#                                                                              #
################################################################################

$res = $pdo->prepare("SELECT 
                               weekofyear(entrydate) - weekofyear(?) + 1 as wk_no
                             , concat(date_format(entrydate, '%d'), ' ', service_code) as dayser
                             , group_no
                             , mem_name
                             , memid
                        FROM attendance a 
                             JOIN member m USING (memid)
                             JOIN service s USING (serid)
                        WHERE month(entrydate) = ?
                        ORDER BY group_no, mem_name, dayser
                        ");
$res->execute([ $day1, $month ]);
$mdata = [];
foreach ($res as $r) {
    if (!isset($mdata[$r['group_no']][$r['mem_name']])) {
        $mdata[$r['group_no']][$r['mem_name']] = $empty;
    }
    $mdata[$r['group_no']][$r['mem_name']][$r['dayser']]['att'] = 1;
}

$tdata = '';
foreach ($mdata as $grp => $mems) {
    $cs = count($empty);
    $tdata .= "<tr><td class='w3-brown'>Group $grp</td>
                  <td colspan='$cs'>&nbsp;</td>
               </tr>    
              ";
    foreach ($mems as $mname => $attend) {
        $tdata .= "<tr><td>$mname</td>";
        $prevwk = 0;
        foreach ($attend as $a) {
            $bar = $a['wk'] != $prevwk ? 'w3-leftbar' : '';
            $prevwk = $a['wk'];
            $cls = $bar;
            if ($a['att']) {
                $clr = $a['wk'] % 2 ? 'oddgrn' : 'evengrn';
                $cls .= " check $clr";
                $tdata .= "<td class='$cls'>&check;</td>";
            }
            else $tdata .= "<td class='$cls'>&nbsp;</td>";
        }
        $tdata .= "</tr>\n";
    }        
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Attendances</title>
<link href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> 
<style type='text/css'>
    table {
        border-collapse: collapse;
        width: 100%;
    }
    caption {
        text-transform: uppercase;
        font-size: 14pt;
    }
    th {
        padding: 4px 0;
        width: 50px;
    }
    td {
        padding: 4px;
    }
    th.oddwk {
        background-color: #eee;
    }
    th.namecol {
        width: 150px;
    }
    .oddgrn {
        background-color: #80ff80;
    }
    .evengrn {
        background-color: #b3ffb3;
    }
    .check {
        font-weight: 600;
        text-align: center;
    }
</style>
</head>
<body>
    <header class='w3-dark-gray w3-padding'>
        <h1>Attendance</h1>
    </header>
    <form class='w3-bar w3-light-gray w3-padding'>
        <label class='w3-bar-item'>Year </label>
        <input type='number' class='w3-bar-item'  name='year' value='<?=$year?>'>
        <label class='w3-bar-item'>Month</label> 
        <input type='number' class='w3-bar-item'  name='month' value='<?=$month?>'>
        <button class='w3-button w3-bar-item w3-indigo'>Search</button>
    </form>
    <div class='w3-content w3-margin-top'>
        <table border='1' class='w3-small'>
            <caption class='w3-light-green w3-padding w3-center'><?=$monthname?></caption>
            <?=$thead?>
            <?=$tdata?>
        </table>
    </div>

</body>
</html>

Zinn · June 8, 2022

Dear Sir @Barand

Thank you so much for your big big help.

Appreciate for your best help and solution on our headache as beginner.

I will update you once i've executed this query.

Thank you so much once again!

Best regards,

Edited June 8, 2022 by Zinn

Zinn · June 9, 2022

Hi Sir @Barand

You've got it right and your code really helps me a lot.

However is there a way to DISTINCT serid and the HEADER Date will based only on LIVE VIEWING?

So that every week there are only 3 services columns are being displayed.

On my table i have stype where if stype = 1 it is (LIVE) and stype = 2 (replay).

I tried GROUP_CONCAT but doesn't work well.

Hope you understand me and PDO for me is a new thing cause am used to MYSQLI.

Way more to go to learn.

Thank you so much for your patience.

Kind regards,

Barand · June 9, 2022

Can't you just add

AND stype = 1

to the WHERE clauses in the two queries?

Zinn · June 9, 2022

12 minutes ago, Barand said:
Can't you just add
AND stype = 1
to the WHERE clauses in the two queries?

Hi Sir @Barand, Yes sir I did but the results still the same.

serid = WS for example should only occupy 1 column regardless of the date they register attendance in a week.

Cause if user cannot join the Live worship they will join on the next day but replay.

image.png.223d8d4efa74cf9760be9b3a252060b6.png

Barand · June 9, 2022

3 hours ago, Zinn said:

I did but the results still the same.

I don't see how they can be. They may not be right but they must be different.

You stated that WS occured on Saturday mornings, so why would there be more than 1 per week? (each serid only appeared on one date in the test data you provided)

You came here with a problem and I helped you. Now a shedload of never before mentioned requirements and conditions suddenly appear. How many more will creep out of the woodwork? I don't like "mission creep" .

As well as only accepting live services (aforementioned where condition) you could also specify the day of week for each service

+-------+----------------+--------------+-----------+
| serid | service_name   | service_code | dayofweek |   1=Sun,..,7=Sat
+-------+----------------+--------------+-----------+
|     1 | Prayer meeting | PM           |         4 |
|     2 | Worship        | WS           |         7 |
|     3 | Thanks giving  | PBB          |         7 |
|     4 | LS             | LS           |      NULL |  unspecified
|     5 | Thanks giving  | D1           |      NULL |
|     6 | Thanks giving  | D2           |      NULL |
|     7 | Thanks giving  | D3           |      NULL |
+-------+----------------+--------------+-----------+

and only process dates/services that match those specified days. That will limit them to one per week. The two queries then become

-- 
-- headers query
-- 
SELECT DISTINCT
       weekofyear(entrydate) -  weekofyear(?) + 1 as wk_no
     , date_format(entrydate, '%e/%c') as edate
     , concat(date_format(entrydate, '%d'), ' ', service_code) as dayser
     , service_code
FROM attendance a 
     JOIN service s ON a.serid = s.serid
                    AND (dayofweek(a.entrydate) = s.dayofweek OR s.dayofweek IS NULL)
WHERE month(entrydate) = ?
      AND stype = 1
ORDER BY entrydate, a.serid;

-- 
-- member attendance query
-- 
SELECT 
       weekofyear(entrydate) - weekofyear(?) + 1 as wk_no
     , concat(date_format(entrydate, '%d'), ' ', service_code) as dayser
     , group_no
     , mem_name
     , memid
FROM attendance a 
     JOIN member m USING (memid)
     JOIN service s ON a.serid = s.serid
                    AND (dayofweek(a.entrydate) = s.dayofweek OR s.dayofweek IS NULL)
WHERE month(entrydate) = ?
      AND stype = 1
ORDER BY group_no, mem_name, dayser

Zinn · June 9, 2022

3 hours ago, Barand said:

I don't see how they can be. They may not be right but they must be different.

You stated that WS occured on Saturday mornings, so why would there be more than 1 per week? (each serid only appeared on one date in the test data you provided)

You came here with a problem and I helped you. Now a shedload of never before mentioned requirements and conditions suddenly appear. How many more will creep out of the woodwork? I don't like "mission creep" .

As well as only accepting live services (aforementioned where condition) you could also specify the day of week for each service

+-------+----------------+--------------+-----------+
| serid | service_name   | service_code | dayofweek |   1=Sun,..,7=Sat
+-------+----------------+--------------+-----------+
|     1 | Prayer meeting | PM           |         4 |
|     2 | Worship        | WS           |         7 |
|     3 | Thanks giving  | PBB          |         7 |
|     4 | LS             | LS           |      NULL |  unspecified
|     5 | Thanks giving  | D1           |      NULL |
|     6 | Thanks giving  | D2           |      NULL |
|     7 | Thanks giving  | D3           |      NULL |
+-------+----------------+--------------+-----------+

and only process dates/services that match those specified days. That will limit them to one per week. The two queries then become

-- 
-- headers query
-- 
SELECT DISTINCT
       weekofyear(entrydate) -  weekofyear('2022-04-01') + 1 as wk_no
     , date_format(entrydate, '%e/%c') as edate
     , concat(date_format(entrydate, '%d'), ' ', service_code) as dayser
     , service_code
FROM attendance a 
     JOIN service s ON a.serid = s.serid
                    AND (dayofweek(a.entrydate) = s.dayofweek OR s.dayofweek IS NULL)
WHERE month(entrydate) = 4
      AND stype = 1
ORDER BY entrydate, a.serid;

-- 
-- member attendance query
-- 
SELECT 
       weekofyear(entrydate) - weekofyear('2022-04-01') + 1 as wk_no
     , concat(date_format(entrydate, '%d'), ' ', service_code) as dayser
     , group_no
     , mem_name
     , memid
FROM attendance a 
     JOIN member m USING (memid)
     JOIN service s ON a.serid = s.serid
                    AND (dayofweek(a.entrydate) = s.dayofweek OR s.dayofweek IS NULL)
WHERE month(entrydate) = 4
      AND stype = 1
ORDER BY group_no, mem_name, dayser

Thank you @Barand

I will use this as reference, I found a solution to capture all services with replay and live service now.

With this code, I now able to display only 3 services per week by putting dayofweek field on both services and attendance table and compare instead of entrydate of attendance = dayofweek of services.

Than you so much for your help.

Kind regards,
Zin

Barand · June 9, 2022

You don't need dayofweek in attendance table for the same reason you don't need month and year columns. They are derived from the entry date so you are just duplcating data and creating unnecessary columns.

mysql> SELECT YEAR('2022-06-09') as year
    ->      , MONTH('2022-06-09') as month
    ->      , DAYOFWEEK('2022-06-09') as dow;
+------+-------+------+
| year | month | dow  |
+------+-------+------+
| 2022 |     6 |    5 |
+------+-------+------+

10 minutes ago, Zinn said:

I found a solution to capture all services with replay and live service now.

Care to share?

Zinn · June 9, 2022

38 minutes ago, Barand said:
You don't need dayofweek in attendance table for the same reason you don't need month and year columns. They are derived from the entry date so you are just duplcating data and creating unnecessary columns.
mysql> SELECT YEAR('2022-06-09') as year
    ->      , MONTH('2022-06-09') as month
    ->      , DAYOFWEEK('2022-06-09') as dow;
+------+-------+------+
| year | month | dow  |
+------+-------+------+
| 2022 |     6 |    5 |
+------+-------+------+
Care to share?

Thank you once again for your advice @Barand

definitely, I will delete those duplicate fields.

The problem with this project is that;

Our checker wanted to inlucude REPLAY column 2/6(Thrusday) to LIVE column 1/6(Wed).

So that Replay on the following day data will still display on Live column which is 1/6(wed).

But anyway, I will find solution to this and I have in mind already.

Thank you, you've been an inspiration to us to learn more.

image.png.d21f8e2b1b3bd74e8e17e13b31df4438.png

Sign In

Pivot Table or Cross Tab in PHP using MYSQLI for attendance

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information