Jump to content

Olumide

Members
 View Profile  See their activity

  • Posts

    126

  • Joined

  • Last visited

  • Days Won

    3

 Content Type 

Everything posted by Olumide

  1. Olumide
    I've modified the form so that when DHCP is turned on, the form inputs are disabled, and when it is turned off, the form inputs are enabled. I've also corrected the duplicate form issue: <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Configuration EMA-ETH WEB</title> <style> body { font-family: Netto-Pro; margin: 0; padding: 0; box-sizing: border-box; } @font-face { font-family: Netto-Pro; src: url(netto_pro.otf); } header { background-color: #333; color: white; text-align: center; padding: 1em; } nav { background-color: #555; overflow: hidden; } nav a { float: left; display: block; color: white; text-align: center; padding: 14px 16px; text-decoration: none; } nav a:hover { background-color: #ddd; color: black; } section { padding: 20px; } form { max-width: 600px; margin: 0 auto; } label { display: block; margin-bottom: 8px; } input, select { width: 100%; padding: 10px; margin-bottom: 16px; border: 1px solid #ccc; border-radius: 4px; box-sizing: border-box; } input[type="submit"] { background-color: #4CAF50; color: white; cursor: pointer; } input[type="submit"]:hover { background-color: #45a049; } .switch { position: relative; display: inline-block; width: 60px; height: 34px; } .switch input { opacity: 0; width: 0; height: 0; } .slider { position: absolute; cursor: pointer; top: 0; left: 0; right: 0; bottom: 0; background-color: #ccc; -webkit-transition: .4s; transition: .4s; } .slider:before { position: absolute; content: ""; height: 26px; width: 26px; left: 4px; bottom: 4px; background-color: white; -webkit-transition: .4s; transition: .4s; } input:checked + .slider { background-color: #2196F3; } input:focus + .slider { box-shadow: 0 0 1px #2196F3; } input:checked + .slider:before { -webkit-transform: translateX(26px); -ms-transform: translateX(26px); transform: translateX(26px); } /* Rounded sliders */ .slider.round { border-radius: 34px; } .slider.round:before { border-radius: 50%; } </style> <script> function toggleForms() { var toggleButton = document.getElementById('toggleButton'); var ipInput = document.getElementById('ip'); var smInput = document.getElementById('sm'); var dgInput = document.getElementById('dg'); var dnsInput = document.getElementById('dns'); ipInput.disabled = toggleButton.checked; smInput.disabled = toggleButton.checked; dgInput.disabled = toggleButton.checked; dnsInput.disabled = toggleButton.checked; } </script> </head> <body> <header> <h1>Configuration EMA-ETH WEB</h1> </header> <nav> <a href="#home">Home</a> <a href="#">Device Info</a> <a href="network.php">Network Settings</a> </nav> <section> <h2>Device Info</h2> <form name="f1" action="#" method="post"> <label>DHCP:</label> <label for="toggleButton" class="switch"> <input type="checkbox" id="toggleButton" onchange="toggleForms()"> <span class="slider round"></span> </label> <form id="form1"> <label for="ip">IP Address:</label> <input type="text" id="ip" name="ip" pattern="^((\d{1,2}|1\d\d|2[0-4]\d|25[0-5])\.){3}(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])$" oninput="this.value = this.value.replace(/[^0-9.]+/g, '');" minlength="7" maxlength="15" required disabled> </form> <form id="form2"> <label for="sm">Subnet Mask:</label> <input type="text" id="sm" name="sm" pattern="^((\d{1,2}|1\d\d|2[0-4]\d|25[0-5])\.){3}(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])$" oninput="this.value = this.value.replace(/[^0-9.]+/g, '');" minlength="7" maxlength="15" required disabled> </form> <form id="form3"> <label for="gw">Default Gateway:</label> <input type="text" id="dg" name="dg" pattern="^((\d{1,2}|1\d\d|2[0-4]\d|25[0-5])\.){3}(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])$" oninput="this.value = this.value.replace(/[^0-9.]+/g, '');" minlength="7" maxlength="15" required disabled> </form> <form id="form4"> <label for="dns">DNS:</label> <input type="text" id="dns" name="dns" pattern="^((\d{1,2}|1\d\d|2[0-4]\d|25[0-5])\.){3}(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])$" oninput="this.value = this.value.replace(/[^0-9.]+/g, '');" minlength="7" maxlength="15" required disabled> </form> <input type="submit" value="Save"> </form> </section> </body> </html>
  2. Olumide
    If you want to disable the input elements instead of hiding them, you can modify the JavaScript code to disable or enable the form elements based on the checkbox state. Here's an updated version of the code: <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Toggle Forms</title> </head> <body> <input type="checkbox" id="toggleButton" onchange="toggleForms()"> <label for="toggleButton">Toggle Forms</label> <form id="form1"> <!-- Your form content goes here --> <p>Form 1</p> <input type="text" name="input1" disabled> <!-- Add more form elements as needed --> </form> <form id="form2"> <!-- Your form content goes here --> <p>Form 2</p> <input type="text" name="input2" disabled> <!-- Add more form elements as needed --> </form> <!-- Add more forms as needed --> <script> function toggleForms() { var toggleButton = document.getElementById('toggleButton'); var forms = document.querySelectorAll('form'); forms.forEach(function(form) { var formElements = form.elements; for (var i = 0; i < formElements.length; i++) { formElements[i].disabled = toggleButton.checked; } }); } </script> </body> </html>
  3. Olumide
    Here's a simple example of JavaScript code that achieves the behavior you described: <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Toggle Forms</title> <style> .hidden { display: none; } </style> </head> <body> <input type="checkbox" id="toggleButton" onchange="toggleForms()"> <label for="toggleButton">Toggle Forms</label> <form id="form1" class="hidden"> <!-- Your form content goes here --> <p>Form 1</p> </form> <form id="form2" class="hidden"> <!-- Your form content goes here --> <p>Form 2</p> </form> <!-- Add more forms as needed --> <script> function toggleForms() { var toggleButton = document.getElementById('toggleButton'); var forms = document.querySelectorAll('form'); forms.forEach(function(form) { if (toggleButton.checked) { form.classList.add('hidden'); } else { form.classList.remove('hidden'); } }); } </script> </body> </html>
  4. Olumide
    To integrate the WPForms shortcode into your custom WooCommerce "No products found" message, you can modify the code as follows: add_action( 'woocommerce_no_products_found', function(){ remove_action( 'woocommerce_no_products_found', 'wc_no_products_found', 10 ); // WooCommerce custom message $woocommerce_message = __( 'Adding parts to the site is an ongoing effort. The part you’re searching for may be within our range but not yet uploaded. If you are unable to find what you are looking for, please fill in our contact form below. Alternatively, email us at sales@crushersparesltd.co.uk , call us on 0044 1443 228329 or contact us via WhatsApp on 0044 732473727.', 'woocommerce' ); // WPForms shortcode $wpforms_shortcode = do_shortcode('[wpforms id="1385"]'); // Output combined message and form echo ' ' . $woocommerce_message .' ' . $wpforms_shortcode .' '; }, 9 );
  5. Olumide
    Below is an example of a simple responsive web page with a navigation bar and a form containing input fields. This example uses HTML and CSS. You can incorporate this code into your project and customize it further as needed. <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Responsive Page with Form</title> <style> body { font-family: Arial, sans-serif; margin: 0; padding: 0; box-sizing: border-box; } header { background-color: #333; color: white; text-align: center; padding: 1em; } nav { background-color: #555; overflow: hidden; } nav a { float: left; display: block; color: white; text-align: center; padding: 14px 16px; text-decoration: none; } nav a:hover { background-color: #ddd; color: black; } section { padding: 20px; } form { max-width: 600px; margin: 0 auto; } label { display: block; margin-bottom: 8px; } input, select { width: 100%; padding: 10px; margin-bottom: 16px; border: 1px solid #ccc; border-radius: 4px; box-sizing: border-box; } input[type="submit"] { background-color: #4CAF50; color: white; cursor: pointer; } input[type="submit"]:hover { background-color: #45a049; } </style> </head> <body> <header> <h1>Responsive Page</h1> </header> <nav> <a href="#home">Home</a> <a href="#about">About</a> <a href="#contact">Contact</a> </nav> <section> <h2>Contact Us</h2> <form> <label for="name">Name:</label> <input type="text" id="name" name="name" required> <label for="email">Email:</label> <input type="email" id="email" name="email" required> <label for="message">Message:</label> <textarea id="message" name="message" rows="4" required></textarea> <input type="submit" value="Submit"> </form> </section> </body> </html>
  6. Olumide
    Try this: RewriteEngine On # Redirect from /shop/mens/jeans/jean-product/ to /shop/mens/jean-product/ RewriteRule ^shop/([^/]+)/[^/]+/([^/]+)/?$ /shop/$1 [R=301,L]
  7. Olumide
    I noticed that you've mentioned pos twice in your SELECT clause. Here's a revised version of your SQL query: SELECT marks.studentid, marks.studentname, marks.acayearid, marks.termid, marks.subjectid, marks.classid, marks.staffname, marks.class_score2, marks.exam_score2, marks.total, marks.grade, RANK() OVER (PARTITION BY marks.termid, marks.classid, marks.subjectid ORDER BY marks.total DESC) as pos, marks.rem FROM marks;
  8. Olumide
    Here's an example of how you can achieve this: // Assuming you have a PDO connection, replace it with your database connection // Fetch data from the database $query = $db->prepare("SELECT id, ranking, team FROM your_table"); $query->execute(); $results = $query->fetchAll(PDO::FETCH_ASSOC); // Initialize an array to store team scores $teamScores = []; // Process each person's ranking foreach ($results as $row) { $ranking = explode(',', $row['ranking']); $id = $row['id']; // Assign scores to teams based on their rankings foreach ($ranking as $key => $team) { $team = trim($team); if (!isset($teamScores[$team])) { $teamScores[$team] = 0; } $teamScores[$team] += $key + 1; // Assign scores based on the ranking } } // Sort the teams based on their total scores asort($teamScores); // Output the sorted teams and their scores foreach ($teamScores as $team => $score) { echo "Team: $team, Total Score: $score<br>"; }
  9. Olumide
    Thanks @gizmola, I will work on that.
  10. Olumide
    I appreciate you @maxxd for your contribution also. Now, I can select from the student list, but I want it in such a way that if a student is selected, the ajax will output the details of the student including fees payable which are already in the table fee. Thanks
  11. Olumide
    Thanks for your feed back sir. I changed the queries as requested but still not working. I attached the screenshort.
  12. Olumide
    Thanks for your feedback sir. I only have the query to get the students list and if a student is selected, the ajax should pop out the details of the students such as "class, fees payable,... but not working maybe sql query is also wrong.
  13. Olumide
    I want to setup a manual payment confirmation for a cashier. When the student pay his or her school. The cashier update the payment by entering the amount paid. I have a table that contain the students list and also fee table. Now, the data are posting to the database, but the ajax is not working. From the index page, the cashier should be able to select student, and immediately a student is selected, the ajax will output the details of the student including fee payable. Here is index.php <body> <label for="studentSelect">Select Student:</label> <select id="studentSelect" onchange="getStudentDetails()"> <!-- Options for students populated dynamically using Ajax --> </select> <div id="studentDetails"> <!-- Student details will be displayed here using Ajax --> </div> <label for="amount">Enter Amount:</label> <input type="number" id="amount"> <button onclick="logPayment()">Log Payment</button> <script> // Your JavaScript code here function getStudentDetails() { var studentId = document.getElementById("studentSelect").value; // Use Ajax to fetch student details based on the selected student var xhr = new XMLHttpRequest(); xhr.open("GET", "get_student_details.php?studentId=" + studentId, true); xhr.onreadystatechange = function () { if (xhr.readyState == 4 && xhr.status == 200) { var studentDetails = xhr.responseText; document.getElementById("studentDetails").innerHTML = studentDetails; } }; xhr.send(); } function logPayment() { var studentId = document.getElementById("studentSelect").value; var amount = document.getElementById("amount").value; // Use Ajax to log the payment and update the database var xhr = new XMLHttpRequest(); xhr.open("POST", "log_payment.php", true); xhr.setRequestHeader("Content-type", "application/x-www-form-urlencoded"); var params = "studentId=" + studentId + "&amount=" + amount; xhr.onreadystatechange = function () { if (xhr.readyState == 4 && xhr.status == 200) { alert(xhr.responseText); // Display the response from the server } }; xhr.send(params); } </script> </body> Here is the get_student_details.php <?php error_reporting(E_ALL); ini_set('display_errors', 1); // Connect to your database (replace with your database credentials) $servername = "localhost"; $username = "root"; $password = ""; $dbname = "mydbt"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Retrieve data from the AJAX request $studentId = $_GET['studentId']; //testing here $sql = "SELECT c.classname, CONCAT(s.firstname, ' ', s.lastname) AS name, sc.feetype, f.description, f.term_fee FROM student s JOIN student_class sc ON s.id = sc.studentid JOIN class c ON sc.classid = c.id JOIN fee f ON c.year = f.year AND sc.semesterid = f.semesterid AND f.feetype = sc.feetype WHERE sc.semesterid = :semesterId ORDER BY c.year, c.classname, name"; // Prepare the SQL statement $stmt = $conn->prepare($sql); // Bind parameters $stmt->bindParam(':semesterId', $studentId, PDO::PARAM_INT); // Execute the query $stmt->execute(); // Fetch the results $result = $stmt->fetchAll(PDO::FETCH_ASSOC); if ($result) { // Output student details as HTML (you can customize this part) foreach ($result as $row) { echo "Class: " . $row["classname"] . "<br>"; echo "Fee Type: " . $row["feetype"] . "<br>"; } } else { echo "No details found for the selected student"; } } catch (PDOException $e) { echo "Connection failed: " . $e->getMessage(); } $conn = null; ?> Here is the log_payment.php <?php error_reporting(E_ALL); ini_set('display_errors', 1); // Connect to your database (replace with your database credentials) $servername = "localhost"; $username = "root"; $password = ""; $dbname = "mydb"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Retrieve data from the AJAX request $studentId = $_POST['studentId']; $amount = $_POST['amount']; $description = "Payment for Semester XYZ"; // Replace with your description // Validate input data if (empty($studentId) || empty($amount)) { die("Error: Student ID and amount cannot be empty."); } // Use prepared statement to prevent SQL injection $sql = "INSERT INTO payment (studentid, pay_date, amount, description) VALUES (?, CURDATE(), ?, ?)"; // Prepare the SQL statement $stmt = $conn->prepare($sql); // Bind parameters $stmt->bindParam(1, $studentId, PDO::PARAM_INT); $stmt->bindParam(2, $amount, PDO::PARAM_STR); $stmt->bindParam(3, $description, PDO::PARAM_STR); if ($stmt->execute()) { echo "Payment logged successfully!"; } else { echo "Error logging payment: " . $stmt->errorInfo()[2]; } } catch (PDOException $e) { echo "Connection failed: " . $e->getMessage(); } $conn = null; ?> Here is my sql for the tables CREATE TABLE `fee` ( `id` int(11) NOT NULL, `feetype` enum('Day','Boarder') DEFAULT NULL, `year` tinyint(4) DEFAULT NULL, `semesterid` int(11) DEFAULT NULL, `fee_description` varchar(100) DEFAULT NULL, `term_fee` decimal(11,2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `fee` -- INSERT INTO `fee` (`id`, `feetype`, `year`, `semesterid`, `fee_description`, `term_fee`) VALUES (1, 'Boarder', 7, 13, 'TUITION', '110000.00'), (2, 'Boarder', 7, 13, 'FEEDING', '191100.00'), (3, 'Boarder', 7, 13, 'CARDIGAN', '10000.00'), (4, 'Boarder', 7, 13, 'UNIFORM (2), TIE (1)', '23000.00'); ALTER TABLE `fee` ADD PRIMARY KEY (`id`), ADD KEY `idx_fee_semesterid` (`semesterid`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `fee` -- ALTER TABLE `fee` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=128; COMMIT; CREATE TABLE `student_class` ( `id` bigint(20) NOT NULL, `studentid` int(11) DEFAULT NULL, `semesterid` int(11) DEFAULT NULL, `classid` int(11) DEFAULT NULL, `feetype` enum('Day','Boarder') DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `student_class` -- INSERT INTO `student_class` (`id`, `studentid`, `semesterid`, `classid`, `feetype`) VALUES (1, 122, 7, 4, 'Boarder'), (2, 122, 8, 4, 'Boarder'), (3, 122, 9, 4, 'Boarder'); -- ALTER TABLE `student_class` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `unq_student_class` (`studentid`,`semesterid`), ADD KEY `idx_student_course_studentid` (`studentid`), ADD KEY `idx_student_course_semesterid` (`semesterid`), ADD KEY `idx_student_class_classid` (`classid`); ALTER TABLE `student_class` MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1589; CREATE TABLE `payment` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studentid` int(11) DEFAULT NULL, `pay_date` date DEFAULT NULL, `amount` decimal(10,2) NOT NULL, `description` varchar(255) NOT NULL, `date_added` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `payment` ADD PRIMARY KEY (`id`), ADD KEY `idx_payment_studentid` (`studentid`); COMMIT; CREATE TABLE `semester` ( `id` int(11) NOT NULL, `semesterName` varchar(100) NOT NULL, `sessionid` int(11) NOT NULL, `date_from` date DEFAULT NULL, `date_until` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `semester` -- INSERT INTO `semester` (`id`, `semesterName`, `sessionid`, `date_from`, `date_until`) VALUES (13, '1st Term', 4, '2023-08-01', '2023-12-15'); -- -- Indexes for dumped tables -- -- -- Indexes for table `semester` -- ALTER TABLE `semester` ADD PRIMARY KEY (`id`), ADD KEY `fk_semester_session1_idx` (`sessionid`); ALTER TABLE `semester` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16; COMMIT; CREATE TABLE `class` ( `id` int(11) NOT NULL, `classname` varchar(45) DEFAULT NULL, `levelid` int(11) NOT NULL, `year` tinyint(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `class` -- INSERT INTO `class` (`id`, `classname`, `levelid`, `year`) VALUES (1, 'Year 7 A', 3, 7), (2, 'Year 7 B', 3, 7), (3, 'Year 7 M', 3, 7), (4, 'Year 8 Ad', 4, 8); ALTER TABLE `class` ADD PRIMARY KEY (`id`), ADD KEY `fk_class_level1_idx` (`levelid`); ALTER TABLE `class` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21; CREATE TABLE `student` ( `id` int NOT NULL, `firstName` varchar(50) NOT NULL, `lastName` varchar(50) NOT NULL, `otherName` varchar(50) NOT NULL, `matricNo` varchar(50) NOT NULL, `password` varchar(255) NOT NULL, `levelId` int DEFAULT NULL, `DOB` date NOT NULL, `Phone` varchar(15) NOT NULL, `Email` varchar(100) NOT NULL, `image` varchar(255) DEFAULT NULL, `dateCreated` date NOT NULL, `leavingdate` date DEFAULT NULL, `login_disabled` tinyint NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `student` -- INSERT INTO `student` (`id`, `firstName`, `lastName`, `otherName`, `matricNo`, `password`, `levelId`, `DOB`, `Phone`, `Email`, `image`, `dateCreated`, `leavingdate`, `login_disabled`) VALUES (122, 'John', 'Kay', '', 'MAB/2021/00001', '$7dydgdgdhhdmOt8TurhyM4RVaX4JM3fhffhfhfj', 7, '2008-04-09', '8100000000', 'example@yahoo.com', 'E.jpg', '2022-10-11', NULL, 0), ALTER TABLE `student` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `idx_student_matricNo` (`matricNo`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `student` -- ALTER TABLE `student` MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1005; COMMIT;
  14. Olumide
    error_reporting(E_ALL); ini_set('display_errors', '1'); $semester = $_GET['semesterid'] ?? 0; $res = $pdo->prepare("SELECT ss.sessionname , ss.id as sessionid , sm.semestername , sm.semestername+0 as termno #, sm.date_until as nextterm # modified , sm.id as smid FROM session ss JOIN semester sm ON sm.sessionid = ss.id WHERE sm.id = ? "); $res->execute([$semester]); $row = $res->fetch(); // $sessionname = $row['sessionname'] ?? ''; $session = $row['sessionid'] ?? 0; // $semestername = $row['semestername'] ?? ''; $termno = $row['termno'] ?? 0; # $nextterm = $row['nextterm'] ?? 0; # modified if ($clid == -1) { $res = $pdo->prepare("SELECT classid FROM student_class WHERE studentid = ? AND semesterid = ? "); $res->execute([$student, $semester]); $clid = $res->fetchColumn(); } switch($termno) { case 1: $term_headings = "<th>1st<br>Term<br>100</th> <!--<th>&ensp;&ensp;&ensp;&ensp;&ensp;</th> <th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>-->"; break; case 2: $term_headings = "<th>1st<br>Term<br>&nbsp;</th> <th>2nd<br>Term<br>100</th> <th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>"; break; default: $term_headings = "<th>1st<br>Term<br>&nbsp;</th> <th>2nd<br>Term<br>&nbsp;</th> <th>3rd<br>Term<br>100</th>"; } $report_title = $termno == 3 ? "End of Year Results" : "End of Term Results"; ################################################################################ # Get scores and put in array with required output structure # ################################################################################ $res = $pdo->prepare("SELECT st.id as stid , concat_ws(' ', st.lastname, st.firstname, st.othername) as stname , st.image , cl.classname , st.dob , st.matricno , sc.classid , l.id as level , sn.sessionname , sm.semestername , sm.date_until , sm.semestername+0 as term , c.subjectid , s.subjectname , exam , score FROM result r JOIN ( student_class sc JOIN class cl ON sc.classid = cl.id JOIN level l ON cl.levelid = l.id JOIN course c ON c.levelid = l.id JOIN student st ON sc.studentid = st.id JOIN semester sm ON sc.semesterid = sm.id JOIN session sn ON sm.sessionid = sn.id JOIN subject s ON c.subjectid = s.id ) ON r.studentclassid = sc.id AND r.courseid = c.id WHERE sn.id = ? AND studentid = ? #AND sm.date_until = ? AND sm.semestername+0 <= ? AND cl.id = ? ORDER BY c.levelid, sc.id, c.subjectid, sc.semesterid, exam "); ############################################################################# ############################################################################# $res->execute( [ $session, $student, $termno, $clid ] ); $data = []; // get data common to all rows from first row $r = $res->fetch(); if ($r) { $studentname = $r['stname']; $studentdob = $r['dob']; $studentmatricno = $r['matricno']; $studentlevel = $r['classname']; $studentsession = $r['sessionname']; $studentterm = "- Term $termno"; $nextterm = $r['date_until']; $passport = "images/" . $r['image']; ### provide image path here $level = $r['level']; // then process the rest of the row data in the first and remaining rows do { if (!isset($data[ $r['subjectid'] ])) { $data[ $r['subjectid'] ] = [ 'name' => $r['subjectname'], #'exams' => ['CA1'=>'', 'CA2'=>'', 'CA3'=>'', 'Exam'=>''], 'exams' => ['CA1'=>'', 'CA2'=>'', 'Exam'=>''], 'scores' => [ 1=>0, 0, 0 ], 'avg' => 0 //'rank' => 0 ]; } if ($r['term'] == $termno && isset($data[$r['subjectid'] ]['exams'][ $r['exam']])) { $data[ $r['subjectid'] ]['exams'][ $r['exam'] ] = $r['score']; } $data[ $r['subjectid'] ]['scores'][$r['term']] += $r['score']; } while ($r = $res->fetch()); // get the avg scores for the class $avgs = classAverageScores($pdo, $clid, $session, $termno); foreach ($avgs as $s => $av) { if (isset($data[$s])) $data[$s]['avg'] = round($av,0); } ###########my ranking here############### /***** manipulating here ************** * * * * / * */ /******************************************** * Derive ordinal suffix for $n * * @param int $n the number * @returns string number with suffix eg 23rd */ function ordinal($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'; } } //function end //$data = []; foreach ($res as $r) { if (!isset($data[$r['subjectname']])) { $data[$r['subjectname']]['students'] = []; } $position = ordinal($r['rank']); $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $position ]; } /* //hffffffffff foreach ($res as $r) { if (!isset($data[$r['subjectname']])) { $data[$r['subjectname']]['students'] = []; } $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $r['rank'] ]; } */ ################################################################################ # Get pupil count # ################################################################################ $res = $pdo->prepare("SELECT COUNT(DISTINCT stc.studentid) AS pupils FROM student_class stc JOIN semester sm ON sm.id = stc.semesterid JOIN result r ON stc.id = r.studentclassid WHERE sm.id = ? AND stc.classid = ? "); $res->execute([ $semester, $clid ]); $pupil_count = $res->fetchColumn(); ################################################################################ # Loop through the data array to construct the output table rows # ################################################################################ $tdata = ''; $n = 1; $grand_total = 0; $subject_count = 0; foreach ($data as $subid => $subdata) { $tdata .= "<tr><td>$n</td><td>{$subdata['name']}</td>"; foreach ($subdata['exams'] as $s) { $tdata .= "<td>" . ($s=='' ? '&ndash;' : $s) . "</td>"; } foreach ($subdata['scores'] as $t => $s) { if ($s==0) $s = ''; $tdata .= "<td>" . ($t <= $termno ? $s : '') . "</td>"; } $temp = array_filter($subdata['scores']); $total = $temp ? round(array_sum($temp)/count($temp)) : 0; $grand_total += $total; if ($total) { list($grade, $comment) = getGradeComment($pdo, $total, $level); $subject_count++; } else { $grade = '-'; $comment = '-'; } $clr = GRADE_COLOUR[$grade] ?? '#000'; $tdata .= "<td>$total</td><td>{$subdata['avg']}</td><td style='color:$clr; font-weight: 600;'>$grade</td><td>$comment</td></tr>\n"; ++$n; } } else { $studentname = ''; $studentdob = ''; $studentmatricno = ''; $studentlevel = ''; $studentsession = ''; $studentsemester = ''; #$nextterm = ''; $studentterm = ''; $passport = ''; $level = '4'; $pupil_count = 0; $grand_total = 0; $subject_count = 1; // $clid = 0; $tdata = "<tr><td colspan='13'>No results found</td></tr>\n"; } ################################################################################ # Get list of gradings # ################################################################################ $res = $pdo->query("SELECT GROUP_CONCAT( grade, concat('&nbsp;(',comments,')'), '&nbsp;', concat(lomark,'&nbsp;-&nbsp;',himark) ORDER BY id SEPARATOR ', ') FROM examgrade #WHERE level_group = ($level > 5) WHERE level_group = ($level > 6) "); $grade_list = $res->fetchColumn(); ################################# $res = $pdo->query('SELECT MIN(date_format(date_from, "%W %M %e, %Y")) as next_term FROM semester WHERE date_from > CURDATE()'); $nextterm = $res->fetchColumn(); ################################# ################################################################################ # Get end of term assessments # ################################################################################ $res = $pdo->prepare("SELECT a.type , a.assessname , e.grade FROM student_class stc JOIN eot_assessment e ON e.studentclassid = stc.id JOIN assessment a ON e.assessmentid = a.id #JOIN semester sm ON sm.id = stc.date_untilid JOIN semester sm ON sm.id = stc.semesterid WHERE stc.studentid = ? AND sm.id = ? "); $res->execute( [ $student, $semester ] ); $ass_data = $res->fetchAll(PDO::FETCH_GROUP); $afflist = $psychlist = ''; if ($ass_data) { $afflist = "<table class='w3-table assess-tbl' > <tr><th>Domain</th><th>Grade</th></tr>\n"; foreach ($ass_data['Affective'] as $agrades) { $afflist .= "<tr><td>{$agrades['assessname']}</td><td>{$agrades['grade']}</td></tr>\n"; } $afflist .= "</table>\n"; $psychlist = "<table class='w3-table assess-tbl' > <tr><th>Domain</th><th>Grade</th></tr>\n"; foreach ($ass_data['Psychomotor'] as $pgrades) { $psychlist .= "<tr><td>{$pgrades['assessname']}</td><td>{$pgrades['grade']}</td></tr>\n"; } $psychlist .= "</table>\n"; } ################################################################################ # Get end of term comments # ################################################################################ $comments = getEOTComments($pdo, $student, $semester); ?> <!DOCTYPE html> <html lang="en"> <head> <title>End Term Results</title> <meta name="viewport" content="width=device-width, initial-scale=1"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <link rel="stylesheet" href="assets/css/w3.css"> <!--<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> --> <script src="assets/js/jquery.js"></script> <!--<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>--> <script type='text/javascript'> $().ready( function() { }) </script> <style type='text/css'> #student-data { width: 100%; } #student-data td { background:#eee; } #result-tbl { width: 100%; } #result-tbl tr:nth-child(2n) { background-color: #eee; } #result-tbl th { text-align: center; padding: 4px; } #result-tbl th:nth-child(2), #result-tbl th:nth-child(13) { text-align: left; } #result-tbl td { text-align: center; padding: 8px 2px; line-height: 0.5em; } #result-tbl td:nth-child(2), #result-tbl td:nth-child(13) { text-align: left; } #result-tbl td:nth-child(12) { padding-left: 25px; text-align: left; } #result-tbl td:nth-child(1), #result-tbl td:nth-child(6), #result-tbl td:nth-child(9) { border-right: 1px solid gray; } .assess-tbl th { border-top: 1px solid gray; border-bottom: 1px solid gray; } #address { font-family: times ; font-size: 20px; line-height: 1.0em; } #bgd { width : 100vw; height: 100vh; z-index: -5; position: fixed; top: 0; left: 0; background-image: url("logo1.png"); opacity: 0.2; background-repeat: no-repeat; background-attachment: fixed; background-position: center; background-size: 600px 600px; } .summaryhead { width: 65%; text-align: center; border: 1px solid blue; } @media print { .noprint { visibility: hidden; } } </style> </head> <body> <header class='w3-row'> <div class='w3-col m2'><img class='w3-image w3-left w3-padding' src='logo1.png' alt='logo'></div> <div class='w3-col m8 w3-padding w3-center' id='address'> <strong></strong><br> <small><i></i></small><br> <h2><?=$report_title?></h2> </div> <div class='w3-col m2'><img class='w3-image w3-right w3-padding' src='<?= $passport ?>' width='160' alt='student photo'></div> </header> <form class='w3-bar w3-light-gray noprint'> <label class='w3-bar-item'>Term</label> <select class='w3-bar-item w3-border' name='semesterid' onchange='this.form.submit()'> <?= semesterOptions($pdo, 0, $semester)?> </select> <?php if (isset($_SESSION['staff_id'])) { ?> <label class='w3-bar-item'>Class</label> <select class='w3-bar-item w3-border' name='classid' id='classid' onchange='this.form.submit()'> <?= classOptions($pdo, $session, $staff, $clid)?> </select> <label class='w3-bar-item'>Student</label> <select class='w3-bar-item w3-border' name='studentid' id='studentid' onchange='this.form.submit()'> <?= studentOptions($pdo, $semester, $clid, $staff, $student)?> </select> <?php } ?> <button class='w3-button w3-bar-item w3-blue-gray w3-right' onclick='window.print()'>Print</button> </form> <!-- my table here --> <div class="w3-responsive"> <table border='2' id='student-data'> <tr class="bg-dark text-white"> <td><strong>Name</strong></td> <td ><strong><?= $studentname ?></strong></td> <td><strong>Class: </strong></td> <td><strong><?= $studentlevel ?></strong></td> <td><strong>RegNo.: </strong></td> <td><strong><?= $studentmatricno ?></strong></td> <td><strong>DOB: </strong></td> <td><strong><?= $studentdob ?></strong></td> <!-- <td><strong>Name: <?= $studentname ?></strong></td> <td><strong>Class: <?= $studentlevel ?></strong></td> <td><strong>RegNo.: <?= $studentmatricno ?></strong></td> <td><strong>DOB: <?= $studentdob ?></strong></td>--> </tr> <tr> <td>Session/Term: </td> <td><strong><?= $studentsession ?> <?=$studentterm?> </strong></td> <td>Total Students in class: </td> <td><strong> <?=$pupil_count?></strong></td> <td>Score: </td> <td><strong> <?=sprintf('%d/%d', $grand_total, $subject_count*100)?></strong></td> <td>Percentage: </td> <td><strong> <?=round($grand_total/$subject_count, 2).'%'?></strong></td> <!-- <td>Session/Term: <?= $studentsession ?> <?=$studentterm?></td> <td>Score: <?=sprintf('%d/%d', $grand_total, $subject_count*100)?></td> <td>Total Students in class: <?=$pupil_count?></td> <td>Percentage: <?=round($grand_total/$subject_count, 2).'%'?></td> --> </tr> <tr> <td>Number of Time School Open</td> <td> <?= $comments['school_open'] ?> </td> <td>Number of Time Present</td> <td> <?= $comments['student_attendance'] ?> </td> </tr> </table> </div> <!-- my table end here --> <!-- <div id='bgd'>&nbsp;</div> <div class='w3-container w3-padding ' id='wrapper'> <div class='w3-row'> <div class='w3-col'> <b>Name: <?= $studentname ?></b> </div> </div> <div class='w3-row'> <div class='w3-col'> <b>DOB: <?= $studentdob ?></b> </div> </div> <div class='w3-row'> <div class='w3-col'> <b>RegNo.: <?= $studentmatricno ?></b> </div> </div> <div class='w3-row'> <div class='w3-col w3-third'> Class: <?= $studentlevel ?><br> Session: <?= $studentsession ?> <?=$studentterm?><br> </div> <div class='w3-col w3-quarter w3-center'> <div class='w3-panel w3-blue summaryhead' >Pupils in class</div> <div class='w3-panel w3-xlarge summaryhead'> <?=$pupil_count?> </div> </div> <div class='w3-col w3-third'> <div class='w3-panel w3-blue summaryhead' >Percentage</div> <div class='w3-panel w3-xlarge summaryhead'> <?=round($grand_total/$subject_count, 2).'%'?> </div> </div> <div class='w3-col w3-third w3-center'> <div class='w3-panel w3-blue summaryhead' >Score</div> <div class='w3-panel w3-xlarge summaryhead'> <?=sprintf('%d/%d', $grand_total, $subject_count*100)?> </div> </div> </div>--> <div class='w3-responsive'> <table border='0' id='result-tbl'> <tr class='w3-border-bottom w3-dark-gray'> <th>&nbsp;</th> <th>Subject</th> <th>CA 1<br>&nbsp;<br>20</th> <th>CA 2<br>&nbsp;<br>20</th> <!--<th>CA 3<br>&nbsp;<br>10</th>--> <th>Exam<br>&nbsp;<br>60</th> <?=$term_headings?> <!-- <th>1st<br>Term<br>&nbsp;</th> <th>2nd<br>Term<br>&nbsp;</th> <th>3rd<br>Term<br>100</th> --> <th>Total</th> <th>Class<br>Avg</th> <th>Position</th> <th>Grade</th> <th>Comment</th> </tr> <?= $tdata ?> </table> </div> <div class='w3-panel w3-padding w3-small'> <b>Grades: </b><i><?= $grade_list ?></i> </div> <h4><b>Assessments</b></h4> <div class='w3-row-padding w3-small'> <div class='w3-col m5 w3-padding'> <b>Affective</b><br> <?= $afflist ?> </div> <div class='w3-col m1 w3-padding'> &nbsp; </div> <div class='w3-col m6 w3-padding'> <b>Psychomotor</b><br> <?= $psychlist ?> <br> <b>Comments</b><br> <div class='w3-container'> <b class='w3-small'>Teacher</b> <div class='w3-padding w3-border' ><?= $comments['teacher'] ?></div > <b class='w3-small'>Head</b> <div class='w3-padding w3-border' ><?= $comments['head'] ?></div > <b class='w3-small'>Next Term Begins on: </b> <?= $nextterm?> </div> </div> </div> </div> </body> </html> The above code is where I am having issue with. The position column. Here is the code to generate the position, but I don't know where to insert the code below into the above scripts, I have tried several ways but to no avail error_reporting(E_ALL); ini_set('display_errors', '1'); case 'E': $res = $pdo->prepare("SELECT subjectname , stname , score , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq , @rank := IF(score = @prev, @rank, @seq) as rank , @prev := score as prev , @prevs := subjectname as prevs FROM ( SELECT sb.subjectname , concat(st.firstname, ' ', st.lastname) as stname , round( sum( score )) as score FROM student_class stc JOIN student st ON stc.studentid = st.id JOIN class cl ON stc.classid = cl.id JOIN level l ON cl.levelid = l.id JOIN course c ON l.id = c.levelid JOIN subject sb ON c.subjectid = sb.id JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id WHERE stc.semesterid = ? AND cl.id = ? GROUP BY c.id, sb.id, st.id ORDER BY subjectname, score DESC LIMIT 9223372036854775807 -- MariaDB bug workaround ) ordered JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init "); $res->execute([ $semester, $class ]); $chke = 'checked'; $chkm = $chky = ''; break; default: $res = $pdo->prepare("SELECT subjectname , stname , score , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq , @rank := IF(score = @prev, @rank, @seq) as rank , @prev := score as prev , @prevs := subjectname as prevs FROM ( SELECT sb.subjectname , concat(st.firstname, ' ', st.lastname) as stname , round( sum( score )/count(distinct sm.id)) as score FROM student_class stc JOIN student st ON stc.studentid = st.id JOIN class cl ON stc.classid = cl.id JOIN level l ON cl.levelid = l.id JOIN course c ON l.id = c.levelid JOIN subject sb ON c.subjectid = sb.id JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id JOIN semester sm ON stc.semesterid = sm.id JOIN session sn ON sm.sessionid = sn.id WHERE sm.sessionid = ? AND cl.id = ? GROUP BY cl.id, sb.id, st.id ORDER BY subjectname, score DESC LIMIT 9223372036854775807 -- MariaDB bug workaround ) ordered JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init ORDER BY subjectname, score DESC "); $res->execute([ $session, $class ]); $chky = 'checked'; $chkm = $chke = ''; break; } /*function getOrdinalSuffix($score) { $score = abs($score) % 100; $lastChar = substr($score, -1, 1); switch ($lastChar) { case '1' : return ($score == '11') ? 'th' : 'st'; case '2' : return ($score == '12') ? 'th' : 'nd'; case '3' : return ($score == '13') ? 'th' : 'rd'; } return 'th'; } $list = ''; for ($score = 1; $score < 150; $score++) { $list .= "$score" . getOrdinalSuffix($score) . "\n"; } ///print "$list";*/ /******************************************** * Derive ordinal suffix for $n * * @param int $n the number * @returns string number with suffix eg 23rd */ /* function ordinal($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'; } }*/ // calling function here /******************************************** * Derive ordinal suffix for $n * * @param int $n the number * @returns string number with suffix eg 23rd */ function ordinal($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'; } } //function end $data = []; foreach ($res as $r) { if (!isset($data[$r['subjectname']])) { $data[$r['subjectname']]['students'] = []; } $position = ordinal($r['rank']); $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $position ]; }
  15. Olumide
    I am sorry, I thought you ask for sample? Please what should I send?
  16. Olumide
    Why not post your script for the sign up to see where the error is.
  17. Olumide
    Attached is the sample of the output of the result which I could not generate the position tagged as "sampleA" while sample is the output of the script with the ordinal suffix which I would like to copy the script into the result to have the position 1st, 2nd, 3rd, ... in the sampleA.
  18. Olumide
    Hi, Please I need help, I am working on an elementary result platform and everything has been done by my mentor but I want the result output to be like the image attached. The only difficulties I am having is the position, the functions and the sql code to generate the position has been written by my boss but it was written in another script. I want to add the script to my results script so that I can have the output attached. Please kindly help as it has been given a sleepless night. Here is the script for the result which I would like to integrate the ordinal functions into: $semester = $_GET['semesterid'] ?? 0; $res = $pdo->prepare("SELECT ss.sessionname , ss.id as sessionid , sm.semestername , sm.semestername+0 as termno #, sm.date_until as nextterm # modified , sm.id as smid FROM session ss JOIN semester sm ON sm.sessionid = ss.id WHERE sm.id = ? "); $res->execute([$semester]); $row = $res->fetch(); // $sessionname = $row['sessionname'] ?? ''; $session = $row['sessionid'] ?? 0; // $semestername = $row['semestername'] ?? ''; $termno = $row['termno'] ?? 0; # $nextterm = $row['nextterm'] ?? 0; # modified if ($clid == -1) { $res = $pdo->prepare("SELECT classid FROM student_class WHERE studentid = ? AND semesterid = ? "); $res->execute([$student, $semester]); $clid = $res->fetchColumn(); } switch($termno) { case 1: $term_headings = "<th>1st<br>Term<br>100</th> <!--<th>&ensp;&ensp;&ensp;&ensp;&ensp;</th> <th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>-->"; break; case 2: $term_headings = "<th>1st<br>Term<br>&nbsp;</th> <th>2nd<br>Term<br>100</th> <th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>"; break; default: $term_headings = "<th>1st<br>Term<br>&nbsp;</th> <th>2nd<br>Term<br>&nbsp;</th> <th>3rd<br>Term<br>100</th>"; } $report_title = $termno == 3 ? "End of Year Results" : "End of Term Results"; ################################################################################ # Get scores and put in array with required output structure # ################################################################################ $res = $pdo->prepare("SELECT st.id as stid , concat_ws(' ', st.lastname, st.firstname, st.othername) as stname , st.image , cl.classname , st.dob , st.matricno , sc.classid , l.id as level , sn.sessionname , sm.semestername , sm.date_until , sm.semestername+0 as term , c.subjectid , s.subjectname , exam , score FROM result r JOIN ( student_class sc JOIN class cl ON sc.classid = cl.id JOIN level l ON cl.levelid = l.id JOIN course c ON c.levelid = l.id JOIN student st ON sc.studentid = st.id JOIN semester sm ON sc.semesterid = sm.id JOIN session sn ON sm.sessionid = sn.id JOIN subject s ON c.subjectid = s.id ) ON r.studentclassid = sc.id AND r.courseid = c.id WHERE sn.id = ? AND studentid = ? #AND sm.date_until = ? AND sm.semestername+0 <= ? AND cl.id = ? ORDER BY c.levelid, sc.id, c.subjectid, sc.semesterid, exam "); ############################################################################# ############################################################################# $res->execute( [ $session, $student, $termno, $clid ] ); $data = []; // get data common to all rows from first row $r = $res->fetch(); if ($r) { $studentname = $r['stname']; $studentdob = $r['dob']; $studentmatricno = $r['matricno']; $studentlevel = $r['classname']; $studentsession = $r['sessionname']; $studentterm = "- Term $termno"; $nextterm = $r['date_until']; $passport = "images/" . $r['image']; ### provide image path here $level = $r['level']; // then process the rest of the row data in the first and remaining rows do { if (!isset($data[ $r['subjectid'] ])) { $data[ $r['subjectid'] ] = [ 'name' => $r['subjectname'], #'exams' => ['CA1'=>'', 'CA2'=>'', 'CA3'=>'', 'Exam'=>''], 'exams' => ['CA1'=>'', 'CA2'=>'', 'Exam'=>''], 'scores' => [ 1=>0, 0, 0 ], 'avg' => 0 //'rank' => 0 ]; } if ($r['term'] == $termno && isset($data[$r['subjectid'] ]['exams'][ $r['exam']])) { $data[ $r['subjectid'] ]['exams'][ $r['exam'] ] = $r['score']; } $data[ $r['subjectid'] ]['scores'][$r['term']] += $r['score']; } while ($r = $res->fetch()); // get the avg scores for the class $avgs = classAverageScores($pdo, $clid, $session, $termno); foreach ($avgs as $s => $av) { if (isset($data[$s])) $data[$s]['avg'] = round($av,0); } ###########my ranking here############### /***** manipulating here ************** * * * * / * */ /******************************************** * Derive ordinal suffix for $n * * @param int $n the number * @returns string number with suffix eg 23rd */ function ordinal($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'; } } //function end //$data = []; foreach ($res as $r) { if (!isset($data[$r['subjectname']])) { $data[$r['subjectname']]['students'] = []; } $position = ordinal($r['rank']); $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $position ]; } /* //hffffffffff foreach ($res as $r) { if (!isset($data[$r['subjectname']])) { $data[$r['subjectname']]['students'] = []; } $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $r['rank'] ]; } */ ################################################################################ # Get pupil count # ################################################################################ $res = $pdo->prepare("SELECT COUNT(DISTINCT stc.studentid) AS pupils FROM student_class stc JOIN semester sm ON sm.id = stc.semesterid JOIN result r ON stc.id = r.studentclassid WHERE sm.id = ? AND stc.classid = ? "); $res->execute([ $semester, $clid ]); $pupil_count = $res->fetchColumn(); ################################################################################ # Loop through the data array to construct the output table rows # ################################################################################ $tdata = ''; $n = 1; $grand_total = 0; $subject_count = 0; foreach ($data as $subid => $subdata) { $tdata .= "<tr><td>$n</td><td>{$subdata['name']}</td>"; foreach ($subdata['exams'] as $s) { $tdata .= "<td>" . ($s=='' ? '&ndash;' : $s) . "</td>"; } foreach ($subdata['scores'] as $t => $s) { if ($s==0) $s = ''; $tdata .= "<td>" . ($t <= $termno ? $s : '') . "</td>"; } $temp = array_filter($subdata['scores']); $total = $temp ? round(array_sum($temp)/count($temp)) : 0; $grand_total += $total; if ($total) { list($grade, $comment) = getGradeComment($pdo, $total, $level); $subject_count++; } else { $grade = '-'; $comment = '-'; } $clr = GRADE_COLOUR[$grade] ?? '#000'; $tdata .= "<td>$total</td><td>{$subdata['avg']}</td><td style='color:$clr; font-weight: 600;'>$grade</td><td>$comment</td></tr>\n"; ++$n; } } else { $studentname = ''; $studentdob = ''; $studentmatricno = ''; $studentlevel = ''; $studentsession = ''; $studentsemester = ''; #$nextterm = ''; $studentterm = ''; $passport = ''; $level = '4'; $pupil_count = 0; $grand_total = 0; $subject_count = 1; // $clid = 0; $tdata = "<tr><td colspan='13'>No results found</td></tr>\n"; } ################################################################################ # Get list of gradings # ################################################################################ $res = $pdo->query("SELECT GROUP_CONCAT( grade, concat('&nbsp;(',comments,')'), '&nbsp;', concat(lomark,'&nbsp;-&nbsp;',himark) ORDER BY id SEPARATOR ', ') FROM examgrade #WHERE level_group = ($level > 5) WHERE level_group = ($level > 6) "); $grade_list = $res->fetchColumn(); ################################# $res = $pdo->query('SELECT MIN(date_format(date_from, "%W %M %e, %Y")) as next_term FROM semester WHERE date_from > CURDATE()'); $nextterm = $res->fetchColumn(); ################################# ################################################################################ # Get end of term assessments # ################################################################################ $res = $pdo->prepare("SELECT a.type , a.assessname , e.grade FROM student_class stc JOIN eot_assessment e ON e.studentclassid = stc.id JOIN assessment a ON e.assessmentid = a.id #JOIN semester sm ON sm.id = stc.date_untilid JOIN semester sm ON sm.id = stc.semesterid WHERE stc.studentid = ? AND sm.id = ? "); $res->execute( [ $student, $semester ] ); $ass_data = $res->fetchAll(PDO::FETCH_GROUP); $afflist = $psychlist = ''; if ($ass_data) { $afflist = "<table class='w3-table assess-tbl' > <tr><th>Domain</th><th>Grade</th></tr>\n"; foreach ($ass_data['Affective'] as $agrades) { $afflist .= "<tr><td>{$agrades['assessname']}</td><td>{$agrades['grade']}</td></tr>\n"; } $afflist .= "</table>\n"; $psychlist = "<table class='w3-table assess-tbl' > <tr><th>Domain</th><th>Grade</th></tr>\n"; foreach ($ass_data['Psychomotor'] as $pgrades) { $psychlist .= "<tr><td>{$pgrades['assessname']}</td><td>{$pgrades['grade']}</td></tr>\n"; } $psychlist .= "</table>\n"; } ################################################################################ # Get end of term comments # ################################################################################ $comments = getEOTComments($pdo, $student, $semester); ?> Here is the code for the ordinal suffix to generate position which I was unable to manipulate into the above script switch ($midend) { case 'M': $res = $pdo->prepare("SELECT subjectname , stname , score , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq , @rank := IF(score = @prev, @rank, @seq) as rank , @prev := score as prev , @prevs := subjectname as prevs FROM ( SELECT cl.classname , sb.subjectname , concat(st.firstname, ' ', st.lastname) as stname , round( sum( case exam when 'Exam' then score/70*100 else score*10 end ) / COUNT(distinct exam) ) as score FROM student_class stc JOIN student st ON stc.studentid = st.id JOIN class cl ON stc.classid = cl.id JOIN level l ON cl.levelid = l.id JOIN course c ON l.id = c.levelid JOIN subject sb ON c.subjectid = sb.id JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id WHERE stc.semesterid = ? AND exam = 'CA1' AND cl.id = ? GROUP BY c.id, sb.id, st.id ORDER BY subjectname, score DESC LIMIT 9223372036854775807 -- MariaDB bug workaround ) ordered JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init "); $res->execute([ $semester, $class ]); $chkm = 'checked'; $chke = $chky = ''; break; case 'E': $res = $pdo->prepare("SELECT subjectname , stname , score , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq , @rank := IF(score = @prev, @rank, @seq) as rank , @prev := score as prev , @prevs := subjectname as prevs FROM ( SELECT sb.subjectname , concat(st.firstname, ' ', st.lastname) as stname , round( sum( score )) as score FROM student_class stc JOIN student st ON stc.studentid = st.id JOIN class cl ON stc.classid = cl.id JOIN level l ON cl.levelid = l.id JOIN course c ON l.id = c.levelid JOIN subject sb ON c.subjectid = sb.id JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id WHERE stc.semesterid = ? AND cl.id = ? GROUP BY c.id, sb.id, st.id ORDER BY subjectname, score DESC LIMIT 9223372036854775807 -- MariaDB bug workaround ) ordered JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init "); $res->execute([ $semester, $class ]); $chke = 'checked'; $chkm = $chky = ''; break; default: $res = $pdo->prepare("SELECT subjectname , stname , score , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq , @rank := IF(score = @prev, @rank, @seq) as rank , @prev := score as prev , @prevs := subjectname as prevs FROM ( SELECT sb.subjectname , concat(st.firstname, ' ', st.lastname) as stname , round( sum( score )/count(distinct sm.id)) as score FROM student_class stc JOIN student st ON stc.studentid = st.id JOIN class cl ON stc.classid = cl.id JOIN level l ON cl.levelid = l.id JOIN course c ON l.id = c.levelid JOIN subject sb ON c.subjectid = sb.id JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id JOIN semester sm ON stc.semesterid = sm.id JOIN session sn ON sm.sessionid = sn.id WHERE sm.sessionid = ? AND cl.id = ? GROUP BY cl.id, sb.id, st.id ORDER BY subjectname, score DESC LIMIT 9223372036854775807 -- MariaDB bug workaround ) ordered JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init ORDER BY subjectname, score DESC "); $res->execute([ $session, $class ]); $chky = 'checked'; $chkm = $chke = ''; break; } /*function getOrdinalSuffix($score) { $score = abs($score) % 100; $lastChar = substr($score, -1, 1); switch ($lastChar) { case '1' : return ($score == '11') ? 'th' : 'st'; case '2' : return ($score == '12') ? 'th' : 'nd'; case '3' : return ($score == '13') ? 'th' : 'rd'; } return 'th'; } $list = ''; for ($score = 1; $score < 150; $score++) { $list .= "$score" . getOrdinalSuffix($score) . "\n"; } ///print "$list";*/ /******************************************** * Derive ordinal suffix for $n * * @param int $n the number * @returns string number with suffix eg 23rd */ /* function ordinal($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'; } }*/ // calling function here /******************************************** * Derive ordinal suffix for $n * * @param int $n the number * @returns string number with suffix eg 23rd */ function ordinal($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'; } } //function end $data = []; foreach ($res as $r) { if (!isset($data[$r['subjectname']])) { $data[$r['subjectname']]['students'] = []; } $position = ordinal($r['rank']); $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $position ]; }
  19. Olumide
    Hi, in my project, I have two scripts, one is to show all the students position in a class and only available to the admin, while the second script will show the results for each student with the position. In the second script which is available to the student and admin, I added position row, but I don't know how to add the scripts for positioning. Please the experts, how can I fix this issue I am encountering. Here is the sql and functions for the ordinal suffix for ranking, from the code, a check button was used in the first script, but the second script, the user only need to select the term. switch ($midend) { case 'M': $res = $pdo->prepare("SELECT subjectname , stname , score , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq , @rank := IF(score = @prev, @rank, @seq) as rank , @prev := score as prev , @prevs := subjectname as prevs FROM ( SELECT cl.classname , sb.subjectname , concat(st.firstname, ' ', st.lastname) as stname , round( sum( case exam when 'Exam' then score/70*100 else score*10 end ) / COUNT(distinct exam) ) as score FROM student_class stc JOIN student st ON stc.studentid = st.id JOIN class cl ON stc.classid = cl.id JOIN level l ON cl.levelid = l.id JOIN course c ON l.id = c.levelid JOIN subject sb ON c.subjectid = sb.id JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id WHERE stc.semesterid = ? AND exam = 'CA1' AND cl.id = ? GROUP BY c.id, sb.id, st.id ORDER BY subjectname, score DESC LIMIT 9223372036854775807 -- MariaDB bug workaround ) ordered JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init "); $res->execute([ $semester, $class ]); $chkm = 'checked'; $chke = $chky = ''; break; case 'E': $res = $pdo->prepare("SELECT subjectname , stname , score , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq , @rank := IF(score = @prev, @rank, @seq) as rank , @prev := score as prev , @prevs := subjectname as prevs FROM ( SELECT sb.subjectname , concat(st.firstname, ' ', st.lastname) as stname , round( sum( score )) as score FROM student_class stc JOIN student st ON stc.studentid = st.id JOIN class cl ON stc.classid = cl.id JOIN level l ON cl.levelid = l.id JOIN course c ON l.id = c.levelid JOIN subject sb ON c.subjectid = sb.id JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id WHERE stc.semesterid = ? AND cl.id = ? GROUP BY c.id, sb.id, st.id ORDER BY subjectname, score DESC LIMIT 9223372036854775807 -- MariaDB bug workaround ) ordered JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init "); $res->execute([ $semester, $class ]); $chke = 'checked'; $chkm = $chky = ''; break; default: $res = $pdo->prepare("SELECT subjectname , stname , score , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq , @rank := IF(score = @prev, @rank, @seq) as rank , @prev := score as prev , @prevs := subjectname as prevs FROM ( SELECT sb.subjectname , concat(st.firstname, ' ', st.lastname) as stname , round( sum( score )/count(distinct sm.id)) as score FROM student_class stc JOIN student st ON stc.studentid = st.id JOIN class cl ON stc.classid = cl.id JOIN level l ON cl.levelid = l.id JOIN course c ON l.id = c.levelid JOIN subject sb ON c.subjectid = sb.id JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id JOIN semester sm ON stc.semesterid = sm.id JOIN session sn ON sm.sessionid = sn.id WHERE sm.sessionid = ? AND cl.id = ? GROUP BY cl.id, sb.id, st.id ORDER BY subjectname, score DESC LIMIT 9223372036854775807 -- MariaDB bug workaround ) ordered JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init ORDER BY subjectname, score DESC "); $res->execute([ $session, $class ]); $chky = 'checked'; $chkm = $chke = ''; break; } // calling function here /******************************************** * Derive ordinal suffix for $n * * @param int $n the number * @returns string number with suffix eg 23rd */ function ordinal($n) { $str = "$n"; $t = $n > 9 ? substr($str,-2,1) : 0; $u = substr($str,-1); //if((($t%100) >= 11) && (($t%100) <= 13)) 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'; } } //function end $data = []; foreach ($res as $r) { if (!isset($data[$r['subjectname']])) { $ Here is the second scripts where I want to fit in the above code for the ranking to populate the position row. The reason why I need all the sql code is because of the term/semester differences. switch($termno) { case 1: $term_headings = "<th>1st<br>Term<br>100</th> <!--<th>&ensp;&ensp;&ensp;&ensp;&ensp;</th> <th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>-->"; break; case 2: $term_headings = "<th>1st<br>Term<br>&nbsp;</th> <th>2nd<br>Term<br>100</th> <th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>"; break; default: $term_headings = "<th>1st<br>Term<br>&nbsp;</th> <th>2nd<br>Term<br>&nbsp;</th> <th>3rd<br>Term<br>100</th>"; } $report_title = $termno == 3 ? "End of Year Results" : "End of Term Results"; ########################## ############################################################################# $res->execute( [ $session, $student, $termno, $clid ] ); $data = []; // get data common to all rows from first row $r = $res->fetch(); if ($r) { $studentname = $r['stname']; $studentdob = $r['dob']; $studentmatricno = $r['matricno']; $studentlevel = $r['classname']; $studentsession = $r['sessionname']; $studentterm = "- Term $termno"; $nextterm = $r['date_until']; $passport = "images/" . $r['image']; ### provide image path here $level = $r['level']; // then process the rest of the row data in the first and remaining rows do { if (!isset($data[ $r['subjectid'] ])) { $data[ $r['subjectid'] ] = [ 'name' => $r['subjectname'], #'exams' => ['CA1'=>'', 'CA2'=>'', 'CA3'=>'', 'Exam'=>''], 'exams' => ['CA1'=>'', 'CA2'=>'', 'Exam'=>''], 'scores' => [ 1=>0, 0, 0 ], 'avg' => 0 //'rank' => 0 ]; } if ($r['term'] == $termno && isset($data[$r['subjectid'] ]['exams'][ $r['exam']])) { $data[ $r['subjectid'] ]['exams'][ $r['exam'] ] = $r['score']; } $data[ $r['subjectid'] ]['scores'][$r['term']] += $r['score']; } while ($r = $res->fetch()); // get the avg scores for the class $avgs = classAverageScores($pdo, $clid, $session, $termno); foreach ($avgs as $s => $av) { if (isset($data[$s])) $data[$s]['avg'] = round($av,0); } ###########my ranking here############### /***** manipulating here ************** * * * * / * */ /******************************************** * Derive ordinal suffix for $n * * @param int $n the number * @returns string number with suffix eg 23rd */ function ordinal($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'; } } //function end //$data = []; foreach ($res as $r) { if (!isset($data[$r['subjectname']])) { $data[$r['subjectname']]['students'] = []; } $position = ordinal($r['rank']); $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $position ]; } /* //hffffffffff foreach ($res as $r) { if (!isset($data[$r['subjectname']])) { $data[$r['subjectname']]['students'] = []; } $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $r['rank'] ]; } */ ################################################################################ # Get pupil count # ################################################################################ $res = $pdo->prepare("SELECT COUNT(DISTINCT stc.studentid) AS pupils FROM student_class stc JOIN semester sm ON sm.id = stc.semesterid JOIN result r ON stc.id = r.studentclassid WHERE sm.id = ? AND stc.classid = ? "); $res->execute([ $semester, $clid ]); $pupil_count = $res->fetchColumn(); ################################################################################ # Loop through the data array to construct the output table rows # ################################################################################ $tdata = ''; $n = 1; $grand_total = 0; $subject_count = 0; foreach ($data as $subid => $subdata) { $tdata .= "<tr><td>$n</td><td>{$subdata['name']}</td>"; foreach ($subdata['exams'] as $s) { $tdata .= "<td>" . ($s=='' ? '&ndash;' : $s) . "</td>"; } foreach ($subdata['scores'] as $t => $s) { if ($s==0) $s = ''; $tdata .= "<td>" . ($t <= $termno ? $s : '') . "</td>"; } $temp = array_filter($subdata['scores']); $total = $temp ? round(array_sum($temp)/count($temp)) : 0; $grand_total += $total; if ($total) { list($grade, $comment) = getGradeComment($pdo, $total, $level); $subject_count++; } else { $grade = '-'; $comment = '-'; } $clr = GRADE_COLOUR[$grade] ?? '#000'; $tdata .= "<td>$total</td><td>{$subdata['avg']}</td><td style='color:$clr; font-weight: 600;'>$grade</td><td>$comment</td></tr>\n"; ++$n; } } else { $studentname = ''; $studentdob = ''; $studentmatricno = ''; $studentlevel = ''; $studentsession = ''; $studentsemester = ''; #$nextterm = ''; $studentterm = ''; $passport = ''; $level = '4'; $pupil_count = 0; $grand_total = 0; $subject_count = 1; // $clid = 0; $tdata = "<tr><td colspan='13'>No results found</td></tr>\n"; }
  20. Olumide
    Thanks so much sir. I appreciate you dearly. But, please sir, I want to integrate the function again in another script, I copied the sql querry and paste it in the term selection. It is not working because I got it wrong, but it is settled in the other part. But, I want to have it in the checking result part so that each student checking his or her result can know his score unlike the formal one that will show all the students which is for the admin. Thanks so much sir. switch($termno) { case 1: $term_headings = "<th>1st<br>Term<br>100</th> <!--<th>&ensp;&ensp;&ensp;&ensp;&ensp;</th> <th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>-->"; $res = $pdo->prepare("SELECT subjectname , stname , score , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq , @rank := IF(score = @prev, @rank, @seq) as rank , @prev := score as prev , @prevs := subjectname as prevs FROM ( SELECT cl.classname , sb.subjectname , concat(st.firstname, ' ', st.lastname) as stname , round( sum( case exam when 'Exam' then score/70*100 else score*10 end ) / COUNT(distinct exam) ) as score FROM student_class stc JOIN student st ON stc.studentid = st.id JOIN class cl ON stc.classid = cl.id JOIN level l ON cl.levelid = l.id JOIN course c ON l.id = c.levelid JOIN subject sb ON c.subjectid = sb.id JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id WHERE stc.semesterid = ? AND exam = 'CA1' AND cl.id = ? GROUP BY c.id, sb.id, st.id ORDER BY subjectname, score DESC LIMIT 9223372036854775807 -- MariaDB bug workaround ) ordered JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init "); $res->execute([ $semester, $class ]); # $chkm = 'checked'; #$chke = $chky = ''; break; case 2: $term_headings = "<th>1st<br>Term<br>&nbsp;</th> <th>2nd<br>Term<br>100</th> <th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>"; $res = $pdo->prepare("SELECT subjectname , stname , score , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq , @rank := IF(score = @prev, @rank, @seq) as rank , @prev := score as prev , @prevs := subjectname as prevs FROM ( SELECT sb.subjectname , concat(st.firstname, ' ', st.lastname) as stname , round( sum( score )) as score FROM student_class stc JOIN student st ON stc.studentid = st.id JOIN class cl ON stc.classid = cl.id JOIN level l ON cl.levelid = l.id JOIN course c ON l.id = c.levelid JOIN subject sb ON c.subjectid = sb.id JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id WHERE stc.semesterid = ? AND cl.id = ? GROUP BY c.id, sb.id, st.id ORDER BY subjectname, score DESC LIMIT 9223372036854775807 -- MariaDB bug workaround ) ordered JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init "); $res->execute([ $semester, $class ]); #$chke = 'checked'; #$chkm = $chky = ''; break; default: $term_headings = "<th>1st<br>Term<br>&nbsp;</th> <th>2nd<br>Term<br>&nbsp;</th> <th>3rd<br>Term<br>100</th>"; $res = $pdo->prepare("SELECT subjectname , stname , score , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq , @rank := IF(score = @prev, @rank, @seq) as rank , @prev := score as prev , @prevs := subjectname as prevs FROM ( SELECT sb.subjectname , concat(st.firstname, ' ', st.lastname) as stname , round( sum( score )/count(distinct sm.id)) as score FROM student_class stc JOIN student st ON stc.studentid = st.id JOIN class cl ON stc.classid = cl.id JOIN level l ON cl.levelid = l.id JOIN course c ON l.id = c.levelid JOIN subject sb ON c.subjectid = sb.id JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id JOIN semester sm ON stc.semesterid = sm.id JOIN session sn ON sm.sessionid = sn.id WHERE sm.sessionid = ? AND cl.id = ? GROUP BY cl.id, sb.id, st.id ORDER BY subjectname, score DESC LIMIT 9223372036854775807 -- MariaDB bug workaround ) ordered JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init ORDER BY subjectname, score DESC "); $res->execute([ $session, $class ]); } $report_title = $termno == 3 ? "End of Year Results" : "End of Term Results"; ################################################################################ # Get scores and put in array with required output structure # ################################################################################ $res = $pdo->prepare("SELECT st.id as stid , concat_ws(' ', st.lastname, st.firstname, st.othername) as stname , st.image , cl.classname , st.dob , st.matricno , sc.classid , l.id as level , sn.sessionname , sm.semestername , sm.date_until , sm.semestername+0 as term , c.subjectid , s.subjectname , exam , score FROM result r JOIN ( student_class sc JOIN class cl ON sc.classid = cl.id JOIN level l ON cl.levelid = l.id JOIN course c ON c.levelid = l.id JOIN student st ON sc.studentid = st.id JOIN semester sm ON sc.semesterid = sm.id JOIN session sn ON sm.sessionid = sn.id JOIN subject s ON c.subjectid = s.id ) ON r.studentclassid = sc.id AND r.courseid = c.id WHERE sn.id = ? AND studentid = ? #AND sm.date_until = ? AND sm.semestername+0 <= ? AND cl.id = ? ORDER BY c.levelid, sc.id, c.subjectid, sc.semesterid, exam "); ############################################################################# ############################################################################# $res->execute( [ $session, $student, $termno, $clid ] ); $data = []; // get data common to all rows from first row $r = $res->fetch(); if ($r) { $studentname = $r['stname']; $studentdob = $r['dob']; $studentmatricno = $r['matricno']; $studentlevel = $r['classname']; $studentsession = $r['sessionname']; $studentterm = "- Term $termno"; $nextterm = $r['date_until']; $passport = "images/" . $r['image']; ### provide image path here $level = $r['level']; // then process the rest of the row data in the first and remaining rows do { if (!isset($data[ $r['subjectid'] ])) { $data[ $r['subjectid'] ] = [ 'name' => $r['subjectname'], #'exams' => ['CA1'=>'', 'CA2'=>'', 'CA3'=>'', 'Exam'=>''], 'exams' => ['CA1'=>'', 'CA2'=>'', 'Exam'=>''], 'scores' => [ 1=>0, 0, 0 ], 'avg' => 0, 'rank' => 0 ]; } if ($r['term'] == $termno && isset($data[$r['subjectid'] ]['exams'][ $r['exam']])) { $data[ $r['subjectid'] ]['exams'][ $r['exam'] ] = $r['score']; } $data[ $r['subjectid'] ]['scores'][$r['term']] += $r['score']; } while ($r = $res->fetch()); // get the avg scores for the class $avgs = classAverageScores($pdo, $clid, $session, $termno); foreach ($avgs as $s => $av) { if (isset($data[$s])) $data[$s]['avg'] = round($av,0); } ###########my ranking here############### /***** manipulating here ************** * * * * / * */ /******************************************** * Derive ordinal suffix for $n * * @param int $n the number * @returns string number with suffix eg 23rd */ function ordinal($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'; } } //function end //$data = []; foreach ($res as $r) { if (!isset($data[$r['subjectname']])) { $data[$r['subjectname']]['students'] = []; } $position = ordinal($r['rank']); $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $position ]; } /* //hffffffffff foreach ($res as $r) { if (!isset($data[$r['subjectname']])) { $data[$r['subjectname']]['students'] = []; } $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $r['rank'] ]; } */ ################################################################################ # Get pupil count # ################################################################################ $res = $pdo->prepare("SELECT COUNT(DISTINCT stc.studentid) AS pupils FROM student_class stc JOIN semester sm ON sm.id = stc.semesterid JOIN result r ON stc.id = r.studentclassid WHERE sm.id = ? AND stc.classid = ? "); $res->execute([ $semester, $clid ]); $pupil_count = $res->fetchColumn(); ################################################################################ # Loop through the data array to construct the output table rows # ################################################################################ $tdata = ''; $n = 1; $grand_total = 0; $subject_count = 0; foreach ($data as $subid => $subdata) { $tdata .= "<tr><td>$n</td><td>{$subdata['name']}</td>"; foreach ($subdata['exams'] as $s) { $tdata .= "<td>" . ($s=='' ? '&ndash;' : $s) . "</td>"; } foreach ($subdata['scores'] as $t => $s) { if ($s==0) $s = ''; $tdata .= "<td>" . ($t <= $termno ? $s : '') . "</td>"; } $temp = array_filter($subdata['scores']); $total = $temp ? round(array_sum($temp)/count($temp)) : 0; $grand_total += $total; if ($total) { list($grade, $comment) = getGradeComment($pdo, $total, $level); $subject_count++; } else { $grade = '-'; $comment = '-'; } $clr = GRADE_COLOUR[$grade] ?? '#000'; $tdata .= "<td>$total</td><td>{$subdata['avg']}</td><td style='color:$clr; font-weight: 600;'>$grade</td><td>$comment</td></tr>\n"; ++$n; } } else { $studentname = ''; $studentdob = ''; $studentmatricno = ''; $studentlevel = ''; $studentsession = ''; $studentsemester = ''; #$nextterm = ''; $studentterm = ''; $passport = ''; $level = '4'; $pupil_count = 0; $grand_total = 0; $subject_count = 1; // $clid = 0; $tdata = "<tr><td colspan='13'>No results found</td></tr>\n"; } ################################################################################ # Get list of gradings # ################################################################################ $res = $pdo->query("SELECT GROUP_CONCAT( grade, concat('&nbsp;(',comments,')'), '&nbsp;', concat(lomark,'&nbsp;-&nbsp;',himark) ORDER BY id SEPARATOR ', ') FROM examgrade #WHERE level_group = ($level > 5) WHERE level_group = ($level > 6) "); $grade_list = $res->fetchColumn(); ################################# $res = $pdo->query('SELECT MIN(date_format(date_from, "%W %M %e, %Y")) as next_term FROM semester WHERE date_from > CURDATE()'); $nextterm = $res->fetchColumn(); ################################# ################################################################################ # Get end of term assessments # ################################################################################ $res = $pdo->prepare("SELECT a.type , a.assessname , e.grade FROM student_class stc JOIN eot_assessment e ON e.studentclassid = stc.id JOIN assessment a ON e.assessmentid = a.id #JOIN semester sm ON sm.id = stc.date_untilid JOIN semester sm ON sm.id = stc.semesterid WHERE stc.studentid = ? AND sm.id = ? "); $res->execute( [ $student, $semester ] ); $ass_data = $res->fetchAll(PDO::FETCH_GROUP); $afflist = $psychlist = ''; if ($ass_data) { $afflist = "<table class='w3-table assess-tbl' > <tr><th>Domain</th><th>Grade</th></tr>\n"; foreach ($ass_data['Affective'] as $agrades) { $afflist .= "<tr><td>{$agrades['assessname']}</td><td>{$agrades['grade']}</td></tr>\n"; } $afflist .= "</table>\n"; $psychlist = "<table class='w3-table assess-tbl' > <tr><th>Domain</th><th>Grade</th></tr>\n"; foreach ($ass_data['Psychomotor'] as $pgrades) { $psychlist .= "<tr><td>{$pgrades['assessname']}</td><td>{$pgrades['grade']}</td></tr>\n"; } $psychlist .= "</table>\n"; }
  21. Olumide
    Thanks for the solution sir. But, when I populated the database with data to have many data. Attached is the position output. From the screenshort, 80 should be 3rd, while 76 should 4th till the lowest. What is likely the problem with it sir?
  22. Olumide
    Oh! I am so sorry sir. I commented it. It work now successfully. You are more than an expert. Thanks so much sir. This solve it.
  23. Olumide
    Thanks so much sir. I got error on line 169: $position = ordinal($r['rank']); Fatal error: Uncaught Error: Call to undefined function ordinal() in C:\xampp\htdocs\local\position.php:169 Stack trace: #0 {main} thrown in C:\xampp\htdocs\local\position.php on line 169
  24. Olumide
    Hmmm... It is a bit hard for me. I tried it but not working. I am a poor novice and not getting it right.
  25. Olumide
    You are indeed a guru sir/ Please how do I include the function in the code? I tried something like this but not getting the result. function ordinal($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'; } } $data = []; foreach ($res as $r) { if (!isset($data[$r['subjectname']])) { $data[$r['subjectname']]['students'] = []; } $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $r['rank'] ]; }
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.