Barand

PHP has array_product(). EG echo array_product([1, 2, 3, 8]); // 48 If you want to be able to supply separate arguments to a function instead of an array EG echo mul(1, 2, 3, 2, 4); then you can define the function as function mul(...$a) { return array_product($a); } echo mul(3,8,2); //48 echo mul(1,2,3,2,2,2) //48

https://lmgtfy.app/?q=Epidemiology+Databases

All you need is $insert_array = [ 'site_name' => 'asdfasdf', 'site_address' => 'Asdffads', 'description' => 'Asdfs', 'contact_phone' => 'Asdfsdaf' ]; $stmt = $conn->prepare("INSERT INTO Sites (site_name, site_address, description, contact_phone) VALUES (:site_name, :site_address, :description, :contact_phone) "); $stmt->execute($insert_array);

One way would be to rename your input fields to something like name = 'jour[$jour][workday]' name = 'jour[$jour][startTime]' name = 'jour[$jour][endTime]' your posted data will then be nicely grouped Array ( [jour] => Array ( [1] => Array ( [workday] => 01/12/2020 [startTime] => 08:00 [endTime] => 06:00 ) [2] => Array ( [workday] => 02/12/2020 [startTime] => 08:30 [endTime] => 06:30 ) [3] => Array ( [workday] => 03/12/2020 [startTime] => 09:00 [endTime] => 04:30 ) . . . )

You are calculating the rank out of the whole population, not just the class. You need to include the class (armslevelid) in the groupings giving EDIT -- use code tags (<> button)

Using your new data I get this (below) for student 1398... How are you getting the "sub-position"?

9208 has 4 students with a higher grade and therefore must be rank"5" and not "4". (If 4 people beat you in a race, you are fifth, even if the 4 winners have the same times) As the rank is either equal to the previous rank or it is the sequence number, there is no way a rank will be more than the number of students.

I give those with same total the same rank, otherwise they are ranked by their position in the sorted (DESC) results This the bit of the SQL that does it , @seq := CASE WHEN yss <> @prev THEN 1 ELSE @seq + 1 END as seq , @rank := CASE WHEN total = @prevtot THEN @rank ELSE @seq END as rank , @prevtot := total as total , @prev := yss as yss

Here's my attempt function TVWeek($d = null) { $dt = new DateTime($d); $y = $dt->format('Y'); $mon1 = new DateTime("$y-01-01"); // last week of year condition ++$y; $nextd1 = new DateTime("$y-01-01"); if ($dt->format('W') == $nextd1->format('W')) return 1; if ($mon1->format('w') != 1) { $mon1->modify('last monday'); } return intdiv( $mon1->diff($dt)->days, 7 ) + 1; } echo '<br>' . TVWeek(); // 1 echo '<br>' . date('W'); // 53 echo '<br>' . TVWeek('2020-06-01'); // 23 echo '<br>' . date('W', strtotime('2020-06-01')); // 23 echo '<br>' . TVWeek('2016-06-01'); // 23 echo '<br>' . date('W', strtotime('2016-06-01')); // 22

You missed a couple

In fact, remove reference to semester completely from the query. As you are no longer grouping by it, any values will be arbitrary and meaningless.

then remove the semester from the groupings, so you get totals by year only

How many records have you where the semesterid is equal 1 and 2 and 3 all at the same time? Try and semesterid in (1,2,3) i.e. 1 OR 2 OR 3 PS as those are the only three possible values, you could just leave that condition out ot the query.

I haven't used a "student" table, but yes, you should have one. I am surprised you don't have one already (it's one of you basic entities). (I do have a subquery with the alias "student" which contains the total students for each group.) Suggested: +----------------+ +-------------+ | student | | subject | +----------------+ +-------------+ | regno PK |---------+ +----------| subjectid PK| | firstname | | | | subject | | lastname | | +-------------+ | +-------------+ | armsid | | | result | | | armslevelid | | +-------------+ | +----------------+ | | result_id PK| | +----------<| regno | | +--------------+ +--------<| year | | | year | +--------<| semester | | +--------------+ | | subjectid |>--------+ | yearid PK |-----+ | | result_type | | startdate | | +---------------+ | | pcent | | enddate | | | semester | | +-------------+ +--------------+ | +---------------+ | +---<| yearid PK |>-----+ | semester PK |>-----+ | startdate | | enddate | +---------------+

1 ) You want to rank the total score so you need to get that in the lowest level subquery 2) you can't apply the grade to the total score (ranges don't apply). I have applied it to the students' average scores. Hopefully, this is what you want... +--------+------------+-------+-------+------+----------------+---------------+-----------+ | yearid | semesterid | regno | total | rank | Total students | Average grade | comment | +--------+------------+-------+-------+------+----------------+---------------+-----------+ | 1 | 1 | 4663 | 865 | 2 | 2 | B2 | V Good | | 1 | 1 | 6073 | 969 | 1 | 2 | A | Excellent | +--------+------------+-------+-------+------+----------------+---------------+-----------+ Query SELECT yearid , semesterid , regno , total , rank , numstudents as `Total students` , grade as `Average grade` , comment FROM ( SELECT yearid , semesterid , @seq := CASE WHEN yss <> @prev THEN 1 ELSE @seq + 1 END as seq , @rank := CASE WHEN total = @prevtot THEN @rank ELSE @seq END as rank , @prevtot := total as total , @prev := yss as yss , regno , armsLevelId , armsid , avgtotal FROM ( SELECT yearid , semesterid , subjectid , concat(yearid, semesterid, armsid, armsLevelId) as yss , regno , SUM(total) as total , ROUND(AVG(total)) as avgtotal , armsLevelId , armsid FROM subject_position GROUP BY yearid, semesterid, armsid, armsLevelId,regno ORDER BY yearid, semesterid, armsid, armsLevelId, total DESC ) sorted JOIN (SELECT @prev := '', @seq := 0, @rank := 0, @prevtot := 0) as init ) ranked JOIN grade ON avgtotal BETWEEN grade.lomark and grade.himark JOIN ( SELECT yearid , semesterid , armsLevelId , armsid , COUNT(DISTINCT regno) as numstudents FROM subject_position GROUP BY yearid, semesterid, armsid, armsLevelId ) students USING (yearid, semesterid, armsid, armsLevelId) WHERE -- regno = 4663 and -- uncomment for individual student armsLevelId='1' and armsId='1' and semesterid='1' and yearid='1' ORDER BY regno;

