SQL compatibility with MySQL >= 5.7 & my extension

Discussion forum for Extension Writers regarding Extension Development.
User avatar
Steve
Registered User
Posts: 992
Joined: Tue Apr 07, 2009 7:48 pm
Location: UK ,up north!!
Name: Steven Clark
Contact:

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

Post by Steve » Fri Sep 08, 2017 7:15 am

Its working in the same way as 3Di's code just collecting the post count in a different query.

User avatar
canonknipser
Registered User
Posts: 1182
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 » Fri Sep 08, 2017 8:16 am

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
New arrival - Extensions and scripts for phpBB
no support via PM or mail

User avatar
Steve
Registered User
Posts: 992
Joined: Tue Apr 07, 2009 7:48 pm
Location: UK ,up north!!
Name: Steven Clark
Contact:

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

Post by Steve » Fri Sep 08, 2017 8:23 am

Ah yeah, maybes was up too early :oops:

User avatar
3Di
Registered User
Posts: 11865
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milano - Frankfurt
Name: Marco
Contact:

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

Post by 3Di » Fri Sep 08, 2017 11:37 pm

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.
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades

User avatar
Steve
Registered User
Posts: 992
Joined: Tue Apr 07, 2009 7:48 pm
Location: UK ,up north!!
Name: Steven Clark
Contact:

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

Post by Steve » Sat Sep 09, 2017 8:41 am

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 😁

User avatar
3Di
Registered User
Posts: 11865
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milano - Frankfurt
Name: Marco
Contact:

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

Post by 3Di » Sat Sep 09, 2017 5:59 pm

Sure thing Steve, thanks for your code. Appreciated.
I will see to make one thing together with all the rest already committed last night. :)
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades

Post Reply

Return to “Extension Writers Discussion”

Who is online

Users browsing this forum: No registered users and 7 guests

cron