Barand

Instead of the above final line, can you post something processable such as var_export($category_path_array) Or, better still, a dump of your test data (struture and data) so we can reproduce the array ourselves. (The more you help us the more we can help you)

I was going to suggest mysql> select * from tprojects; +-----------+---------+----------+------------+ | IdProject | service | IsActive | start | +-----------+---------+----------+------------+ | 6 | 123 | 1 | 2024-05-01 | | 7 | 123 | 1 | 2024-05-02 | | 8 | 123 | 1 | 2024-05-03 | | 9 | 123 | 1 | 2024-05-04 | | 10 | 123 | 1 | 2024-05-05 | | 11 | 123 | 1 | NULL | | 12 | 123 | 1 | NULL | | 13 | 123 | 1 | NULL | | 14 | 123 | 1 | NULL | | 15 | 123 | 1 | NULL | | 16 | 123 | 1 | NULL | | 17 | 123 | 1 | NULL | +-----------+---------+----------+------------+ mysql> SELECT COUNT(IdProject) as Sales -> , DATE_FORMAT(start, '%M %Y') AS Month -> FROM tprojects -> WHERE IsActive = '1' -> AND Service = 123 -> AND MONTH(start) IS NOT NULL -> GROUP BY month; +-------+----------+ | Sales | Month | +-------+----------+ | 5 | May 2024 | +-------+----------+

Are you also doing something silly like storing your "start" column as type VARCHAR instead of type DATE?

Execute your query in phpmyadmin. What do you get?

That seems to suggest you have 61 entries with an invalid date (null) and 5 entries for month 5 (may). I don't understand where "Count" index is coming from in that results array as the count() has an column alias "Sales".

To get the month in your results you need to select it. Your query only selects the count.

