to loop or not to loop?

[jodunno]

I'm not an sql programmer, so i am unable to find a solution to a problem with my basic sql knowledge. The problem is a forum database that has three tables of interest to me. The first table is a basic select query but the two other tables require matching categoryID field from the first query.

It is complicated to explain. table one has categoryID and i need to get a threadID from a lastposts table based upon the categoryID from the first query. Then i need the actual thread from a threads table based upon the threadID from the second query.

For the first two tables, i could run a single select query then match the two arrays by categoryID but the only way that i know to get the columns from table 3 by categoryID is to loop over the array results from query one. But now i have an sql query in a loop.

is it possible to use union or join or some other sql construct to accomplish this task, or do i need to write 50 separate select queries for the table 3 data?

query one works fine:

select categoryID, categoryTitle, categoryDesc, categoryType from categories;

i need the threadID from the lastposts table which can be obtained by using the categoryID from the first query but i cannot use a where clause when i cannot provide the info from query one without looping over the resulting array and using each categoryID in a loop query. select threadID from lastposts where categoryID = [cannot change this value for each value from the first query]

then i need to use the threadID from the second query to get the actual thread information from a threads table.

select title, userID, timestamp, username from threads where threadID = (i cannot provide this info from the second query because it is variable);

i guess that i need a loop over the arrays if there is not other way to get the data with changing/variable column values.

i didn't write the database but i need to display the categories with the last post in each category. Any ideas?

[Barand]

