IPv6Freely

1 hour ago, Kirill Gromov said:

You can take the author of the previous or next post and call him lucky)

Haha yes we gave it to the next one. It didn't make sense to give it to the 999,999th post anyway, let alone an admin! The first legit post over a million got it :)

I love ChatGPT. And thank you guys for pointing me at the correct tables!

Heh, that user is banned. So I went back one. You have to be kidding me.

mysql> SELECT * FROM (   SELECT pid AS id, author_name AS author, post_date AS date   FROM ibf_forums_posts   WHERE `queued` = 0   UNION ALL   SELECT archive_id AS id, archive_author_name AS author, archive_content_date AS date   FROM ibf_forums_archive_posts   WHERE `archive_queued` = 0 ) combined ORDER BY id LIMIT 999998, 1;
+---------+------------+------------+
| id      | author     | date       |
+---------+------------+------------+
| 1125214 | IPv6Freely | 1670888060 |
+---------+------------+------------+
1 row in set (14.16 sec)

Okay, ChatGPT to the rescue!

Quote

I have two MySQL tables. One of them (table "ibf_forums_posts") has the fields "pid" (which is the primary key), "author_name", and "post_date". The other table ("ibf_forums_archive_posts") has the fields "archive_id" (the primary key), "archive_author_name", and "archive_content_date".

I want to combine the two tables together, sort by the first field (pid and archive_id), and find the 1000000th entry in the list. when selecting from the first table, use "WHERE `queued` = 0" and in the second table use "WHERE `archive_queued` = 0"

mysql> SELECT * FROM
    -> (
    ->   SELECT pid AS id, author_name AS author, post_date AS date
    ->   FROM ibf_forums_posts
    ->   WHERE `queued` = 0
    ->   UNION ALL
    ->   SELECT archive_id AS id, archive_author_name AS author, archive_content_date AS date
    ->   FROM ibf_forums_archive_posts
    ->   WHERE `archive_queued` = 0
    -> ) combined
    -> ORDER BY id
    -> LIMIT 999999, 1;
+---------+-------------+------------+
| id      | author      | date       |
+---------+-------------+------------+
| 1125215 | SuperdaveHR | 1670895838 |
+---------+-------------+------------+
1 row in set (14.98 sec)

Hmm that doesn't seem right either. Post with pid of 1000022 is from 2014. So that doesn't seem right either if the current post count is 1001050. I'm sure just a misunderstanding on my part of both how its calculated and how its stored. 

47 minutes ago, Square Wheels said:

Here are some that I partially wrote and others helped me with.

Two options, first shows all posts, last one removes hidden posts.

Set n (999999) to be one less that the record you are looking for

SELECT
    *
FROM
    forums_posts
ORDER BY
    pid
LIMIT 1 OFFSET 999999;

https://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table/16606

---

Does not show hidden posts.  This is what matches the Forum Statistics block.

SELECT (SELECT COUNT(1) FROM `forums_posts` WHERE `queued` = 0) + (SELECT COUNT(1) FROM    `forums_archive_posts` WHERE archive_queued = 0) AS `PostCount`

---

As long as the archive table is empty, this works.

SELECT
    *
FROM
    forums_posts
WHERE `queued` = 0
ORDER BY
    pid
LIMIT 1 OFFSET 999999;

Thank you!

The Forum Statistics block is giving me the right number:

mysql> SELECT (SELECT COUNT(1) FROM `ibf_forums_posts` WHERE `queued` = 0) + (SELECT COUNT(1) FROM    `ibf_forums_archive_posts` WHERE archive_queued = 0) AS `PostCount`;
+-----------+
| PostCount |
+-----------+
|   1001050 |
+-----------+
1 row in set (0.50 sec)

But the other queries show their latest post on the forums (from this evening) using OFFSET 170823 with a pid of 1126337:

mysql> SELECT pid FROM ibf_forums_posts ORDER BY pid LIMIT 1 OFFSET 170823;
+---------+
| pid     |
+---------+
| 1126337 |
+---------+
1 row in set (0.23 sec)

mysql> SELECT pid FROM ibf_forums_posts ORDER BY pid LIMIT 1 OFFSET 170824;
Empty set (0.05 sec)

It also loks like it jumps from 999998 to 1000022:

mysql> SELECT pid FROM ibf_forums_posts ORDER BY pid LIMIT 1 OFFSET 88016;
+--------+
| pid    |
+--------+
| 999998 |
+--------+
1 row in set (0.03 sec)

mysql> SELECT pid FROM ibf_forums_posts ORDER BY pid LIMIT 1 OFFSET 88017;
+---------+
| pid     |
+---------+
| 1000022 |
+---------+
1 row in set (0.03 sec)

Does that mean the post with pid 1000000 is in a different state (hidden)?

Hi everyone!

Is there a way for me to determine what was the millionth post on our forum? We just crossed that a month or so ago and would like to send a prize to the person who made that post. Unless it was me, I guess ;) 

Thanks!

