Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,599

  • Joined

  • Last visited

  • Days Won

    828

 Content Type 

Everything posted by Barand

  1. Barand
    Put this line just before the mysqli connection code... mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); Ensure error_reporting is ON. You should now get error messages if there are problems with a query.
  2. Barand
    PS You aren't doing yourself any favours by insisting on mysqli. PDO is a superior library and easier to use. While you are still learning, put your effort into PDO
  3. Barand
    Change $pdo = pdoConnect(); to $pdo = new mysqli(HOST, USERNAME, PASSWORD, DATABASE); Job done.
  4. Barand
    BTW, it seems a pity that that those students who attain a C6 grade should receive only an anagram instead of a Credit
  5. Barand
    @sule, Here are two version of the program A single loop tracking changes in studentid (as I suggested) Stores data in a muti-dimensional array and uses nested foreach() loops Version 1 <?php define("HOST", 'localhost'); define("USERNAME", '????'); define("PASSWORD", '????'); define("DATABASE", '????'); // default database name function pdoConnect($dbname=DATABASE) { $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $db->setAttribute(PDO::MYSQL_ATTR_LOCAL_INFILE, true); return $db; } $pdo = pdoConnect(); $res = $pdo->query("SELECT marks.acayear , marks.semester , marks.form , marks.class , marks.studentid , marks.name , marks.subject , marks.class_score1 , marks.exam_score1 , ROUND(marks.total, 1) as total , marks.grade , marks.remarks , RANK() OVER (PARTITION BY marks.acayear, marks.semester, marks.lessonsid ORDER BY marks.total DESC) as rank FROM marks WHERE marks.acayear='2024/2025' AND marks.semester='1' AND marks.form='3' AND marks.class='SCI A' ORDER BY name, subject; "); $previd = 0; $tdata = ''; foreach ($res as $r) { if ($r['studentid'] != $previd) { if ($previd != 0) { // if not the first $tdata .= "\n</table>\n</div>\n</div>\n\n"; // close preceding report } $tdata .= outputReportHeading(...array_slice($r, 0, 6)); $previd = $r['studentid']; } $tdata .= outputSubject(...array_slice($r, 6)); // close last report } $tdata .= "\n</table>\n</div>\n</div>\n\n"; # # FUNCTIONS # function outputReportHeading($acayear, $semester, $form, $class, $studentid, $name) { return <<<HEAD <div class='report w3-container w3-white'> <div class='report-head'> SAVELUGU SENIOR HIGH SCHOOL </div> <div class='report-head'> $acayear ACADEMIC YEAR </div> <div class='w3-row w3-margin-top'> <div class='w3-col m1 w3-center'> [SCHOOL<br>CREST] </div> <div class='w3-col m5 report-subhead'> Student ID: <span class='hval'>$studentid</span> <br> Name: <span class='hval'>$name</span> <br> Semester: <span class='hval'>$semester</span> </div> <div class='w3-col m5 report-subhead'> House: <span class='hval'></span> <br> Gender: <span class='hval'></span> <br> Class: <span class='hval'>$form $class</span> </div> <div class='w3-col m1 w3-center'> [PHOTO] </div> </div> <div class='report-head w3-margin-top'> STUDENT TERMINAL REPORT </div> <div class='w3-responsive'> <table border='1' class='scores'> <tr> <th>Subject</th> <th class='w3-hide-small'>Class Score</th> <th class='w3-hide-small'>Exam Score</th> <th>Total</th> <th>Grade</th> <th>Pos</th> <th>Remarks</th> </tr> HEAD; } function outputSubject($subject, $class_score1, $exam_score1, $total, $grade, $remarks, $rank) { return <<<SUB <tr> <td>$subject</td> <td class='w3-hide-small ca'>$class_score1</td> <td class='w3-hide-small ca'>$exam_score1</td> <td class='ca'>$total</td> <td class='ca'>$grade</td> <td class='ca'>$rank</td> <td>$remarks</td> </tr> SUB; } ?> <!DOCTYPE html> <html lang="en"> <head> <title>Example Reports 1</title> <meta charset="utf-8"> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <style type='text/css'> body { background-color: #FCF8E8; } .report { margin: 16px; padding: 8px; border: 1px solid gray; } .report-head { font-size: 18pt; font-weight: 600; text-align: center; } .report-subhead { font-size: 14pt; font-weight: 300; } .hval { font-weight: 600; color: blue; } .scores { width: 100%; border-collapse: collapse; } th { padding: 8px 2px; background-color: gray; color: white; } td { padding: 8px 4px; } .ca { text-align: center; } </style> </head> <body> <?= $tdata ?> </body> </html> Version 2 <?php define("HOST", 'localhost'); define("USERNAME", '????'); define("PASSWORD", '????'); define("DATABASE", '????'); // default database name function pdoConnect($dbname=DATABASE) { $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $db->setAttribute(PDO::MYSQL_ATTR_LOCAL_INFILE, true); return $db; } $pdo = pdoConnect(); $res = $pdo->query("SELECT marks.acayear , marks.semester , marks.form , marks.class , marks.studentid , marks.name , marks.subject , marks.class_score1 , marks.exam_score1 , ROUND(marks.total, 1) as total , marks.grade , marks.remarks , RANK() OVER (PARTITION BY marks.acayear, marks.semester, marks.lessonsid ORDER BY marks.total DESC) as rank FROM marks WHERE marks.acayear='2024/2025' AND marks.semester='1' AND marks.form='3' AND marks.class='SCI A' ORDER BY name, subject; "); $tdata = ''; # # store the data in a 3D array # $data = []; foreach ($res as $r) { if (!isset($data[$r['studentid']])) { $data[$r['studentid']] = [ 'head' => array_slice($r, 0, 6), 'scores' => [] ]; } $data[$r['studentid']]['scores'][] = array_slice($r, 6); } # echo '<pre>' . print_r($data, 1) . '</pre>'; // uncomment to view array structure # # PROCESS THE ARRAY # foreach ($data as $sdata) { $tdata .= outputReportHeading(...$sdata['head']); foreach ($sdata['scores'] as $subdata) { $tdata .= outputSubject(...$subdata); } $tdata .= "\n</table>\n</div>\n</div>\n\n"; } # # FUNCTIONS # function outputReportHeading($acayear, $semester, $form, $class, $studentid, $name) { return <<<HEAD <div class='report w3-container w3-white'> <div class='report-head'> SAVELUGU SENIOR HIGH SCHOOL </div> <div class='report-head'> $acayear ACADEMIC YEAR </div> <div class='w3-row w3-margin-top'> <div class='w3-col m1 w3-center'> [SCHOOL<br>CREST] </div> <div class='w3-col m5 report-subhead'> Student ID: <span class='hval'>$studentid</span> <br> Name: <span class='hval'>$name</span> <br> Semester: <span class='hval'>$semester</span> </div> <div class='w3-col m5 report-subhead'> House: <span class='hval'></span> <br> Gender: <span class='hval'></span> <br> Class: <span class='hval'>$form $class</span> </div> <div class='w3-col m1 w3-center'> [PHOTO] </div> </div> <div class='report-head w3-margin-top'> STUDENT TERMINAL REPORT </div> <div class='w3-responsive'> <table border='1' class='scores'> <tr> <th>Subject</th> <th class='w3-hide-small'>Class Score</th> <th class='w3-hide-small'>Exam Score</th> <th>Total</th> <th>Grade</th> <th>Pos</th> <th>Remarks</th> </tr> HEAD; } function outputSubject($subject, $class_score1, $exam_score1, $total, $grade, $remarks, $rank) { return <<<SUB <tr> <td>$subject</td> <td class='w3-hide-small ca'>$class_score1</td> <td class='w3-hide-small ca'>$exam_score1</td> <td class='ca'>$total</td> <td class='ca'>$grade</td> <td class='ca'>$rank</td> <td>$remarks</td> </tr> SUB; } ?> <!DOCTYPE html> <html lang="en"> <head> <title>Example Reports 2</title> <meta charset="utf-8"> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <style type='text/css'> body { background-color: #FCF8E8; } .report { margin: 16px; padding: 16px; border: 1px solid gray; } .report-head { font-size: 18pt; font-weight: 600; text-align: center; } .report-subhead { font-size: 14pt; font-weight: 300; } .hval { font-weight: 600; color: blue; } .scores { width: 100%; border-collapse: collapse; } th { padding: 8px 2px; background-color: gray; color: white; } td { padding: 8px 4px; } .ca { text-align: center; } </style> </head> <body> <?= $tdata ?> </body> </html> Samples
  6. Barand
    Use the <> button when posting code
  7. Barand
    I've just described a method to you. What have you now tried? (or are you just going to keep repeating the same question without trying the suggestion?)
  8. Barand
    As you loop throught the query results, check for a change of studentid. Each time to reach a new student, out put the report headings (name, photo etc) then continue listing subject rows until the next change. Pseudocode... previd = 0; foreach result row { if studentid != previd { previd = studentid output report headings } output subject data }
  9. Barand
    I couldn't find anything wrong with your query, although I had to limit my version to the marks table only as the other tables required seem to have fallen down the back of your sofa. I assumed you want the positions in their class. SELECT -- marks.acayear -- , marks.semester -- , marks.form -- , marks.class marks.studentid , marks.name , marks.subject , marks.class_score1 , marks.exam_score1 , marks.total , marks.remarks , RANK() OVER (PARTITION BY marks.lessonsid, marks.acayear, marks.form, marks.semester, marks.subject, marks.class ORDER BY marks.total DESC) as rank FROM marks WHERE marks.acayear='2024/2025' AND marks.semester='1' AND marks.form='3' AND marks.class='SCI A' ORDER BY name, SUBJECT; +-------------+--------------------------+----------------+--------------+-------------+-------+-----------+------+ | studentid | name | subject | class_score1 | exam_score1 | total | remarks | rank | +-------------+--------------------------+----------------+--------------+-------------+-------+-----------+------+ | 82611900422 | ABDUL-HANAN SA-EED | BIOLOGY | 0 | 0 | 0 | | 23 | | 82611900422 | ABDUL-HANAN SA-EED | CHEMISTRY | 0 | 0 | 0 | | 29 | | 82611900422 | ABDUL-HANAN SA-EED | ELECTIVE MATHS | 0 | 0 | 0 | | 29 | | 82611900422 | ABDUL-HANAN SA-EED | ENGLISH LANG | 0 | 0 | 0 | | 29 | | 82611900422 | ABDUL-HANAN SA-EED | ICT | 0 | 0 | 0 | | 26 | | 82611900422 | ABDUL-HANAN SA-EED | INT SCIENCE | 0 | 0 | 0 | | 29 | | 82611900422 | ABDUL-HANAN SA-EED | MATHEMATICS | 0 | 0 | 0 | | 25 | | 82611900422 | ABDUL-HANAN SA-EED | PHYSICS | 0 | 0 | 0 | | 27 | | 82611900422 | ABDUL-HANAN SA-EED | SOCIAL STUDIES | 0 | 0 | 0 | | 26 | +-------------+--------------------------+----------------+--------------+-------------+-------+-----------+------+ | 80106802422 | ABDUL-RAHMAN NASIBA | BIOLOGY | 23.4 | 53.9 | 77.3 | Very Good | 3 | | 80106802422 | ABDUL-RAHMAN NASIBA | CHEMISTRY | 16.8 | 53.2 | 70 | Very Good | 6 | | 80106802422 | ABDUL-RAHMAN NASIBA | ELECTIVE MATHS | 16.8 | 53.9 | 70.7 | Very Good | 11 | | 80106802422 | ABDUL-RAHMAN NASIBA | ENGLISH LANG | 20.1 | 39.2 | 59.3 | Credit | 18 | | 80106802422 | ABDUL-RAHMAN NASIBA | ICT | 16.8 | 23.8 | 40.6 | Pass | 25 | | 80106802422 | ABDUL-RAHMAN NASIBA | INT SCIENCE | 20.1 | 39.2 | 59.3 | Credit | 14 | | 80106802422 | ABDUL-RAHMAN NASIBA | MATHEMATICS | 16.8 | 30.8 | 47.6 | Pass | 21 | | 80106802422 | ABDUL-RAHMAN NASIBA | PHYSICS | 16.8 | 53.9 | 70.7 | Very Good | 8 | | 80106802422 | ABDUL-RAHMAN NASIBA | SOCIAL STUDIES | 13.5 | 23.1 | 36.6 | Fail | 25 | +-------------+--------------------------+----------------+--------------+-------------+-------+-----------+------+ | 82606700822 | ABUBAKARI ABDUL HALIK | BIOLOGY | 13.5 | 45.5 | 59 | Credit | 11 | | 82606700822 | ABUBAKARI ABDUL HALIK | CHEMISTRY | 22.8 | 53.9 | 76.7 | Very Good | 5 | | 82606700822 | ABUBAKARI ABDUL HALIK | ELECTIVE MATHS | 13.5 | 39.2 | 52.7 | Crdeit | 26 | | 82606700822 | ABUBAKARI ABDUL HALIK | ENGLISH LANG | 20.1 | 46.2 | 66.3 | Good | 14 | | 82606700822 | ABUBAKARI ABDUL HALIK | ICT | 13.5 | 46.2 | 59.7 | Credit | 15 | | 82606700822 | ABUBAKARI ABDUL HALIK | INT SCIENCE | 22.8 | 53.9 | 76.7 | Very Good | 5 | | 82606700822 | ABUBAKARI ABDUL HALIK | MATHEMATICS | 23.1 | 45.5 | 68.6 | Good | 10 | | 82606700822 | ABUBAKARI ABDUL HALIK | PHYSICS | 13.5 | 23.8 | 37.3 | Fail | 24 | | 82606700822 | ABUBAKARI ABDUL HALIK | SOCIAL STUDIES | 16.8 | 30.8 | 47.6 | Pass | 21 | +-------------+--------------------------+----------------+--------------+-------------+-------+-----------+------+ | 80115900922 | ALAWI ABDALLAH | BIOLOGY | 10.2 | 37.8 | 48 | Pass | 13 | | 80115900922 | ALAWI ABDALLAH | CHEMISTRY | 22.8 | 38.5 | 61.3 | Credit | 8 | | 80115900922 | ALAWI ABDALLAH | ELECTIVE MATHS | 23.1 | 39.2 | 62.3 | Credit | 20 | | 80115900922 | ALAWI ABDALLAH | ENGLISH LANG | 16.8 | 53.9 | 70.7 | Very Good | 12 | | 80115900922 | ALAWI ABDALLAH | ICT | 13.5 | 39.2 | 52.7 | Crdeit | 22 | | 80115900922 | ALAWI ABDALLAH | INT SCIENCE | 19.8 | 37.8 | 57.6 | Credit | 16 | | 80115900922 | ALAWI ABDALLAH | MATHEMATICS | 26.4 | 46.9 | 73.3 | Very Good | 3 | | 80115900922 | ALAWI ABDALLAH | PHYSICS | 16.8 | 38.5 | 55.3 | Credit | 17 | | 80115900922 | ALAWI ABDALLAH | SOCIAL STUDIES | 13.5 | 23.8 | 37.3 | Fail | 24 | +-------------+--------------------------+----------------+--------------+-------------+-------+-----------+------+ | etc...
  10. Barand
    You can't concatenate an if() statement like that. Try $message = 'Message goes here' . "\r\n" . 'Name: ' . $name . "\r\n"; if ($doesthishavedata != '') { $message .= 'Does this have data: ' . $doesthishavedata . "\r\n"; } $message .= 'something else: ' . $hasdata . "\r\n" .
  11. Barand
    An "if() construct" often comes in useful at times like this.
  12. Barand
    Inser queries cannot have a WHERE clause. You need an UPDATE query
  13. Barand
    You don't need the braces so long as there are only single statements to be executed but only if they are in the same <?php .. ?> block of code. EG this works... <?php if (date('d')==24) echo 'today'; else echo 'Not today'; ?>
  14. Barand
    Are you using <?php ... ?> or <? ... ?> in your code when you enter a php code section? The short version (<?) is no longer a default and requires an ini file setting to enable.
  15. Barand
    I'd do something like this... <?php $data = [ '46.105.73.18:27015' => [ 'gq_address' => '46.105.73.18', 'gq_dedicated' => '', 'gq_gametype' => '', 'gq_hostname' => '', 'gq_joinlink' => 'steam://connect/46.105.73.18:27015/' , 'gq_mapname' => '', 'gq_maxplayers' => '', 'gq_mod' => '', 'gq_name' => 'Counter-Strike: Source' , 'gq_numplayers' => '', 'gq_online' => '', 'gq_password' => '', 'gq_port_client' => 27015 , 'gq_port_query' => 27015 , 'gq_protocol' => 'source' , 'gq_transport' => 'udp' , 'gq_type' => 'css' , 'players' => Array (), 'teams' => Array () ] ] ; $tdata = ''; foreach ($data as $k => $v) { $v = array_filter($v); // get rid of blank values $tdata .= "<table border='1'> <tr><th>Array key</th><th>" . join('</th><th>', array_keys($v)) . "</th></tr>\n"; // output headings $tdata .= "<tr><th class='rowth'>$k</th><td>" . join('</td><td>', array_values($v)) . "</td></tr> // output values </table>\n"; } ?> <html lang='en'> <head> <meta 'charset'='utf-8'> <title>Example</title> <style type='text/css'> table { border-collapse: collapse; margin-bottom: 10px; } th { background-color: #444; color: white; padding: 8px; } .rowth { background-color: #888; } td { padding: 8px; text-align: center; } </style> </head> <body> <?= $tdata ?> </body> </html> Giving ...
  16. Barand
    I fell foul of that this morning. It worked fine yesterday when I posted the query but when I loaded some more data and ran it again after midnight I got nothing. The last date I had was 2025-01-26 so -7 DAY worked fine on the 2nd Feb but not on the 3rd. On the subject of temporary tables, have a look at WITH RECURSIVE. There may be occasions when you want to list, say, total sales for each day last month and show a zero total for days with no sales. You can't just sum, grouping by the dates, as you won't get output if there is no data fora date. You need a table containing all the dates in the month and use a left join. WITH RECURSIVE gives a great way of create such a temporary date table on the fly.
  17. Barand
    Could be there is no data in the table for the last 7 days. Try "- INTERVAL 1 MONTH"
  18. Barand
    You don't need a separate table to specify the structure, your board table already contains the structure by virtue of the parentid column. Just needs a recursive function to resolve... include 'db_inc.php' ; $pdo = mdbConnect('jodunno'); // connect to database $res = $pdo->query("SELECT parentID, boardID, title FROM wbb1_1_board ORDER BY parentID, title"); $brds = []; foreach ($res as $r) { $brds[$r['parentID']][] = [ 'id' => $r['boardID'], 'title' => $r['title']]; } // echo '<pre>' . print_r($brds, 1) . '</pre>'; echo "<ul>\n"; outputBoard($brds, 0); echo "</ul>\n"; function outputBoard(&$brds, $parent) { foreach ($brds[$parent] as $b) { echo "<li>{$b['title']}</li>\n"; if (isset($brds[$b['id']])) { // if this is a parent board echo "<ul>\n"; outputBoard($brds, $b['id']); // recursively output the child boards echo "</ul>\n"; } } }
  19. Barand
    After about 3 hours of waiting I killed the process as the SQL server never showed a status other than "idle". I think it hit a problem and got itself into an infinite loop doing nothing. To give myself some data to work with, I managed to extact the table structure , the first 3,400 and the last 600 records from the sql file. I could have done all of them but it (thankfully) uses multiple row inserts (1700 at a time) and it takes an age scrolling through the text to find each block's start and end then select the block. There are about 200 such blocks and each takes about 2.5 seconds to load the data - so the whole load should have taken 8-9 minutes. Enough of the excuses. I finally came up with a solution using the post table. The first part (WITH ...) creates a temporary table called "plast" which contains a row for each threadID with the latest date of all the posts for the thread. The main part of the query (SELECT ...) matches the threadid/latest date with the post table to find the matching post and also joins to the thread table to pick uo thread info. Finally, I limit the output to just those dates in the last 7 days. (Apologies for screwing up the text encoding along the way - eg "Jürgen Peters". It's only test data.) WITH plast AS ( SELECT threadID , MAX(time) as latest FROM wbb1_1_post GROUP BY threadID ) SELECT t.threadID , t.topic , p.userID , p.username , p.postid , FROM_UNIXTIME(p.time) AS time , FROM_UNIXTIME(plast.latest) AS latest FROM wbb1_1_post p JOIN wbb1_1_thread t ON p.threadID = t.threadID JOIN plast ON plast.threadid = p.threadid AND plast.latest = p.time WHERE FROM_UNIXTIME(p.time) > CURDATE() - INTERVAL 7 DAY ; +----------+----------------------------------------------------------------+--------+------------------+--------+---------------------+---------------------+ | threadID | topic | userID | username | postid | time | latest | +----------+----------------------------------------------------------------+--------+------------------+--------+---------------------+---------------------+ | 131549 | welche Spinne? --> eventuell Lepthyphantes sp. | 5455 | Manfred Zapf | 507256 | 2025-01-26 10:08:19 | 2025-01-26 10:08:19 | | 131698 | Baumwanze | 1397 | zobel | 507259 | 2025-01-26 12:48:43 | 2025-01-26 12:48:43 | | 56659 | Grüne Futterwanze? | 15196 | Christine | 507261 | 2025-01-26 14:57:09 | 2025-01-26 14:57:09 | | 131576 | Kleine schwarze Spinne --> Enoplognatha cf. thoracica | 15395 | Bernd 07 | 507263 | 2025-01-26 16:01:45 | 2025-01-26 16:01:45 | | 131307 | Amaurobius fenestralis? --> bestätigt | 15395 | Bernd 07 | 507264 | 2025-01-26 16:08:09 | 2025-01-26 16:08:09 | | 131701 | Unbekannte Schneckenart | 15395 | Bernd 07 | 507267 | 2025-01-26 16:43:50 | 2025-01-26 16:43:50 | | 131702 | Encyrtidae? | 11406 | JohnEs81 | 507268 | 2025-01-26 17:00:37 | 2025-01-26 17:00:37 | | 131700 | Welche Wanze ist das? --> Arocatus longiceps | 15395 | Bernd 07 | 507272 | 2025-01-26 17:36:02 | 2025-01-26 17:36:02 | | 131699 | Tegenaria --> nein sondern Amaurobius similis/fenestralis | 1999 | Klaus Fritz | 507274 | 2025-01-26 17:48:07 | 2025-01-26 17:48:07 | | 131683 | Grüne Larve -> Geometridae Art | 11406 | JohnEs81 | 507280 | 2025-01-26 20:41:49 | 2025-01-26 20:41:49 | | 131703 | eine Acericerus heydenii? | 1 | Jürgen Peters | 507282 | 2025-01-26 20:45:32 | 2025-01-26 20:45:32 | | 131687 | Zygina nivea? | 15392 | Sascha_N | 507286 | 2025-01-26 21:15:21 | 2025-01-26 21:15:21 | | 131686 | Welcher Schnellkäfer? --> Melanotus sp. | 15395 | Bernd 07 | 507287 | 2025-01-26 21:29:11 | 2025-01-26 21:29:11 | | 131693 | Lispocephala brachialis --> bestätigt | 15800 | Bernd Cogel | 507293 | 2025-01-26 22:06:16 | 2025-01-26 22:06:16 | | 131704 | Cantharis paradoxa? --> Cantharis sp., ein schwarzer, immerhin | 15335 | Simeon Indzhov | 507295 | 2025-01-26 22:29:15 | 2025-01-26 22:29:15 | | 131695 | Peyerimhoffina gracilis? | 15335 | Simeon Indzhov | 507296 | 2025-01-26 22:36:08 | 2025-01-26 22:36:08 | | 131705 | Phytoecia coerulescens? | 1 | Jürgen Peters | 507297 | 2025-01-26 22:52:48 | 2025-01-26 22:52:48 | +----------+----------------------------------------------------------------+--------+------------------+--------+---------------------+---------------------+ Are you planning on rebuilding the database?
  20. Barand
    I've started the load of that dump into a test DB. Now I'll just bingewatch a couple of Netfix series while it runs.
  21. Barand
    I'm curious to see that table. There must be around 20 years' data on this site too but thankfully pages don't take that long to load.
  22. Barand
    Yes! If you have each post with its time_posted then you can alway s find the times of first and last posts without storing those dates. Basically, do not store derived data like those dates or totals. Also the thread table contains usierID and username - username belongs only in the user table, not repeated in every thread record for that user. The dats/times are stored as unix timestamps. Horrible and completely unreadable. Use the inbuilt date/time/datetime/timestamp type columns.
  23. Barand
    From what I have been given, I came up with this ... SELECT b.boardID , b.title , t.threadID , t.topic , t.userID , t.username , FROM_UNIXTIME(t.time) AS time , FROM_UNIXTIME(t.lastPostTime) AS lastPostTime FROM wbb1_1_board b JOIN wbb1_1_thread t USING (boardid) JOIN wbb1_1_board_last_post l USING (boardid, threadid) ORDER BY boardID, threadid, time; Hovever, it seems to me that there should be a "post" table containing the post details, including the time_posted. Then you could just extract the posts containing the latest timestamp for each thread.
  24. Barand
    Posting the structures of the three tables would help. Better still a dump of tose tables (if there isn't too much data)
  25. Barand
    You could experiment quite safely without any danger of causing a global catastrophe
×
×
  • 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.