Use COUNT(DISTINCT regno) as students What as arms and armsLevel?

Looking at you latest output examples I would suggest you lose the "subject_position" table. All you need to store are the highlighted result values below. All the rest (totals, positions) can be derived by querying the data TABLE: result +-------------+--------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------------------+------+-----+---------+----------------+ | result_id | int(11) | NO | PRI | NULL | auto_increment | | regno | int(11) | YES | MUL | NULL | | | year | varchar(45) | YES | | NULL | | | semester | varchar(45) | YES | | NULL | | | subjectid | int(11) | YES | MUL | NULL | | | result_type | enum('CA1','CA2','Exam') | YES | | NULL | | | pcent | int(11) | YES | | NULL | | +-------------+--------------------------+------+-----+---------+----------------+ Note: I have omitted armsid and armsLevel as I haven't clue what they are and where they belong in the schema.

Here's my function for ordinal suffices function ordSuffix($n) { $str = "$n"; $t = $n > 9 ? substr($str,-2,1) : 0; $u = substr($str,-1); if ($t==1) return $str . 'th'; else switch ($u) { case 1: return $str . 'st'; case 2: return $str . 'nd'; case 3: return $str . 'rd'; default: return $str . 'th'; } } echo ordSuffix(1).'<br>'; // 1st echo ordSuffix(11).'<br>'; // 11th echo ordSuffix(101).'<br>'; // 101st echo ordSuffix(2).'<br>'; // 2nd echo ordSuffix(3).'<br>'; // 3rd echo ordSuffix(8).'<br>'; // 8th

