Barand
-
Posts
24,607 -
Joined
-
Last visited
-
Days Won
831
Everything posted by Barand
-
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?
-
Student subject Positioning based on score using php and mysql database
Barand replied to Ponel's topic in PHP Coding Help
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.
-
Trying to get the cookie session functioning: "lost upon reopened browser".
Barand replied to oz11's topic in PHP Coding Help
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);
-
Is the name of your table really "table"?
-
Given the obsolete nature of the code it's surprising it lasted this long. If it's a hosted site it probable that errors are being written to the error log instead of being displayed. Check the log.
-
Here's how I'd do it without the windowing functions Note: if you have A 98 B 98 C 95 the A and B a re ranked 1, C is ranked 3. $grade_class = 'JS1'; $grade_name = 'EMMANUEL OKONJI'; $grade_term = 'First Term'; $grade_year = '2022'; $student_class_position = $conn->prepare( "SELECT rank FROM ( SELECT ord.grade_id , @seq := @seq+1 as seq , @rank := CASE WHEN ord.grand_total = @prev THEN @rank ELSE @seq END as rank , @prev := ord.grand_total as grand_total FROM ( SELECT grade_id , g.grand_total FROM student_grade g ORDER BY grand_total DESC LIMIT 18446744073709551615 ) ord JOIN (SELECT @seq:=0, @rank:=0,@prev:=0) init ) ranked JOIN student_grade g ON g.grade_id = ranked.grade_id WHERE class = ? AND name = ? AND term = ? AND year = ? "); $student_class_position->bind_param('ssss', $grade_class, $grade_name, $grade_term, $grade_year); $student_class_position->execute(); $student_class_position->bind_result($position); $student_class_position->fetch(); echo $position; I used a prepared statement so user data is not inserted into the query (SQL injection attack prevention)
-
Fixing invalid synatx in my Python login window
Barand replied to ScribhneoirIldanach's topic in Other Programming Languages
It isn't even your thread and, just in case you think we are prepared to host your personal Python blog, I'm closing it. -
Go to your profile, click account settings. Signature is in the menu on the left.
-
what would be the best way to store images for a dynamic slideshow.
Barand replied to alexandre's topic in PHP Coding Help
Store them presized to the size you want to display. Don't download images whose sizes are 4800x3600 only to display them as, say, 960x720 - it's going to be unnecessarily slower to download them.
