b.ID_BOARD = m.ID_BOARD question

[Damann]

In the file RemoveTopic.php in the function RemoveTopics() stands the following query:

SELECT m.ID_MEMBER, COUNT(m.ID_MSG) AS posts
         FROM {$db_prefix}messages AS m, {$db_prefix}topics AS t
         WHERE m.ID_TOPIC $condition
            AND b.ID_BOARD = m.ID_BOARD !
            AND m.icon != 'recycled'
            AND t.ID_TOPIC $condition
            GROUP BY MEMBER_ID

What I don't understand is the line with an exclamtion point.
What effect has this on the selection?
Maybe a stupid question but I might need to know what it does for developing mods.

[[Unknown]]

That's not the query I have.  I have:

SELECT m.ID_MEMBER, COUNT(m.ID_MSG) AS posts
FROM {$db_prefix}messages AS m, {$db_prefix}boards AS b
WHERE m.ID_TOPIC $condition
   AND b.ID_BOARD = m.ID_BOARD
   AND m.icon != 'recycled'
   AND b.countPosts = 0
GROUP BY m.ID_MEMBER

The query you have will not work.  In any case, the purpose of this query is to find out how many posts are being deleted for each member, so as to properly decrease their post counts.  I'll go through it line by line.

SELECT m.ID_MEMBER, COUNT(m.ID_MSG) AS posts

We want to know the ID_MEMBER and how many messages they posted.  We are going to decrease their post count by this number.

FROM {$db_prefix}messages AS m, {$db_prefix}boards AS b

We are looking at the posts, most specifically, but we also need to skip boards that have "count posts" disabled... so we need the boards table.

WHERE m.ID_TOPIC $condition

Only look at those topics we are deleting.

   AND b.ID_BOARD = m.ID_BOARD

And join on the board so we can get the correct row for countPosts.

   AND m.icon != 'recycled'

Don't count posts that were already taken from their post count....

   AND b.countPosts = 0

Make sure countPosts is 0 (do count posts, I know it's backwards.)

GROUP BY m.ID_MEMBER

And do it in groups by poster.

If you like inner joins, another example is:

SELECT m.ID_MEMBER, COUNT(m.ID_MSG) AS posts
FROM {$db_prefix}messages AS m
   INNER JOIN {$db_prefix}boards AS b ON (b.ID_BOARD = m.ID_BOARD)
WHERE m.ID_TOPIC $condition
   AND m.icon != 'recycled'
   AND b.countPosts = 0
GROUP BY m.ID_MEMBER

Does that make any more sense?

-[Unknown]

[Damann]

well a little
I see I made a mistake in my query. I indeed was looking for that one you have.
But still I don't know the effect of b.ID_BOARD = m.ID_BOARD
that's the line that's in my way

[[Unknown]]

It tells MySQL to only look at rows in the boards table that have the same ID_BOARD as the post does.

Think of it this way.  Let's say I have a table that looks like this: (table1)

a  b
1  3
2  6
3  9
4  12

And another that looks like this: (table2)

b  c
3  hi
6  bye
9  stuff

I'm going to give some example queries now, and what they will return:

SELECT table1.a, table1.b, table2.b, table2.c
FROM table1, table2;

a  b  b  c
1  3  3  hi
1  3  6  bye
1  3  9  stuff
2  6  3  hi
2  6  6  bye
2  6  9  stuff
3  9  3  hi
3  9  6  bye
3  9  9  stuff
4  12 3  hi
4  12 6  bye
4  12 9  stuff

That is generally not very useful.

SELECT table1.a, table1.b, table2.b, table2.c
FROM table1, table2
WHERE table1.b = table2.b;

a  b  b  c
1  3  3  hi
2  6  6  bye
3  9  9  stuff

That's very useful .

SELECT table1.a, table1.b, table2.b, table2.c
FROM table1
   LEFT JOIN table2 ON (table1.b = table2.b);

a  b  b  c
1  3  3  hi
2  6  6  bye
3  9  9  stuff
4  12 -  -

Sometimes useful, indeed, but less efficient.

What you're asking about is an example of the second one.  We want to know the "c" from table2, and "b" is ID_BOARD.

-[Unknown]

[Damann]

so actually in that query it only selects topics that have an existing board_id

[[Unknown]]

Luckily, we can safely assume that will always be true.  And, because it's more efficient to make this assumption, we make it.

The point isn't to skip posts without matching boards, but rather to get information about the board we couldn't get.  It would work fine to use a LEFT JOIN, but it would also be slightly slower... so why do it if there's no reason?

-[Unknown]

[Damann]

but my problem is that I don't want to use the GROUP BY ID_MEMBER. That makes my script functionate incorrectly. Is there any other way to get the same result but without having to use GORPU BY ID_MEMBER?