23 hours ago, IPv6Freely said:

Another feature request: The ability to order medals. Eg. I always want winterfest first, summerjam second, and then the other smaller medals in whatever order they are achieved. 

I accomplished this with the following (probably really bad) code:

		<div class="ipsTrophy-postbit-container ipsType_center">
            {{foreach $items AS $trophy}}
          		{{if $trophy->item->name_seo == "winterfest"}}
					{$trophy->item->getItemImage('trophy_trophyicon ipsTrophy-postbit-bit', 2, TRUE)|raw}
          		{{endif}}
            {{endforeach}}
            {{foreach $items AS $trophy}}
          		{{if $trophy->item->name_seo == "summerjam"}}
					{$trophy->item->getItemImage('trophy_trophyicon ipsTrophy-postbit-bit', 2, TRUE)|raw}
          		{{endif}}
            {{endforeach}}
            {{foreach $items AS $trophy}}
          		{{if $trophy->item->name_seo != "winterfest" and $trophy->item->name_seo != "summerjam"}}
					{$trophy->item->getItemImage('trophy_trophyicon ipsTrophy-postbit-bit', 2, TRUE)|raw}
          		{{endif}}
            {{endforeach}}
		</div>

I know PHP moderately well but I don't know IPS templating very well so this seemed like the easiest way even if its inefficient. 

23 hours ago, IPv6Freely said:

Another feature request: The ability to order medals. Eg. I always want winterfest first, summerjam second, and then the other smaller medals in whatever order they are achieved. 

I'd even be happy being able to change them to show in a descending order instead. 

Edit: I accomplished this by doing:

{{$items = array_reverse($items);}}

Would still definitely be nice to have a sort option in admincp. 

Another feature request: The ability to order medals. Eg. I always want winterfest first, summerjam second, and then the other smaller medals in whatever order they are achieved. 

By the way, I killed the legend by adding this to CSS:

legend.ipsType_center a[href*="node_trophies_TrophiesAndMedals"] { display: none; }

On 7/22/2018 at 4:46 AM, Fosters said:

I like this and I'll add a new setting to the next version where you'll be able to define the position of the trophies:)

Could you also do size and position per medal? For example we have this:

Currently this is all controlled by adding people to various secondary groups (one group is SummerJam, one group is Winterfest, and then we have a group that is just one of the bottom icons, another group that is two of them, and another group that is three of them) so obviously our groups setup is a convoluted mess. 

Obviously the bottom three icons are easily done as medals, but the two wider ones aren't going to work. 

I'd also like to not have the "Trophies and Medals" heading under the user, either. 

Just ordered this (under the primary account for our site - I'm just a secondary admin) and can't wait to implement. Until now we'd just been using secondary groups with badges, adding people to secondary groups for various things just to give them the badge. Looking forward to cleaning that entire process up!

That said, I just downloaded the htaccess file from the link with the description To see the .htaccess used for Apache servers for comparison, download this file. It had this:

<IfModule mod_rewrite.c>
Options -MultiViews
RewriteEngine On
RewriteBase /forums/
RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule \.(js|css|jpeg|jpg|gif|png|ico|map)(\?|$) /forums/404error.php [L,NC]

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /forums/index.php [L]
</IfModule>

Realized for this to work my line in my config file has to have the /forums/ in the rewrite rule because that's where the forum is installed.

        location @ips {
                rewrite ^ /forums/index.php?$args last;
        }

Now, to move the forum into the root dir instead of having URLs like domain.com/forums/forum/87-msh-front-page ? 

1 minute ago, Nathan Explosion said:

Did you put the provided htaccess file in place as described?

Your config might be conflicting with something that is native to the software, so it would be a good idea to revert your own config and use the IPS functionality 'as is'

I didn't think nginx used htaccess files? 

1 hour ago, ASTRAPI said:

Go to the admin panel at System->Search Engine Optimization-> and enable rewrite Urls ?

I knew I had to be missing a step. So, I did that and got the "doesn't seem to be working" message. On the forum the links all had the correct URL (no more 'index.php?' in them), but clicking them just gave me 404s. So I must be missing something else as well. 

I'm having difficulty with my rewrites. I want to lose the 'index.php?' from my URLs.

This is my conf file:

server {
        listen 80 default_server;
        listen [::]:80 default_server ipv6only=on;

        root /mnt/mshvol-01/www;
        index index.php index.html;
        server_name modsquadhockey.com;
        client_max_body_size 10M;

        location / {
                try_files $uri $uri/ @ips;
        }

        location @ips {
                rewrite ^ /index.php?$args last;
        }

        error_page 404 /404.php;
        error_page 500 502 503 504 /50x.php;

        location ~ \.php$ {
                include snippets/fastcgi-php.conf;
                fastcgi_pass unix:/run/php/php7.0-fpm.sock;
        }

        location ~ /.well-known {
                allow all;
        }

}

Am I doing something wrong? Is there something in AdminCP or a config file I have to modify to make this happen?