Latest posts - multiple boards

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

For example I have one board that uses 'A_entries' to store its posts, and another which uses 'B-entries' to store its posts. This all works fine (for over 100 different boards).

I would like to show the latest posts taken from any board. However I have hit a brick wall when it comes to adjusting the relevant code. N.B - All users for all boards are in 'mlf2_userdata'

I am adjusting this code (e.g - for A_entries only) in index.inc.php:

$latest_postings_result = @mysqli_query($connid, "SELECT id, pid, tid, name, user_name, A_entries.user_id, UNIX_TIMESTAMP(time) AS time, UNIX_TIMESTAMP(time + INTERVAL ".intval($time_difference)." MINUTE) AS timestamp, UNIX_TIMESTAMP(last_reply) AS last_reply, subject
                                            FROM (A_entries) LEFT JOIN mlf2_userdata ON mlf2_userdata.user_id=A_entries.user_id
                                            WHERE spam=0 ORDER BY time DESC LIMIT 3") or raise_error('database_error',mysql_error());

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! I've been trying wildcard statements with regard to *_entries but no joy yet.

Thanks in advance

Andy

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

--
Trenne niemals Müll, denn er hat nur eine Silbe!

Auge

Thanks so much for this detailed reply. I am almost there, and yes the join was slowing down the initial query, so I am endeavouring to implement it later. Almost there, and I will post when complete...

Cheers

Andy

Hi Auge

Ok, here is what I did in the end. You were right about the initial method slowing down because of the join, therefore I first created a view from these queries:

(SELECT id,pid,tid, user_id, UNIX_TIMESTAMP(TIME) AS TIME,UNIX_TIMESTAMP(TIME + INTERVAL 60 MINUTE) AS TIMESTAMP, subject, '#000066' AS color, 'board1' AS SOURCE FROM board1_entries WHERE pid = 0 ORDER BY TIME DESC LIMIT 1) UNION 
(SELECT id,pid,tid, user_id, UNIX_TIMESTAMP(TIME) AS TIME,UNIX_TIMESTAMP(TIME + INTERVAL 60 MINUTE) AS TIMESTAMP, subject, '#990000' AS color, 'board2' AS SOURCE FROM board2_entries WHERE pid = 0 ORDER BY TIME DESC LIMIT 1) UNION...

...and so on, for all the other boards. I decided to only to show completely new threads/posts, not replies.

And then I queried this view in index.inc.php with:

SELECT *, user_name FROM mlf_latest_entries LEFT JOIN mlf2_userdata ON mlf2_userdata.user_id = mlf_latest_entries.user_id ORDER BY time DESC LIMIT 10

Seems to work very well, with no time lag.

Thanks again for your help.

my little forum

Latest posts - multiple boards (General)