Jump to content

Leaderboard

Popular Content

Showing content with the highest reputation on 02/02/2025 in all areas

  1. 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?
    1 point
  2. i recommend giving the fields a class name. you can then eliminate the array and just use getElementsByClassName to get a collection of the elements to loop over. see if this gets you closer to what you want - <html> <head> </head> <body> <script> function hasNumericValue() { const matches = document.getElementsByClassName("numeric"); for (let i = 0; i < matches.length; i++) { alert(Number.parseInt(matches[i].value)); } } </script> <form name="tester" method="post" action="" onsubmit="return hasNumericValue();" > <input name="it_c" type="text" class='numeric' value=""> <br> <input name="it_h" type="text" class='numeric' value=""> <br> <input name="ot_c" type="text" class='numeric' value=""> <br> <input name="ot_h" type="text" class='numeric' value=""> <br> <input type="submit" value="submit"> </form> </body> </html> you can then test if each value is or is not a number.
    1 point
  3. Hi Gizmola, very helpful post. I've read about this topic for quite some time today and it is very fascinating to me. I should've explored sql many years ago but atleast i'm trying to catch up now. I have learned alot from Barand but i never implemented it with my own code to gain experience. I am learning alot today and your notes are also very helpful. I see how it is working now and it is quite clever 🙂 Thank you for taking time to educate me on this subject. I appreciate your expertise. I appreciate Barand's expertise always, although he may not know it. I hope that you have a pleasant evening and i'll keep experimenting with this concept to gain more experience with it.
    1 point
  4. Yes this is common practice. When you first introduce a table you can alias it. You are then free to alias all the columns which can be quite a time saver when you have joined tables together, using "alias.column_name" as Barand did. You can do this explicitly using the 'AS' keyword but you can also omit the 'AS'. It's up to you, but I typically will abbreviate the name in some way, as do most experienced developers, such that your alias is at most a few letters. You should also notice, that he used an alias for the computed columns. Column names can also be aliased, and it's also a common practice. FROM_UNIXTIME(t.time) AS time So in this case you have an example of both alias options being used: thread table was aliased to t Used the t alias to specify the time thread.time field being passed to the FROM_UNIXTIME() SQL function the result of the function being aliased to the name 'time' in the final result set Also time was used in the order by. MySQL allows you to do this (use an aliased column name in an ORDER BY) although not all RDBMS do.
    1 point
  5. Barry is truly a master of relational database design, implementation and SQL. However , at least initially, a small investment on your part in learning how to join tables together, will dramatically improve your understanding of his analysis and the SELECT statement he provided you. I browsed this material and it's a solid free tutorial on Joins using MySQL. https://www.mysqltutorial.org/mysql-basics/mysql-join/ You may see references to ANSI standard SQL, which is a standard for portable SQL syntax that should be compatible with most relational databases, but I did want to mention that different databases will have features that are specific to their implementation (non-standard), so you might see that in the case of joins there is more than one syntax possible, but they all do the same things. Don't let that confuse you -- JOINS are an essential concept that all relational databases implement. If you have an option, and can use ANSI standard syntax, opt for that, but it really doesn't matter that much, so long as you are clear on what the join produces. A basic understanding of Set theory might give you some insight into the ideas that went into relational database management (union, intersection, difference, subset) might help as well. This article cover the topic pretty well, and you may notice some of the overlap in concept and terminology: https://kyleshevlin.com/set-theory/ It's also worth learning how to read an Entity-Relationship-Diagram (ERD). Once you understand the fundamentals, you should be able to look at an ERD and understand how tables can be joined together. There are also many tools that people use to design or reverse engineer databases. For example, mysql provides ERD design features in their free SQL Workbench tool. ERD's are the way that people socialize a database design, and are the documentation that teams use to document for developers, the database design.
    1 point
  6. You can add a usort function, which sorts the $files1 array based on the modification time of each file. Something like this: usort($files1, function($a, $b) { return filemtime($b) - filemtime($a); }); I would also recommend using htmlspecialchars for added security. Hope this helps.
    1 point
  7. 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.
    0 points
  8. lol. Glad to see that Powershell had nothing to do with your question, though.
    0 points
This leaderboard is set to New York/GMT-05:00
×
×
  • 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.