Hello

I am trying to show the latest posts from my site - which uses multiple boards - on the index page. … over 100 different boards

It would be much appreciated if I could get some guidance to solve this. My MySQL knowledge just isn't up to the challenge yet!

When I'm right, you want to show the latest ten or twenty post from any (of your 100) boards. This is more than one task.

You don't know, wich of the boards stores the latest entries. Maybe some boards are inactive since days or weeks and others are flooded with new postings every hour. You don't want to read the latest X postings from every of your 100 boards to show ten or twenty of them.

Thatswhy it is a proper solution to create in a first step a database view of all entries of all boards. Or maybe the latest twenty entries of all boards. This will be a big typing task, but it must be done only once (if it's not planned to have more boards in the future).

In a second step you change the query to read from the view instead from the tables.

Step 1: Create a database view of the entries-tables as a union query. This is done easiest with a tool like phpMyAdmin. A union query reads data from different source tables with a fixed row name scheme. Create the view and save it on the MySQL-server (for example as mlf_latest_entries).

As an example I'll show a UNION-query with only two tables (A_entries, B_entries). To identify the source board of the postings I inserted a new row (source) into the view but not into the source tables. I inserted a short text, you can specify an identifier or the URL of the board or even better the full URL of the entry instead.

SELECT
  id,
  pid,
  tid,
  name,
  user_name,
  A_entries.user_id,
  UNIX_TIMESTAMP(TIME) AS TIME,
  UNIX_TIMESTAMP(TIME + INTERVAL 60 MINUTE) AS TIMESTAMP,
  UNIX_TIMESTAMP(last_reply) AS last_reply,
  subject,
  'Board 1' AS SOURCE
FROM A_entries
  LEFT JOIN mlf2_userdata
    ON mlf2_userdata.user_id = A_entries.user_id
  WHERE spam = 0
  ORDER BY TIME DESC LIMIT 20
UNION SELECT
  id,
  pid,
  tid,
  name,
  user_name,
  B_entries.user_id,
  UNIX_TIMESTAMP(TIME) AS TIME,
  UNIX_TIMESTAMP(TIME + INTERVAL 60 MINUTE) AS TIMESTAMP,
  UNIX_TIMESTAMP(last_reply) AS last_reply,
  subject,
  'Board 2' AS SOURCE
FROM B_entries
  LEFT JOIN mlf2_userdata
    ON mlf2_userdata.user_id = B_entries.user_id
  WHERE spam = 0
  ORDER BY TIME DESC LIMIT 20

The result is a view with the latest 20 entries of all included forum-entry-tables. In case of 100 tables there are 2000 entries in the view.

It may be, that the JOIN slows down the execution of the query. In that case it may be better to read only from the entries-tables in the view and join the userdata when reading from the view only for the twenty entries of the latest postings.

Step 2: Change the query to show the latest twenty postings to read from the view mlf_latest_entries. Take the new row and the preformatted entries in the timebased rows into account. These rows contain the unix-timestamps so you can use the entries directly. If you decided against joining in the first step (view-query) you have to join the user data now, if not, you can read the data as plain text.

I've been trying wildcard statements with regard to *_entries but no joy yet.

That's not possible with table names.

Tschö, Auge