Posting the structures of the three tables would help. Better still a dump of tose tables (if there isn't too much data)

[jodunno]

Hi Barry,

 

you already know that it is not my forum and not my database. I have no problems with the php revision code but this sql code is difficult for me. Here is the result of the show columns, actually a select * from each table. I changed boardID to categoryID for simplicity. I see them as categories.

board;
+-----------------------------+--------------------------------------------------+------+-----+-----------+----------------+
| Field                       | Type                                             | Null | Key | Default   | Extra          |
+-----------------------------+--------------------------------------------------+------+-----+-----------+----------------+
| boardID                     | int(10)                                          | NO   | PRI | NULL      | auto_increment |
| parentID                    | int(10)                                          | NO   |     | 0         |                |
| title                       | varchar(255)                                     | NO   |     |           |                |
| description                 | mediumtext                                       | YES  |     | NULL      |                |
| allowDescriptionHtml        | tinyint(1)                                       | NO   |     | 0         |                |
| boardType                   | tinyint(1)                                       | NO   |     | 0         |                |
| image                       | varchar(255)                                     | NO   |     |           |                |
| imageNew                    | varchar(255)                                     | NO   |     |           |                |
| imageShowAsBackground       | tinyint(1)                                       | NO   |     | 1         |                |
| imageBackgroundRepeat       | enum('no-repeat','repeat-y','repeat-x','repeat') | NO   |     | no-repeat |                |
| externalURL                 | varchar(255)                                     | NO   |     |           |                |
| time                        | int(10)                                          | NO   |     | 0         |                |
| prefixes                    | mediumtext                                       | YES  |     | NULL      |                |
| prefixRequired              | tinyint(1)                                       | NO   |     | 0         |                |
| prefixMode                  | tinyint(1)                                       | NO   |     | 0         |                |
| styleID                     | int(10)                                          | NO   |     | 0         |                |
| enforceStyle                | tinyint(1)                                       | NO   |     | 0         |                |
| daysPrune                   | smallint(5)                                      | NO   |     | 0         |                |
| sortField                   | varchar(20)                                      | NO   |     |           |                |
| sortOrder                   | varchar(4)                                       | NO   |     |           |                |
| postSortOrder               | varchar(4)                                       | NO   |     |           |                |
| isClosed                    | tinyint(1)                                       | NO   |     | 0         |                |
| countUserPosts              | tinyint(1)                                       | NO   |     | 1         |                |
| isInvisible                 | tinyint(1)                                       | NO   |     | 0         |                |
| showSubBoards               | tinyint(1)                                       | NO   |     | 1         |                |
| clicks                      | int(10)                                          | NO   |     | 0         |                |
| threads                     | int(10)                                          | NO   |     | 0         |                |
| posts                       | int(10)                                          | NO   |     | 0         |                |
| enableRating                | tinyint(1)                                       | NO   |     | -1        |                |
| threadsPerPage              | smallint(5)                                      | NO   |     | 0         |                |
| postsPerPage                | smallint(5)                                      | NO   |     | 0         |                |
| searchable                  | tinyint(1)                                       | NO   |     | 1         |                |
| searchableForSimilarThreads | tinyint(1)                                       | NO   |     | 1         |                |
| ignorable                   | tinyint(1)                                       | NO   |     | 1         |                |
| enableMarkingAsDone         | tinyint(1)                                       | NO   |     | 0         |                |
+-----------------------------+--------------------------------------------------+------+-----+-----------+----------------+
35 rows in set (0.027 sec)
	last_post;
+---------+------------+----------+
| boardID | languageID | threadID |
+---------+------------+----------+
|       1 |          0 |   131614 |
|       2 |          0 |   131699 |
|       4 |          0 |   131409 |
|       6 |          0 |   131448 |
|       7 |          0 |   131644 |
|       8 |          0 |   109430 |
|       9 |          0 |   131636 |
|      12 |          0 |   131447 |
|      14 |          0 |   131211 |
|      15 |          0 |   130124 |
|      16 |          0 |    47890 |
|      19 |          0 |   131610 |
|      20 |          0 |   130326 |
|      22 |          0 |   131693 |
|      23 |          0 |   131702 |
|      24 |          0 |   131705 |
|      25 |          0 |   131687 |
|      26 |          0 |   131683 |
|      27 |          0 |    71457 |
|      28 |          0 |   131566 |
|      29 |          0 |   131550 |
|      30 |          0 |   131462 |
|      31 |          0 |   131597 |
|      33 |          0 |   131670 |
|      34 |          0 |   131701 |
|      35 |          0 |   131048 |
|      36 |          0 |    55605 |
|      40 |          0 |   127001 |
|      47 |          0 |    59622 |
|      48 |          0 |    56781 |
|      49 |          0 |    59625 |
|      50 |          0 |    57619 |
|      51 |          0 |    59683 |
|      52 |          0 |   131695 |
|      54 |          0 |    10964 |
|      53 |          0 |   131441 |
+---------+------------+----------+
36 rows in set (0.020 sec)
thread;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| threadID         | int(10)      | NO   | PRI | NULL    | auto_increment |
| boardID          | int(10)      | NO   | MUL | 0       |                |
| languageID       | int(10)      | NO   | MUL | 0       |                |
| prefix           | varchar(255) | NO   |     |         |                |
| topic            | varchar(255) | NO   |     |         |                |
| firstPostID      | int(10)      | NO   | MUL | 0       |                |
| firstPostPreview | text         | YES  |     | NULL    |                |
| time             | int(10)      | NO   |     | 0       |                |
| userID           | int(10)      | NO   | MUL | 0       |                |
| username         | varchar(255) | NO   |     |         |                |
| lastPostTime     | int(10)      | NO   | MUL | 0       |                |
| lastPosterID     | int(10)      | NO   |     | 0       |                |
| lastPoster       | varchar(255) | NO   |     |         |                |
| replies          | mediumint(7) | NO   |     | 0       |                |
| views            | mediumint(7) | NO   |     | 0       |                |
| ratings          | smallint(5)  | NO   |     | 0       |                |
| rating           | mediumint(7) | NO   |     | 0       |                |
| attachments      | smallint(5)  | NO   |     | 0       |                |
| polls            | smallint(5)  | NO   |     | 0       |                |
| isAnnouncement   | tinyint(1)   | NO   |     | 0       |                |
| isSticky         | tinyint(1)   | NO   |     | 0       |                |
| isDisabled       | tinyint(1)   | NO   | MUL | 0       |                |
| everEnabled      | tinyint(1)   | NO   |     | 1       |                |
| isClosed         | tinyint(1)   | NO   |     | 0       |                |
| isDeleted        | tinyint(1)   | NO   | MUL | 0       |                |
| movedThreadID    | int(10)      | NO   | MUL | 0       |                |
| movedTime        | int(10)      | NO   | MUL | 0       |                |
| deleteTime       | int(10)      | NO   |     | 0       |                |
| deletedBy        | varchar(255) | NO   |     |         |                |
| deletedByID      | int(10)      | NO   |     | 0       |                |
| deleteReason     | text         | YES  |     | NULL    |                |
| isDone           | tinyint(1)   | NO   |     | 0       |                |
+------------------+--------------+------+-----+---------+----------------+
32 rows in set (0.039 sec)

the threadID of the lastposts is how i can pull the correct data from the threads table but how can i get that without the boardID from query one? it is complicated. an sql dump is too large, the forum has 20 years of data.

[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.

[jodunno]

You are the best sql programmer that i have seen to date. I have seen you imagine a db and a corresponding form and code both in twenty minutes or less. I would need twenty hours as my sql skills are entry level at best. I appreciate you more than you know. I will implement the code this evening.

yes, there is a posts table. I can send it to you later if you wish to look at it for arguments sake. I do not recall the structure since i was trying to rebuild the index page. I was doing fine on the php, html and css side until i hit this wall.

i'm not an sql programmer but surely this db could be designed better, yes? i do not think that storing last thread in its own table is correct but i would need your opinion about that since you are a master db architect. I just find this db to be a bit of a wild west scene.

Edited Saturday at 03:15 PM by jodunno

[Barand]

1 hour ago, jodunno said:

surely this db could be designed better, yes?

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.

[jodunno]

i have a text file full of notes from Barand. at some point in my programming quest, i will need to learn sql and database design and i only want to learn from pros. Thus, i maintain notes that from posts that you have made here at freaks forum. You really are a fantastic database designer and a master of sql. I admire your knowledge of this field. I mention your name to my wife during discussions about sql. She knows who you are from my time here at freaks. She remembers the database help that you provided for my private messaging system idea for my nature website.

From what i know about good db design from your notes, i find this db from the forum to be amateurish and i told my wife that if Barand had built this forum it would be clean, smart, correctly coded and much easier for me to understand. Although i need to strengthen my knowledge of complex sql commands, I am really in kindergarten with sql.

regarding the posts table, i made an error: i ran the dreaded select* from posts and it is still running 40 minutes later. so now i know where the 20 years of forum life is being stored. yikes! i do not want to interrupt mysql in mid operation, so i will let the query finish. What a stupid idea of mine ...

[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.

[gizmola]

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.

[jodunno]

Hi Gizmola,

The links that you have provided are excellent resources. Thank you! I am trying today to understand Barry's code from an sql point-of-view. I really must learn this aspect of programming. I have neglected sql for far too long. Now i have a need to know it and i am struggling. I am trying to learn it but you know how that goes, i will need time to grasp all of this material. I have alot to learn.

I always appreciate your insight and also Requinix and Kicken. I have learned alot from all of you. Yet i have failed to master sql. It is biting me in the behind today...

Best wishes,

John

[jodunno]

16 hours ago, Barand said:

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.

well i have finally made a dump and sent it to you. I am still trying to find a way to instruct my brain of how i could come up with the same query that you have posted. I really need to master sql now.

Not to take any more of your time but what is with the b. t. prefix. How is this working? what do i call it so that i can research it? adding b. and t. to the column seems like a reference that can be used in multiple tables. I have not seen this before...

 

[jodunno]

36 minutes ago, jodunno said:

Not to take any more of your time but what is with the b. t. prefix. How is this working? what do i call it so that i can research it? adding b. and t. to the column seems like a reference that can be used in multiple tables. I have not seen this before...

it is an alias, yes? i didn't know that we could use an alias. I only know basic sql: select what from table_name or from table_name where whatID = thisID etc.

i found a cheat sheet: https://learnsql.com/blog/sql-join-cheat-sheet/joins-cheat-sheet-letter.pdf 

very fascinating!

[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.

[jodunno]

I have been reading so much sql that i am getting dizzy. I need a break. I'm going to switch to the html 5 and css 3 code a bit, then i will watch a film with my wife to relax. I'm trying to convert the forum to php 8, html 5 and css 3 with better session handling and security. I hit a wall with the sql code. I just need to rebuild the posts with attachments and finish the index page, then the forum will be stabilized. I cannot believe the sloppiness of this commercial code and database implementation. Anyway, my friend is too old to battle code and i am helping him for free since i use the forum as well. Some experts are in the forum and i'd hate to see it disappear. I'm trying to rescue it from a hacking death. I can tell that sql injections are working on this forum and i believe that bots may be the reason that the site crashes with a session error.

I just watched Venom Last Dance and Saturday Night movies. Very good films if you are looking for something to watch. Unless you are not a fan of Marvel or Saturday Night Live.

[gizmola]

7 hours ago, jodunno said:

it is an alias, yes? i didn't know that we could use an alias. I only know basic sql: select what from table_name or from table_name where whatID = thisID etc.

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.

 

 

[jodunno]

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.

[gizmola]

2 hours ago, jodunno said:

 

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.

I'm sure he does know that, and we are both motivated by an interest in mentoring and passing along our expertise to people like yourself if we are able.  We appreciate the feedback.

[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?

[jodunno]

12 hours ago, gizmola said:

I'm sure he does know that, and we are both motivated by an interest in mentoring and passing along our expertise to people like yourself if we are able.  We appreciate the feedback.

Thank you. You should know that i have a text file of data that you have posted. I always appreciate the wisdom from the top talent here at phpfreaks. I have learned alot from this forum and its top members. I give credit where credit is due.

I actually learned how this JOIN is working and i have successfully implemented my own query on the db last night before bed. I'll post the code in a reply to Barry. My point is that i learn from Barry and not simply take code and be on my way. I'm not looking for a free ride, only help when something is above my current level of knowledge.

I hope that you have a splendid day, Gizmola.

[jodunno]

10 hours ago, Barand said:

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.

are you planning on rebuilding the database?

I'm sorry that it didn't work out for you after three hours. I should've included the table structure in the file. My apologies for not properly planning.

Barry, you never cease to amaze me. Your code is fantastic and you only had the structure to work with. I hope that i can get my level of sql programming to such a position as to build from table structure alone and it works. I still have trouble with advanced sql since i have neglected this language. But i am learning from you.

I decided to take this JOIN concept with aliases and put it to practice. I had displayed the board index forums using basic sql but i noticed that a board structure table exists. I thought that i should be loading the forums according to the board structure, so i implemented your sql concept to arrive with the following query:

	select s.parentID, s.boardID, s.position,
b.title, b.description, b.boardType
FROM wbb1_1_board_structure s
JOIN wbb1_1_board b
USING (boardID);
	

it works 🙂 i have the forums displayed in the order specified in the board structure table. More importantly, i learned from you -- pending your approval.

i would like to rebuild the database because from day one i have had a difficult time looking at it. I am not a db architect and my level of sql is currently basic or entry level. However, from what i knw about db design from you and a few others at this forum, the db is not correctly designed. I also do not understand why username and userid has to exist in every table. I thought that one is supposed to relate the tables using a common field, such as id. I do not see this being implemented. But my first goal is to upgrade the php, html, css and sql to php 8, html 5 , css3 and pdo. Then i can look at the db but then i suppose that doing it this way will mean that i have to rewrite the sql parts of my current code. So i have to think about that.

Once again, my dear friend, Thank You. You have helped me more than you know. I am going to keep reading sql tuorials in my free time. I need an upgrade too 🙂

Please get some rest, Barry and i hope that your Netflix binging was entertaining and satisfying.

[Barand]

1 hour ago, jodunno said:

I thought that i should be loading the forums according to the board structure, so i implemented your sql concept to arrive with the following query

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";
        }
    }
}

[jodunno]

yes i see that the recursive code is working. Thank you for correcting me. At least i learned how to use aliases with JOINs. I also Thank you for educating me on this subject.

hmm. the query that you designed is working but the last line of sql is returning an empty set for me. I'm using xampp with mysql mariaDB. if i replace the last line with LIMIT 20 to test that the query is working, then i get results. Something is not working with the date. Do you know why?so

WHERE FROM_UNIXTIME(p.time) > CURDATE() - INTERVAL 7 DAY

Edited yesterday at 11:04 AM by jodunno

[Barand]

Could be there is no data in the table for the last 7 days. Try "- INTERVAL 1 MONTH"

[jodunno]

yes, of course. I seem to have forgotten that i am using a dump. I need my coffee. LOL but it works with INTERVAL 2 WEEK.

meantime, i'm reading about temporary tables, what a fascinating and useful method of retrieving data without effecting permanent storage. I'm enjoying this concept.

[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.