Barand

As I told you before, you need to get the database right before you embark on the processes - you are not ready yet for complex queries. If you must run before you can crawl then there is an example of ranking queries in the SQL Tutorial link in my signature and also here in this forum.

Does every class have a project and an assigment every term so the score wieightings are consistent? (Your previous sample didn't) And my previous question (acknowledging that the weightings are now 0.1, 0.1, 0.1, 0.1, 0.6) - "If a student scores 80% in CA1, do you store this as 80 and apply the weighting when you produce the report or do you store the weighted score (8%)?" That is the purpose of a database - to store the data you need to produce the outputs you want. The way to get it from the DB to the output is by using queries. So the answer is yes. However, if by "everything", you mean totals, ranks etc as well then, yes you can - if you want to do it stupid. I prefer to do it right. The database tables do not resemble the outputs you want to produce, as you seem to think they should. That's why we use queries and PHP. If you have a results table like this sample (3 students, 3 subjects, one term) +-----------+-----------+------------+-----------+-------------+-------+ | result_id | studentid | semesterid | subjectid | result_type | pcent | +-----------+-----------+------------+-----------+-------------+-------+ | 36 | 936 | 4 | 1 | CA1 | 8 | | 37 | 936 | 4 | 2 | CA1 | 11 | | 38 | 936 | 4 | 3 | CA1 | 5 | | 588 | 936 | 4 | 1 | CA2 | 14 | | 589 | 936 | 4 | 2 | CA2 | 12 | | 590 | 936 | 4 | 3 | CA2 | 5 | | 1140 | 936 | 4 | 1 | Exam | 49 | | 1141 | 936 | 4 | 2 | Exam | 44 | | 1142 | 936 | 4 | 3 | Exam | 17 | | 82 | 2393 | 4 | 1 | CA1 | 12 | | 83 | 2393 | 4 | 2 | CA1 | 8 | | 84 | 2393 | 4 | 3 | CA1 | 9 | | 634 | 2393 | 4 | 1 | CA2 | 12 | | 635 | 2393 | 4 | 2 | CA2 | 9 | | 636 | 2393 | 4 | 3 | CA2 | 11 | | 1186 | 2393 | 4 | 1 | Exam | 38 | | 1187 | 2393 | 4 | 2 | Exam | 34 | | 1188 | 2393 | 4 | 3 | Exam | 42 | | 146 | 3391 | 4 | 1 | CA1 | 8 | | 147 | 3391 | 4 | 2 | CA1 | 8 | | 148 | 3391 | 4 | 3 | CA1 | 8 | | 698 | 3391 | 4 | 1 | CA2 | 11 | | 699 | 3391 | 4 | 2 | CA2 | 11 | | 700 | 3391 | 4 | 3 | CA2 | 13 | | 1250 | 3391 | 4 | 1 | Exam | 50 | | 1251 | 3391 | 4 | 2 | Exam | 49 | | 1252 | 3391 | 4 | 3 | Exam | 54 | +-----------+-----------+------------+-----------+-------------+-------+ then with a query ... SELECT studentid , subject , SUM(IF(result_type='CA1', pcent, null)) as CA1 , SUM(IF(result_type='CA2', pcent, null)) as CA2 , SUM(IF(result_type='Assignment', pcent, null)) as Assignment , SUM(IF(result_type='Project', pcent, null)) as Project , SUM(IF(result_type='Exam', pcent, null)) as Exam , SUM(pcent) as total FROM result r JOIN student st USING (studentid) JOIN subject USING (subjectid) WHERE semesterid = 4 GROUP BY lastname, subjectid; you can produce something that resembles the output you want ... +-----------+---------------+-----+-----+------------+---------+------+-------+ | studentid | subject | CA1 | CA2 | Assignment | Project | Exam | total | +-----------+---------------+-----+-----+------------+---------+------+-------+ | 2393 | English Lang. | 12 | 12 | | | 38 | 62 | | 2393 | Mathematics | 8 | 9 | | | 34 | 51 | | 2393 | BST | 9 | 11 | | | 42 | 62 | | 936 | English Lang. | 8 | 14 | | | 49 | 71 | | 936 | Mathematics | 11 | 12 | | | 44 | 67 | | 936 | BST | 5 | 5 | | | 17 | 27 | | 3391 | English Lang. | 8 | 11 | | | 50 | 69 | | 3391 | Mathematics | 8 | 11 | | | 49 | 68 | | 3391 | BST | 8 | 13 | | | 54 | 75 | +-----------+---------------+-----+-----+------------+---------+------+-------+

You have introduced 3 new placeholders into the query so you need to provide parameter values for them

You cannot use column aliases in a WHERE clause (they don't exist at that stage of the process). You need to repeat the expression.

You are changing the table back to a spreadsheet again. Each row should contain the marks obtained for one event (CA1, CA2, Assignment, Project or Exam) only and a column to indicate which as I suggested earlier (now extended) `result_type` enum('CA1', 'CA2', 'Assignment', 'Project', 'Exam') DEFAULT NULL COMMENT '1 = CA1, 2 = CA2, 3 = Assignment, 4 = Project, 5 = Exam', Totals and ranks are derived and not stored. You get those by querying the result table. You should have something like this CREATE TABLE IF NOT EXISTS `result` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studentid` int(11) DEFAULT NULL, `classid` int(11) DEFAULT NULL, `subjectid` int(11) DEFAULT NULL, `result_type` enum('CA1', 'CA2', 'Assignment', 'Project', 'Exam') DEFAULT NULL COMMENT '1 = CA1, 2 = CA2, 3 = Assignment, 4 = Project, 5 = Exam', `termid` INT(11) DEFAULT NULL, `percent` DECIMAL(4,1) DEFAULT NULL, PRIMARY KEY (`id`) KEY idx_result_student (studentid), KEY idx_result_class (classid), KEY idx_result_subject (subjectid), KEY idx_result_term (termid), ) ENGINE=InnoDB; I would also create a "term" table so you can have the term dates CREATE TABLE `term` ( id INT not null auto_increment, year INT, termno TINYINT, termstart DATE, termend DATE, PRIMARY KEY (id), ) ENGINE=InnoDB; This still begs the question of how to store the percent score attained in each test. On your sample report you have CA1 and CA2 each contributing 20% of the final total and the Exam contibuting 60%, therefore the weightings are 0.2, 0.2, 0.6 respectively. If a student score 80% in CA1, do you store this as 80 and apply the weighting when you produce the report or do you store the weighted score (16%) (Your example had no assignment or project scores so I don't know what happens with those)

That is certainly an improvement. What are the "exam" and "test" columns for? On your results output report that you showed us there were CA1, CA2 and Exam marks so I would expect a row in the result table would contain the marks achieved for one of these, so intead of those two columns, one would suffice. For example... `result_type` enum('CA1','CA2','Exam') DEFAULT NULL COMMENT '1 = CA1, 2 = CA2, 3 = Exam', I also think you need the "term" in there too, so you know when the marks were achieved. "Rank" is derived nd should not be stored.

They should be the size required to store the content. Do you know anyone whose name or phone number is 150 chars long? Also many of those should not be varchar at all. For example, DOB should be DATE type. The classid in the class table is INT, do why is the foreign key classid in the students table varchar(1000)? They should match (ie both should be int) Does every student have their own personal logo? The whole database needs normalizing and rebuilding correctly to make it fit for purpose. When you've done that, come back to the re-coding of the pages.

Got this on a couple of tables when trying to load your dump. But with those ridiculous column sizes like VARCHAR(10000) for a date of birth, and others, I am not surprised.

If you want to make it better, start by normalizing your data. The results table in particular is a mess. Most of the items in it have nothing to do with results, it shouldn't contain repeating fields (like the marks columns) and it shouldn't contain derived data (like totals). It's a spreadsheet, not a relational DB table, in its present form. Of course, when the data is normalized correctly, the code will have to be rewritten - another advantage of normalization.

Bye.

Don't store derived data (like totals). Calculate by query when required. And ...

When you post code, use the <> button. Why have you got two almost identical queries for the student data? You are running several queries inside the loop to process the second student query - don't do that, use joins so you have a single query What is the point of this line from your code? ... echo $sqresult['DepartmentName'] == " " ? " " : " "; What are you data table structures/definitions?

What do you have so far by way of code and data?

As you have resurrected this thread, you may as well read it and get some ideas.

I can give you a complete example script so you can see how it all fits together plus some test data. Output sample Code <?php const HOST = 'localhost'; const USERNAME = '????'; const PASSWORD = '????'; const DBNAME = '????'; // PDO database connection // $dsn = "mysql:dbname=".DBNAME."; host=".HOST."; charset=utf8"; $pdo = new pdo($dsn, USERNAME, PASSWORD, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]); ################################################################################ # HANDLE AJAX REQUEST # ################################################################################ if (isset($_GET['ajax'])) { if ($_GET['ajax']=='getorders') { $res = $pdo->prepare("SELECT order_id as oid , date_format(order_date, '%b %D %Y') as odate FROM ba_orders WHERE client_id = ? ORDER BY order_date "); $res->execute( [ $_GET['id'] ] ); $results = $res->fetchAll(); if (count($results)==0) $results = [['No orders', '']]; exit(json_encode($results)); } else exit('INVALID REQUEST'); } ################################################################################ # GET THE CLIENT DATA FOR DISPLAY # ################################################################################ $cdata = ''; $res = $pdo->query("SELECT client_id , name FROM ba_client ORDER BY name "); foreach ($res as $clt) { $cdata .= "<tr><td>{$clt['client_id']}</td> <td class='client-name' data-id='{$clt['client_id']}'>{$clt['name']}</td> </tr>\n"; } ?> <!DOCTYPE=html> <html> <head> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script> <script type='text/javascript'> $(function() { $(".client-name").click( function() { let cid = $(this).data("id") $(".client-name").css("background-color", "white") $(this).css("background-color","#eee") $.get ( "", // request sent to self {"ajax":"getorders", "id":cid}, function(resp) { $("#orderbody").html("") // clear output table's tbody section $.each(resp, function(k, row) { let newrow = $("<tr>") // create new row $.each(row, function(k1, col) { let newcol = $("<td>", {"text":col}) // ceate new col with content $(newrow).append(newcol) // append to row }) $("#orderbody").append(newrow) // append row to tbody }) }, "JSON" ) }) }) </script> <style type='text/css'> td.client-name { cursor: pointer; color: blue; } td.client-name:hover { border: 1px solid red; text-decoration: underline; } </style> </head> <body> <header class='w3-container w3-padding'> </header> <div class='w3-container w3-padding'> <div class='w3-row-padding'> <div class='w3-col w3-half w3-padding'> <h3>Clients</h3> <table class='w3-table w3-bordered'> <thead> <tr class='w3-blue-gray'><th>Client ID</th><th>Client Name</th></tr> </thead> <tbody id='clbody'> <?=$cdata?> </tbody> </table> </div> <div class='w3-col w3-half w3-padding'> <h3>Orders</h3> <table class='w3-table w3-bordered'> <thead> <tr class='w3-blue-gray'><th>Order ID</th><th>Order Date</th></tr> </thead> <tbody id='orderbody'> </tbody> </table> </div> </div> </div> </body> </html> Data CREATE TABLE `ba_client` ( `client_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`client_id`) ) ENGINE=InnoDB; INSERT INTO `ba_client` VALUES (1,'Dancer'),(2,'Prancer'),(3,'Dasher'),(4,'Vixen'),(5,'Comet'),(6,'Cupid'),(7,'Donner'),(8,'Blitzen'); CREATE TABLE `ba_orders` ( `order_id` int(11) NOT NULL AUTO_INCREMENT, `client_id` int(11) DEFAULT NULL, `order_date` date NOT NULL DEFAULT '0000-00-00', PRIMARY KEY (`order_id`), KEY `idx_order_client` (`client_id`) ) ENGINE=InnoDB; INSERT INTO `ba_orders` VALUES (1,5,'2022-12-01'),(2,5,'2022-12-02'),(3,7,'2022-12-03'),(4,3,'2022-12-04'),(5,6,'2022-12-05'), (6,4,'2022-12-06'),(7,7,'2022-12-07'),(8,2,'2022-12-08'),(9,4,'2022-12-09'),(10,3,'2022-12-10'),(11,3,'2022-12-11'), (12,4,'2022-12-12'),(13,7,'2022-12-13'),(14,2,'2022-12-14'),(15,6,'2022-12-15'),(16,8,'2022-12-16');

