Barand
-
Posts
24,585 -
Joined
-
Last visited
-
Days Won
826
About Barand
Recent Profile Visitors
103,899 profile views
Barand's Achievements
-
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 ...
-
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.
-
Could be there is no data in the table for the last 7 days. Try "- INTERVAL 1 MONTH"
-
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"; } } }
-
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?
-
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.
-
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.
-
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.
-
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.
-
Posting the structures of the three tables would help. Better still a dump of tose tables (if there isn't too much data)
-
You could experiment quite safely without any danger of causing a global catastrophe
-
The ability to use strings as array keys (associative keys) has always been a feature of PHP. The drawbacks are the same as when using string columns as keys in DB tables... they tend to be longer and therefore less efficient great care has to taken to ensure consistency of case, puctuation and spelling
-
The last 2 lines of your query are ... where you are joining options and filters tables. Your option_id column contains values between 1 and 12. select * from ap_element_options LIMIT 15; +--------+---------+------------+-----------+----------+---------------+-------------------+------------------+------+ | aeo_id | form_id | element_id | option_id | position | option | option_is_default | option_is_hidden | live | +--------+---------+------------+-----------+----------+---------------+-------------------+------------------+------+ | 431 | 10556 | 1 | 4 | 1 | MCSO | 0 | 0 | 1 | | 432 | 10556 | 1 | 5 | 2 | CCSO | 0 | 0 | 1 | | 433 | 10556 | 1 | 6 | 3 | LCSO | 0 | 0 | 1 | | 434 | 10556 | 1 | 7 | 4 | DPD | 0 | 0 | 1 | | 435 | 10556 | 1 | 8 | 5 | FPHM | 0 | 0 | 1 | | 436 | 10556 | 1 | 9 | 6 | FHPC | 0 | 0 | 1 | | 437 | 10556 | 1 | 10 | 7 | FHPL | 0 | 0 | 1 | | 438 | 10556 | 1 | 11 | 8 | AUTO CLUB | 0 | 0 | 1 | | 439 | 10556 | 1 | 12 | 9 | OWNER | 0 | 0 | 1 | | 446 | 10556 | 2 | 4 | 1 | F550 | 0 | 0 | 1 | | 447 | 10556 | 2 | 5 | 2 | F350 | 0 | 0 | 1 | | 448 | 10556 | 2 | 6 | 3 | International | 0 | 0 | 1 | | 449 | 10556 | 2 | 7 | 4 | Ranger | 0 | 0 | 1 | | 453 | 10556 | 4 | 1 | 1 | Credit Card | 0 | 0 | 1 | | 454 | 10556 | 4 | 2 | 2 | Cash | 0 | 0 | 1 | +--------+---------+------------+-----------+----------+---------------+-------------------+------------------+------+ Which of these values are you expecting to match the filter_keyword Tow ? select * from ap_form_filters; +--------+---------+---------+--------------------+--------------+------------------+----------------+ | aff_id | form_id | user_id | incomplete_entries | element_name | filter_condition | filter_keyword | +--------+---------+---------+--------------------+--------------+------------------+----------------+ | 312 | 10556 | 3 | 0 | element_6 | is | Tow | +--------+---------+---------+--------------------+--------------+------------------+----------------+ I suggest you rethink your design and come back when you have something that could work.
-
The syntax you want is foreach (get_holidays($year) as $holiday_name => $holiday) { . . . }
-
Earlier in this thread you said that this ... +----+-----------+-----------+ | id | slug | tag_check | +----+-----------+-----------+ | 1 | hugh-jass | 1 | | 2 | hugh-jass | 2 | +----+-----------+-----------+ ... was acceptable. Your "solution" would not permit this. Secondly, you are using a "dependent subquery" which means for every record inserted you must query all the existing lookig for a duplicate. This can be extremely slow and should be avoided when writing queries. Changing your unique key to ... UNIQUE INDEX `unq_name` (`name`) USING BTREE, ... and using this query would have same result ... INSERT IGNORE into wp_terms (name,slug) SELECT concat(nameFirst,' ',nameLast) , lower(concat(nameFirst,'-',nameLast)) FROM a_players ;