Try changing the `tran_term_taxonomy`.`description` = "" to `tran_term_taxonomy`.`description` = '' It won't like you using " in a string enclosed by ".."

PS. This is my grade table DROP TABLE IF EXISTS `grade`; CREATE TABLE `grade` ( `id` int(11) NOT NULL AUTO_INCREMENT, `grade` char(2) NOT NULL DEFAULT '', `lomark` int(11) DEFAULT NULL, `himark` int(11) DEFAULT NULL, `comment` varchar(15) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `grade` -- INSERT INTO `grade` VALUES (1,'A*',91,100,'Distinction'),(2,'A',80,90,'Excellent'),(3,'B1',75,79,'V Good'), (4,'B2',70,74,'V Good'),(5,'C1',68,69,'Good'),(6,'C2',66,67,'Good'), (7,'C3',64,65,'Good'),(8,'C4',61,63,'Good'),(9,'D1',59,60,'Pass'), (10,'D2',57,58,'Pass'),(11,'D3',55,56,'Pass'),(12,'D4',53,54,'Pass'), (13,'D5',51,52,'Pass'),(14,'D6',49,50,'Pass'),(15,'D7',40,48,'Pass'), (16,'E ',21,39,'Poor'),(17,'F ',0,20,'Fail');

It just occured to me that our grade tables are not the same. The missing subjects are 4, 9 and 12 so I extracted those records... mysql> SELECT * FROM subject_position -> WHERE subjectid in (4,9,12) -> and semesterid='1' -> and yearid='1' -> and armsLevelId='1' -> and armsId='1' -> ORDER BY regno, subjectid; +----+-------+-----------+-------------+--------+------------+--------+-------+ | id | regNo | subjectid | armsLevelId | armsId | semesterid | yearid | total | +----+-------+-----------+-------------+--------+------------+--------+-------+ | 23 | 4663 | 12 | 1 | 1 | 1 | 1 | 69 | | 7 | 4663 | 4 | 1 | 1 | 1 | 1 | 99 | | 17 | 4663 | 9 | 1 | 1 | 1 | 1 | 96 | | 24 | 6073 | 12 | 1 | 1 | 1 | 1 | 94 | | 8 | 6073 | 4 | 1 | 1 | 1 | 1 | 95 | | 18 | 6073 | 9 | 1 | 1 | 1 | 1 | 96 | +----+-------+-----------+-------------+--------+------------+--------+-------+ Could it be that those total values are missing from the grade ranges in your grade table and so no matches are found? Try LEFT JOIN grade and see if all records now appear. [edit] Your results - Getting 100% and still failing must have been a little disheartening!

The only change that I made at my end was to define the columns as int, not the varchar(50) that you used) I have just changed mine to varchar to match yours but the only difference was the subject sequence (1, 10, 11, 12, 2,... as expected) and I still got all 12 subjects. So still head-scratching.

mysql> select version(); +------------+ | version() | +------------+ | 5.7.21-log | +------------+