IDs in an html document must be unique. Every time you set the content of $('#Point_Marks') you are writing to the same element. A better approach is to json encode your query results and return the json object in your response, then do something like this function(resp) { $("#orderbody").html("") // clear output table's tbody section tag $.each(resp, function(k, row) { let newrow = $("<tr>") // create new row $.each(row, function(k1, col) { let newcol = $("<td>", {"text":col}) // ceate new col with content $(newrow).append(newcol) // append to row }) $("#orderbody").append(newrow) // append row to tbody }) },

You need to put it in quotes otherwise it accepts the value up to the first space only <a href="StatementEntry.php?ID=<?=$ID?>" title="<?=$AddDate?>"> ^ ^

You should not store derived data (such as totals) in a database. You should get these values by using a query. Otherwise you are storing the data twice (as a total and as the data from which the total is derived) giving the risk of there being two versions of the "truth" should the two get out of synch for any reason. The subquery... ( SELECT c.name , c.client_id , min(o.order_date) as minord FROM client c LEFT JOIN orders o USING (client_id) GROUP BY c.client_id ) cl effectively creates a temporary table called "cl" which looks like this, containing the the client and date of their first order... TEMP TABLE: cl +---------+-----------+------------+ | name | client_id | minord | +---------+-----------+------------+ | Dancer | 1 | NULL | | Prancer | 2 | 2022-12-08 | | Dasher | 3 | 2022-12-04 | | Vixen | 4 | 2022-12-06 | | Comet | 5 | 2022-12-01 | | Cupid | 6 | 2022-12-05 | | Donner | 7 | 2022-12-03 | | Blitzen | 8 | NULL | +---------+-----------+------------+ Now the query simplifies to this... SELECT cl.name , count(order_id) as points FROM cl -- our temporary table LEFT JOIN orders o ON order_date > minord GROUP BY cl.client_id; which counts, for each client, the number of orders placed since they placed their first order. The LEFT JOIN is to list all clients, even if they have no points.

You don't need to change anything. Just run a query when you need to knw each client's points. The query needs to count thenumber of orders placed after each client's first order and give them a point for each order. DATA TABLE: client TABLE: orders +-----------+---------+ +----------+-----------+------------+ | client_id | name | | order_id | client_id | order_date | +-----------+---------+ +----------+-----------+------------+ | 1 | Dancer | | 1 | 5 | 2022-12-01 | | 2 | Prancer | | 2 | 5 | 2022-12-02 | | 3 | Dasher | | 3 | 7 | 2022-12-03 | | 4 | Vixen | | 4 | 3 | 2022-12-04 | | 5 | Comet | | 5 | 6 | 2022-12-05 | | 6 | Cupid | | 6 | 4 | 2022-12-06 | | 7 | Donner | | 7 | 7 | 2022-12-07 | | 8 | Blitzen | | 8 | 2 | 2022-12-08 | +-----------+---------+ | 9 | 4 | 2022-12-09 | | 10 | 3 | 2022-12-10 | | 11 | 3 | 2022-12-11 | | 12 | 4 | 2022-12-12 | | 13 | 7 | 2022-12-13 | | 14 | 2 | 2022-12-14 | | 15 | 6 | 2022-12-15 | +----------+-----------+------------+ QUERY SELECT cl.name , count(order_id) as points FROM ( SELECT c.name , c.client_id , min(o.order_date) as minord FROM client c LEFT JOIN orders o USING (client_id) GROUP BY c.client_id ) cl LEFT JOIN orders o ON order_date > minord GROUP BY cl.client_id; +---------+--------+ | name | points | +---------+--------+ | Dancer | 0 | | Prancer | 7 | | Dasher | 11 | | Vixen | 9 | | Comet | 14 | | Cupid | 10 | | Donner | 12 | | Blitzen | 0 | +---------+--------+

One post will suffice.

After a brief scan, the only error I can see is that your second $headers value overwrites the first. For future reference, pictures of your code are as useful as a chocolate teapot.

Nothing can possibly go wrong in production? Just in case, leave error checking on but change from displaying the errors to logging them instead.

Make sure your error reporting is ON in your php.ini file and let it tell you what's wrong - then fix the errors.

You should check that $_GET['alias'] exists before attempting to use it. For example, this sets $alias to a default value of '0' if it does not exist. $alias = $_GET['alias'] ?? '0''

Have you got php error reporting turned on? Have you looked at your browser developer tools ( network tab ) to check that the ajax info is being passed OK? Check that your queries are working correctly - put this code just before your mysqli_connect mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);