"o" and "v" are table aliases for the option and vote tables. First I made a couple of additions to the tables... Poll table - added "polling_day" date column (so we now know the current poll when voting) Vote table - added unique index on user_id/option_id to prevent double-voting. CREATE TABLE `vote` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `option_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unq_vote_user_opt` (`user_id`,`option_id`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE `poll` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `polling_day` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; The sample code below uses a query of the poll_option table to get the options for the current poll and generates the checkboxe for the form. The checkboxes are names "vote[]" so they are posted as an array. Note that only "checked" checkboxes are posted so you just need to loop through the array of $POST['vote'] values and insert a vote record for each (with the id of the logged-in user. CODE <?php require 'db_inc.php'; // USE YOUR OWN ... $pdo = mdbConnect('db1'); // ... CONNECTION CODE $_SESSION['user_id'] = 123; // emulate user being logged in - testing only. ################################################################################ ## Handle posted form data ################################################################################ if ($_SERVER['REQUEST_METHOD'] == 'POST') { // Use "INSERT IGNORE ... " so attemmpts to vote twice for an option are ignored $stmt = $pdo->prepare("INSERT IGNORE INTO vote(user_id, option_id) VALUES (?, ?)"); foreach ($_POST['vote'] as $oid) { $stmt->execute( [ $_SESSION['user_id'], $oid ] ); } header("Refresh: 0"); // reload form exit; } ################################################################################ ## Build arrays of vote options for the current poll ################################################################################ $res = $pdo->query("SELECT o.id, o.opt FROM poll_option o JOIN poll p ON o.poll_id = p.id WHERE p.polling_day = CURDATE() ORDER BY opt "); $options = array_column($res->fetchAll(), 'opt', 'id'); if (empty($options)) { exit ("Today is not a polling day"); } ################################################################################ ## Build check box list for the form ################################################################################ $chkboxes = ''; foreach ($options as $val => $label) { $chkboxes .= "<input type='checkbox' name='vote[]' value='$val'>&ensp;$label<br>"; } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset="utf-8"> <title>Example</title> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <style type='text/css'> </style> </head> <body> <form method='POST'> <div class='w3-margin w3-padding w3-border'> <h3>Select your preferences</h3> <?=$chkboxes?> <br> <input type='submit'> </div> </form> </body> </html>

I prefer to use a function to build the options from an array (or query results), creating the html for the options and selecting the appropriate item. An example is below. DATA USED RESOURCE USER +----+--------------------+ +----+----------+--------------+ | id | description | | id | username | fullname | +----+--------------------+ +----+----------+--------------+ | 1 | Car - C43 GBT | | 1 | tess | Tess Tickell | | 2 | Van - D76 GBT | | 2 | hugh | Hugh Jass | | 3 | Minibus - MN11 BUS | | 3 | tom | Tom DiCanari | +----+--------------------+ | 4 | lucy | Lucy Lastik | | +----+----------+--------------+ | | +---------+ | | +---------------------+ | | BOOKING | | +----+-------------+---------------------+---------------------+---------+ | id | resource_id | book_start | book_end | user_id | +----+-------------+---------------------+---------------------+---------+ | 1 | 1 | 2024-05-13 09:00:00 | 2024-05-13 12:00:00 | 1 | | 2 | 1 | 2024-05-13 12:00:00 | 2024-05-13 17:00:00 | 2 | | 4 | 1 | 2024-05-16 12:00:00 | 2024-05-16 18:00:00 | 3 | | 5 | 2 | 2024-05-13 11:30:00 | 2024-05-17 18:00:00 | 4 | +----+-------------+---------------------+---------------------+---------+ OUTPUT EXAMPLE CODE EXAMPLE <?php require 'db_inc.php'; // USE YOUR OWN ... $pdo = mdbConnect('db1'); // ... CONNECTION CODE ################################################################################ ## Build arrays of dropdown options ################################################################################ $res = $pdo->query("SELECT id, description FROM resource ORDER BY description "); $results = $res->fetchAll(); $resources = array_column($results, 'description', 'id'); $res = $pdo->query("SELECT id, fullname FROM user ORDER BY fullname "); $results = $res->fetchAll(); $users = array_column($results, 'fullname', 'id'); /*************************************************************************** * function to generate menu options from array and select current value * * @param array $optArray Array of menu options * @param mixed $current Currnt value to be selected */ function menuOptions(&$optArray, $current='') { $opts = "<option value=''>- select -</option>\n"; foreach ($optArray as $val => $txt) { $sel = $val==$current ? 'selected' : ''; $opts .= "<option $sel value='$val'>$txt</option>\n"; } return $opts; } ################################################################################ ## Build html table for sreen display ################################################################################ $res = $pdo->query("SELECT b.id as bid , r.id as rid , u.id as uid , date_format(b.book_start, '%b %d - %h:%i%p') as stime , date_format(b.book_end, '%b %d - %h:%i%p') as etime FROM booking b JOIN user u ON b.user_id = u.id JOIN resource r ON b.resource_id = r.id ORDER BY rid, book_start, book_end "); $tdata = ''; foreach ($res as $r) { $tdata .= "<tr> <td>{$r['bid']}</td> <td><select name='resid'>" . menuOptions($resources, $r['rid']) . "</select></td> <td><select name='userid'>" . menuOptions($users, $r['uid']) . "</select></td> <td>{$r['stime']}</td> <td>{$r['etime']}</td> </tr> "; } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset="utf-8"> <title>Example</title> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <style type='text/css'> table { border-collapse: collapse; width: 100%; } th, td { padding: 8px; } th { border: 1px solid white; background-color: black; color: white; } </style> </head> <body> <header class='w3-indigo w3-padding'> <h1>Dropdown Menus</h1> </header> <table border='1'> <tr><th>ID</th> <th>Item</th> <th>User</th> <th>From</th> <th>Until</th> </tr> <?=$tdata?> </table> <br> <?=pdo2text($pdo, "select * from resource")?> </body> </html>

Added user table and also added user_id to booking table records... USER +----+----------+--------------+ | id | username | fullname | +----+----------+--------------+ | 1 | tess | Tess Tickell | | 2 | hugh | Hugh Jass | | 3 | tom | Tom DiCanari | | 4 | lucy | Lucy Lastik | +----+----------+--------------+ BOOKING +----+-------------+---------------------+---------------------+---------+ | id | resource_id | book_start | book_end | user_id | +----+-------------+---------------------+---------------------+---------+ | 1 | 1 | 2024-05-13 09:00:00 | 2024-05-13 12:00:00 | 1 | | 2 | 1 | 2024-05-13 12:00:00 | 2024-05-13 17:00:00 | 2 | | 4 | 1 | 2024-05-16 12:00:00 | 2024-05-16 18:00:00 | 3 | | 5 | 2 | 2024-05-13 11:30:00 | 2024-05-17 18:00:00 | 4 | +----+-------------+---------------------+---------------------+---------+ I also added styles for each user name to define background colours. I used a couple of SQL WINDOW functions to the query the row_number() so I know which is the first cell of each booking (That's the only on we need to output) the row count() so we know how many rows the cell should span. CODE <?php require 'db_inc.php'; // $pdo = mdbConnect('db1'); // USER YOUR OWN CONNECTION CODE $wkcomm = $_GET['wkcomm'] ?? date('Y-m-d'); ################################################################################ ## COLUMN HEADINGS ################################################################################ $res = $pdo->query("SELECT id, description FROM resource ORDER BY id "); $theads = "<tr class='w3-dark-gray'><th>Time Slot</th>"; foreach ($res as $r) { $theads .= "<th>{$r['description']}</th>"; } $theads .= "</tr>\n"; ################################################################################ ## QUERY BOOKINGS AND BUILD ARRAY IN REQUIRED STRUCTURE FOR OUTPUT ################################################################################ $res = $pdo->prepare(" -- -- create temporary table containing the daily 30-minute timeslots -- WITH RECURSIVE timeslot (n, starttime, endtime, date) AS ( SELECT 1, '07:00:00', '07:30:00', ? UNION ALL SELECT n+1 , addtime(starttime, '00:30:00') , addtime(endtime, '00:30:00') , date FROM timeslot WHERE n < 24 ) SELECT r.id as resid , time_format(starttime, '%H:%i') as time , b.id as id , u.fullname as booked_by , u.username , t.date , t.starttime , ROW_NUMBER() OVER w1 as slotnum , COUNT(b.id) OVER w2 as slotcount -- -- cross join the resource table with temp timeslot table to give rows for every timeslot for each resource -- then match these against the bookings to see which slots fall withing the booking range -- for the matching resource -- FROM resource r CROSS JOIN timeslot t LEFT JOIN booking b ON CONCAT(t.date, ' ', t.starttime) < b.book_end AND CONCAT(t.date, ' ', t.endtime) > b.book_start AND r.id = b.resource_id LEFT JOIN user u ON b.user_id = u.id WINDOW w1 AS (PARTITION BY id, t.date ORDER BY t.starttime) , w2 AS (PARTITION BY id, t.date) ORDER BY starttime, resid "); $res->execute([ $wkcomm ]); $data = []; foreach ($res as $r) { $data[$r['time']][$r['resid']] = ['bid' => $r['id'], 'num' => $r['slotnum'], 'span' => $r['slotcount'], 'cust' => $r['booked_by'], 'class' => $r['username'] ]; } ################################################################################ ## LOOP THROUGH ARRAY TO CREATE HTML ROWS ################################################################################ $tdata = ''; foreach ($data as $tslot => $slotdata) { $tdata .= "<tr><td class='w3-gray w3-text-white'>$tslot</td>"; foreach ($slotdata as $booked) { if ($booked['bid']) { if ($booked['num']==1) { $tdata .= "<td class='booked {$booked['class']}' rowspan='{$booked['span']}'>{$booked['cust']}</td>"; } } else { $tdata .= '<td>&nbsp;</td>'; } } $tdata .= "</tr>\n"; } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset="utf-8"> <title>Example</title> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <style type='text/css'> table { border-collapse: collapse; width: 100%; } th, td { padding: 4px; } th { border: 1px solid white; } .booked { color: white; border: 3px solid #E0E0E0; text-align: center; } .tess { background-color: #A91723; } .hugh { background-color: #54BC54; } .tom { background-color: #EC9807; } .lucy { background-color: #8F1FCF; } </style> </head> <body> <header class='w3-indigo w3-padding'> <h1>Resource Bookings</h1> </header> <form class='w3-light-gray w3-padding w3-margin-bottom'> Date: <input type='date' name='wkcomm' value='<?=$wkcomm?>'> &emsp; <button class='w3-blue w3-button'>Refresh</button> </form> <div class='w3-content'> <table class='w3-small' border='1'> <?=$theads?> <?=$tdata?> </table> </div> </body> </html>

Slightly simpler this way (by resource headings) CODE <?php require 'db_inc.php'; // $pdo = mdbConnect('db1'); // USER YOUR OWN CONNECTION CODE $wkcomm = $_GET['wkcomm'] ?? date('Y-m-d'); ################################################################################ ## COLUMN HEADINGS ################################################################################ $res = $pdo->query("SELECT id, description FROM resource ORDER BY id "); $theads = "<tr class='w3-dark-gray'><th>Time Slot</th>"; foreach ($res as $r) { $theads .= "<th>{$r['description']}</th>"; } $theads .= "</tr>\n"; ################################################################################ ## QUERY BOOKINGS AND BUILD ARRAY IN REQUIRED STRUCTURE FOR OUTPUT ################################################################################ $res = $pdo->prepare(" -- -- create temporary table containing the daily 30-minute timeslots -- WITH RECURSIVE timeslot (n, starttime, endtime, date) AS ( SELECT 1, '07:00:00', '07:30:00', ? UNION ALL SELECT n+1 , addtime(starttime, '00:30:00') , addtime(endtime, '00:30:00') , date FROM timeslot WHERE n < 24 ) SELECT r.id as resid , time_format(starttime, '%H:%i') as time , b.id -- -- cross join the resource table with temp timeslot table to give rows for every timeslot for each resource -- then match these against the bookings to see which slots fall withing the booking range -- for the matching resource -- FROM resource r CROSS JOIN timeslot t LEFT JOIN booking b ON CONCAT(t.date, ' ', t.starttime) < b.book_end AND CONCAT(t.date, ' ', t.endtime) > b.book_start AND r.id = b.resource_id ORDER BY starttime, resid "); $res->execute([ $wkcomm ]); $data = []; foreach ($res as $r) { $data[$r['time']][$r['resid']] = $r['id']; } ################################################################################ ## LOOP THROUGH ARRAY TO CREATE HTML ROWS ################################################################################ $tdata = ''; foreach ($data as $tslot => $slotdata) { $tdata .= "<tr><td class='w3-gray w3-text-white'>$tslot</td>"; foreach ($slotdata as $booked) { $class = $booked ? 'w3-red' : ''; $tdata .= "<td class='$class'>&nbsp;</td>"; } $tdata .= "</tr>\n"; } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset="utf-8"> <title>Example</title> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <style type='text/css'> table { border-collapse: collapse; width: 100%; } th, td { padding: 4px; } th { border: 1px solid white; } </style> </head> <body> <header class='w3-indigo w3-padding'> <h1>Resource Bookings</h1> </header> <form class='w3-light-gray w3-padding w3-margin-bottom'> Date: <input type='date' name='wkcomm' value='<?=$wkcomm?>'> &emsp; <button class='w3-blue w3-button'>Refresh</button> </form> <div class='w3-content'> <table class='w3-small' border='1'> <?=$theads?> <?=$tdata?> </table> </div> </body> </html>

Try it and see.

I suggest output like this below. I have used yiur two resources and ny booking data looke like this... mysql> select * from booking; +----+-------------+---------------------+---------------------+ | id | resource_id | book_start | book_end | +----+-------------+---------------------+---------------------+ | 1 | 1 | 2024-05-13 09:00:00 | 2024-05-13 12:00:00 | | 2 | 1 | 2024-05-15 09:00:00 | 2024-05-15 19:00:00 | | 4 | 1 | 2024-05-16 12:00:00 | 2024-05-16 18:00:00 | | 5 | 2 | 2024-05-13 12:00:00 | 2024-05-17 18:00:00 | +----+-------------+---------------------+---------------------+ The output looks lke this for the 2 resources... CODE <?php require 'db_inc.php'; // $pdo = mdbConnect('db1'); // USE YOUR OWN CONNECTION CODE $wkcomm = $_GET['wkcomm'] ?? date('Y-m-d'); $resid = $_GET['resid'] ?? ''; ################################################################################ ## COLUMN HEADINGS ################################################################################ $d1 = new DateTime($wkcomm); $di = new DateInterval('P1D'); $dp = new DatePeriod($d1, $di, 6); $theads = "<tr class='w3-dark-gray'><th>Time Slot</th>"; foreach ($dp as $d) { $theads .= "<th>" . $d->format('D M d') . "</th>"; } $theads .= "</tr>\n"; ################################################################################ ## QUERY BOOKINGS AND BUILD ARRAY IN REQUIRED STRUCTURE FOR OUTPUT ################################################################################ $res = $pdo->prepare("-- -- create temporary table containing the dates of the selected week -- WITH RECURSIVE dateslot (n, slotdate) AS ( SELECT 1, ? UNION ALL SELECT n+1 , date_add(slotdate, INTERVAL 1 DAY) FROM dateslot WHERE n < 7 ), -- -- create temporary table containing the daily 30-minute timeslots -- timeslot (n, starttime, endtime) AS ( SELECT 1, '07:00:00', '07:30:00' UNION ALL SELECT n+1 , addtime(starttime, '00:30:00') , addtime(endtime, '00:30:00') FROM timeslot WHERE n < 24 ) SELECT slotdate , time_format(starttime, '%H:%i') as time , b.id -- -- cross join the two temporary tables to give rows for every timeslot for the seven days -- then match these against the bookings to see which slots fall withing the booking range -- for the selected resource -- FROM dateslot d CROSS JOIN timeslot t LEFT JOIN booking b ON CONCAT(d.slotdate, ' ', t.starttime) < b.book_end AND CONCAT(d.slotdate, ' ', t.endtime) > b.book_start AND b.resource_id = ? ORDER BY starttime, slotdate "); $res->execute([ $wkcomm, $resid ]); $data = []; foreach ($res as $r) { $data[$r['time']][$r['slotdate']] = $r['id']; } ################################################################################ ## LOOP THROUGH ARRAY TO CREATE HTML ROWS ################################################################################ $tdata = ''; foreach ($data as $tslot => $slotdata) { $tdata .= "<tr><td class='w3-gray w3-text-white'>$tslot</td>"; foreach ($slotdata as $booked) { $class = $booked ? 'w3-red' : ''; $tdata .= "<td class='$class'>&nbsp;</td>"; } $tdata .= "</tr>\n"; } ################################################################################ ## FUNCTIONS ################################################################################ function resourceOptions($pdo, $current) { $res = $pdo->query("SELECT id, description FROM resource ORDER BY description "); $opts = "<option value=''>- select resource -</option>\n"; foreach ($res as $r) { $sel = $r['id']==$current ? 'selected' : ''; $opts .= "<option $sel value='{$r['id']}'>{$r['description']}</option>\n"; } return $opts; } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset="utf-8"> <title>Example</title> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <style type='text/css'> table { border-collapse: collapse; width: 100%; } th, td { padding: 4px; } th { border: 1px solid white; } </style> </head> <body> <header class='w3-indigo w3-padding'> <h1>Resource Bookings</h1> </header> <form class='w3-light-gray w3-padding w3-margin-bottom'> Week commencing: <input type='date' name='wkcomm' value='<?=$wkcomm?>'> &emsp; Resource: <select name='resid'> <?=resourceOptions($pdo, $resid)?> </select> <button class='w3-blue w3-button'>Refresh</button> </form> <div class='w3-content'> <table class='w3-small' border='1'> <?=$theads?> <?=$tdata?> </table> </div> </body> </html>

When posting code, use the <> button to create a code block

There's a beginners' SQL tutorial in my forum signature. It may help.

@phppup, Given that dog's breakfast of a table that you are storing as a spreadsheet in your data base, you can do it all with SQL but you need a different approach to that above. (Using the same votes table as above) SELECT opt as `Option` , SUM(vote) as Votes FROM ( SELECT 'Balloons' as opt , balloons as vote FROM votes UNION ALL SELECT 'Soda' as opt , soda as vote FROM votes UNION ALL SELECT 'Ribbons' as opt , ribbons as vote FROM votes UNION ALL SELECT 'Bows' as opt , bows as vote FROM votes ) data GROUP BY opt ORDER BY Votes DESC; +-------------+-------------+ | option | votes | +-------------+-------------+ | Balloons | 4 | | Ribbons | 2 | | Bows | 2 | | Soda | 1 | +-------------+-------------+ However, the correct way to do it to store normalized data so that each option that is voted for is stored in its own record. Here's a simple data model and query... TABLE : poll TABLE: poll_option TABLE: vote +---------+-----------+ +--------+----------+-------------+ +-----+----------+-----------+ | id | name | | id | poll_id | opt | | id | user_id | option_id | +---------+-----------+ +--------+----------+-------------+ +-----+----------+-----------+ | 1 | Pets | | 1 | 1 | Cat | | 1 | 1 | 4 | | 2 | Parties | | 2 | 1 | Dog | | 2 | 1 | 6 | +---------+-----------+ | 3 | 1 | Iguana | | 3 | 2 | 4 | | 4 | 2 | Balloons | | 4 | 2 | 6 | | 5 | 2 | Soda | | 5 | 2 | 7 | | 6 | 2 | Ribbons | | 6 | 3 | 4 | | 7 | 2 | Bows | | 7 | 3 | 5 | +--------+----------+-------------+ | 8 | 4 | 4 | | 9 | 4 | 7 | +-----+----------+-----------+ SELECT o.opt as `option` , count(v.option_id) as votes FROM poll_option o JOIN vote v ON o.id = v.option_id WHERE o.poll_id = 2 GROUP BY o.opt ORDER BY votes DESC +-------------+-------------+ | option | votes | +-------------+-------------+ | Balloons | 4 | | Ribbons | 2 | | Bows | 2 | | Soda | 1 | +-------------+-------------+

@Danishhafeez, mysql> select * from votes; +----+----------+------+---------+------+ | id | balloons | soda | ribbons | bows | +----+----------+------+---------+------+ | 1 | 1 | NULL | 1 | NULL | | 2 | 1 | NULL | 1 | 1 | | 3 | 1 | 1 | NULL | NULL | | 4 | 1 | NULL | NULL | 1 | +----+----------+------+---------+------+ 4 rows in set (0.00 sec) mysql> SELECT -> SUM(balloons) AS balloons_total, -> SUM(soda) AS soda_total, -> SUM(ribbons) AS ribbons_total, -> SUM(bows) AS bows_total -> FROM -> votes -> GROUP BY -> balloons, soda, ribbons, bows -> ORDER BY -> balloons_total DESC, soda_total DESC, ribbons_total DESC, bows_total DESC; +----------------+------------+---------------+------------+ | balloons_total | soda_total | ribbons_total | bows_total | +----------------+------------+---------------+------------+ | 1 | 1 | NULL | NULL | | 1 | NULL | 1 | 1 | | 1 | NULL | 1 | NULL | | 1 | NULL | NULL | 1 | +----------------+------------+---------------+------------+ 4 rows in set (0.00 sec)

I think you will find that line no 21374 in the "spare" csv has only 73 values instead of the expected 75.

perhaps you should show the while loop and your query which gets $result->id too.

Strange a function that is defined twice should be flagged as "undefined". You have a surfeit of quotes in this line... echo '<select name="avatar" id="Avatar" onChange="changeAvi()"">'; ^

Working with what you've unfortunately got now, when you read through the query results store each record in an array which has a subarray for month values. Covert the start and dates to index values for this array (0 if the start date is earlier than Jan and 11 if the end date is after Dec. Just use that portion of the array to calculate totals and averages.

Now you have all the data you need to do what you wanted to do. For each mark record,j ust include the values for those months that lie within the data range and job done.

Finally...

How do you know that he started in July? How do you know that Kuldeep Singh (#230) didn't leave at the end August and his average shouldn't be calculated over 8 months? In other words, what is the rule for when to include blank values in the averages and when to ignore them? [edit] BTW, there is no "branch" column the "mark" table

Stop storing spreadsheets in your database. Come back when you have normalized data.

If you work in base 9 rather than base 10 1 + 5 + 7 + 6 = 21