This seems to work <?php include 'db_inc.php'; // use your own $db = pdoConnect('testdb'); // connection process session_start(); $_SESSION['user'] = 1; // just for testing, You use your log in value if (isset($_GET['ajax'])) { $events = []; $t = round($_GET['date'],0); $date = (new DateTime("@$t"))->format('Y-m-d'); $res = $db->prepare("SELECT day(date) as day , status FROM pto_tracker WHERE account = ? AND EXTRACT(YEAR_MONTH FROM date) = EXTRACT(YEAR_MONTH FROM ?) "); $res->execute( [ $_SESSION['user'], $date ]); foreach ($res as $r) { $events[$r['day']] = $r['status']; } exit(json_encode($events)); } ?> <!DOCTYPE HTML> <html> <head> <title>Test</title> <meta http-equiv="content-language" content="en"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.0/jquery.min.js"></script> <script type='text/javascript'> $(document).ready(function() { var currentDate = new Date(); $('#left').click(function(e) { $('table').text(''); if (currentDate.getMonth() === 0) { currentDate = new Date(currentDate.getFullYear() - 1, 11); generateCalendar(currentDate); } else { currentDate = new Date(currentDate.getFullYear(), currentDate.getMonth() - 1) generateCalendar(currentDate); } e.preventDefault(); }); $('#right').click(function(e) { $('table').html('<tr></tr>'); if (currentDate.getMonth() === 11) { currentDate = new Date(currentDate.getFullYear() + 1, 0); generateCalendar(currentDate); } else { currentDate = new Date(currentDate.getFullYear(), currentDate.getMonth() + 1) generateCalendar(currentDate); } e.preventDefault(); }); generateCalendar(currentDate); }); function generateCalendar(d) { Date.prototype.monthDays = function() { var d1 = new Date(d.getFullYear(), d.getMonth() + 1, 0); return d1.getDate(); }; var details = { // totalDays: monthDays(d.getMonth(), d.getFullYear()), totalDays: d.monthDays(), weekDays: ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'], months: ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'], }; var start = new Date(d.getFullYear(), d.getMonth()).getDay(); var cal = []; var day = 1; for (var i = 0; i <= 6; i++) { cal.push(['<tr>']); for (var j = 0; j < 7; j++) { if (i === 0) { cal[i].push('<td>' + details.weekDays[j] + '</td>'); } else if (day > details.totalDays) { cal[i].push('<td>&nbsp;</td>'); } else { if (i === 1 && j < start) { cal[i].push('<td>&nbsp;</td>'); } else { cal[i].push('<td class="day" data-day="' + day + '">' + day++ + '</td>'); } } } cal[i].push('</tr>'); } cal = cal.reduce(function(a, b) { return a.concat(b); }, []).join(''); $('#caltable').append(cal); $('#month').text(details.months[d.getMonth()]); $('#year').text(d.getFullYear()); $('td.day').mouseover(function() { $(this).addClass('hover'); }).mouseout(function() { $(this).removeClass('hover'); }); getEventsArray(d); } function getEventsArray(d) { $.get( "", {"ajax":1, "date":d.getTime()/1000}, function(resp) { console.log(resp) //var r = JSON.parse(resp) $.each(resp, function (k,v) { var day = $(".day[data-day="+k+"]") if (v == 'Approved') { $(day).css("background-color", "#4CAF50") } else if (v == 'Pending') { $(day).css("background-color", "#f0cb11") } else { $(day).css("background-color", "#c62d1f") } $(day).attr("title", v) }) }, "JSON" ) } function monthDays(month, year) { var result = []; var days = new Date(year, month, 0).getDate(); for (var i = 1; i <= days; i++) { result.push(i); } return result; } </script> <style> .container { width: 80%; margin: 15px auto; } </style> </head> <body> <div><h1>Calendar</h1></div> <div> <h3><span id='month'></span> <span id='year'></span></h3> </div> <div> <table id="caltable"></table> </div> </body> </html> Not very pretty but gives ... from this data ... +----+---------+------------+----------+ | id | account | date | status | +----+---------+------------+----------+ | 1 | 1 | 2020-11-05 | Approved | | 2 | 2 | 2020-11-10 | Denied | | 3 | 1 | 2020-11-12 | Pending | | 4 | 1 | 2020-11-16 | Denied | | 5 | 2 | 2020-12-15 | Approved | | 6 | 1 | 2020-12-08 | Approved | * | 7 | 2 | 2020-12-12 | Pending | | 8 | 1 | 2020-12-15 | Denied | * | 9 | 1 | 2020-12-17 | Pending | * +----+---------+------------+----------+

Check the number of affected rows to see if any rows were updated. Not finding a record is not a query failure.