SQL compatibility with MySQL >= 5.7 & my extension

Discussion forum for Extension Writers regarding Extension Development.
User avatar
Steve
Registered User
Posts: 1480
Joined: Tue Apr 07, 2009 7:48 pm
Name: Steven Clark
Contact:

Re: SQL compatibility with MySQL >= 5.7 & my extension

Post by Steve »

Its working in the same way as 3Di's code just collecting the post count in a different query.
@ The Chief Medical Officers guideline for men is that: You are safest not to drink regularly more than 14 units per week.
- I drank that today++ :lol: 🍺
User avatar
canonknipser
Registered User
Posts: 2096
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: SQL compatibility with MySQL >= 5.7 & my extension

Post by canonknipser »

Marco has (reduced to the significant parts ;) )
SELECT ... COUNT(p.post_id) AS total_posts and ORDER BY total_posts DESC, so he's grabbing the poster with the most posts in his first (and only) fetch.
you are grabbing the poster of last post in the month and count (afterwards) his posts in that period
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB
User avatar
Steve
Registered User
Posts: 1480
Joined: Tue Apr 07, 2009 7:48 pm
Name: Steven Clark
Contact:

Re: SQL compatibility with MySQL >= 5.7 & my extension

Post by Steve »

Ah yeah, maybes was up too early :oops:
@ The Chief Medical Officers guideline for men is that: You are safest not to drink regularly more than 14 units per week.
- I drank that today++ :lol: 🍺
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: SQL compatibility with MySQL >= 5.7 & my extension

Post by 3Di »

Yeah, thanks you all for the interest, much appreciated.
I did more testing today and everything goes like it has always been and the blocker's gate is down, finally I can go on with the development of the extension. The following SQL now fixes as well another issue I filed there months ago (since then I was very busy with thousands of lines of code elsewhere).

Adopted the post visibility as per Steve and yes, a better check for ANONYMOUS :)

Code: Select all

$sql = 'SELECT u.username, u.user_id, u.user_colour, MAX(u.user_type), p.poster_id, MAX(p.post_time), COUNT(p.post_id) AS total_posts
	FROM ' . USERS_TABLE . ' u, ' . POSTS_TABLE . ' p
	WHERE u.user_id <> ' . ANONYMOUS . '
		AND u.user_id = p.poster_id
		AND (u.user_type <> ' . USER_FOUNDER . ')
		AND ' . $this->db->sql_in_set('u.user_id', $admin_mod_array, true, true) . '
		AND ' . $this->db->sql_in_set('u.user_id', $ban_ids, true, true) . '
		AND p.post_visibility = ' . ITEM_APPROVED . '
		AND p.post_time BETWEEN ' . $month_start . ' AND ' . $month_end . '
	GROUP BY u.user_id
	ORDER BY total_posts DESC';
$result = $this->db->sql_query_limit($sql, 1);
$row = $this->db->sql_fetchrow($result);
$this->db->sql_freeresult($result);
Issues which are bound to be closed thanks of this reload:
- https://github.com/3D-I/tpotm/issues/14 (MySQL >= 5.7 compatibility)
- https://github.com/3D-I/tpotm/issues/17 (Check for post visibility)
Now I am ready to take care of the suggestions from the Validation Team and go on, thx.

I add here something for the posterity, some shortcut via phpMyAdmin to make your life easier for testing;

Check your DB SQL mode
- in console digit: SELECT @@sql_mode and [CTRL] + [ENTER]

Default usually is
- STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

ADD ONLY_FULL_GROUP_BY to it and your are now ready for tests.

Always in console digit:
- SET GLOBAL sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"
and [CTRL] + [ENTER]
Last edited by 3Di on Sun Sep 10, 2017 10:52 pm, edited 1 time in total.
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
User avatar
Steve
Registered User
Posts: 1480
Joined: Tue Apr 07, 2009 7:48 pm
Name: Steven Clark
Contact:

Re: SQL compatibility with MySQL >= 5.7 & my extension

Post by Steve »

I got it working yesterday afternoon also. Post the code later on today when back on the computer. Made some changes I'd like to see and use 😁
@ The Chief Medical Officers guideline for men is that: You are safest not to drink regularly more than 14 units per week.
- I drank that today++ :lol: 🍺
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: SQL compatibility with MySQL >= 5.7 & my extension

Post by 3Di »

Sure thing Steve, thanks for your code. Appreciated.
I will see to make one thing together with all the rest already committed last night. :)
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
Post Reply

Return to “Extension Writers Discussion”