[plugin] Bounce Statistics

3rd party code for phpList

Re: [plugin] Bounce Statistics

Postby salvadoralicea » 5:59pm, Sat 10 May, 2014

ok, CommonPlugin was updated and now we see more info in the error report...


Code: Select all
#0 /home2/salvador/public_html/lists/plugins/CommonPlugin/DB.php(72): CommonPlugin_DB->_query('SELECT u.email,...')
#1 /home2/salvador/public_html/lists/plugins/BounceStatisticsPlugin/DAO/Bounce.php(120): CommonPlugin_DB->queryAll('SELECT u.email,...')
#2 /home2/salvador/public_html/lists/plugins/BounceStatisticsPlugin/Model.php(82): BounceStatisticsPlugin_DAO_Bounce->bouncedUsers(Array, 0, 50)
#3 /home2/salvador/public_html/lists/plugins/BounceStatisticsPlugin/Controller/Users.php(30): BounceStatisticsPlugin_Model->bouncedUsers(0, 50)
#4 /home2/salvador/public_html/lists/plugins/CommonPlugin/Listing.php(49): BounceStatisticsPlugin_Controller_Users->populate(Object(CommonPlugin_WebblerListing), 0, 50)
#5 /home2/salvador/public_html/lists/plugins/BounceStatisticsPlugin/Controller.php(40): CommonPlugin_Listing->display()
#6 /home2/salvador/public_html/lists/plugins/CommonPlugin/Controller.php(87): BounceStatisticsPlugin_Controller->actionDefault()
#7 /home2/salvador/public_html/lists/plugins/CommonPlugin/Main.php(39): CommonPlugin_Controller->run(NULL)
#8 /home2/salvador/public_html/lists/plugins/BounceStatisticsPlugin/users.php(30): CommonPlugin_Main::run(Object(BounceStatisticsPlugin_ControllerFactory))
#9 /home2/salvador/public_html/lists/admin/index.php(508): include('/home2/salvador...')
#10 {main}

Problem with sql query
SELECT u.email, u.id, u.bouncecount, u.confirmed, u.blacklisted, ub.email as ub_email , ua1.value as attr1, ua2.value as attr2, ua3.value as attr3, ua4.value as attr4, ua5.value as attr5, ua6.value as attr6, ua7.value as attr7, ua8.value as attr8, la10.name as attr10, la11.name as attr11, ua9.value as attr9
FROM phplist_user_user AS u
LEFT JOIN phplist_user_blacklist ub ON u.email = ub.email

LEFT JOIN phplist_user_user_attribute ua1 ON ua1.userid = u.id AND ua1.attributeid = 1
LEFT JOIN phplist_user_user_attribute ua2 ON ua2.userid = u.id AND ua2.attributeid = 2
LEFT JOIN phplist_user_user_attribute ua3 ON ua3.userid = u.id AND ua3.attributeid = 3
LEFT JOIN phplist_user_user_attribute ua4 ON ua4.userid = u.id AND ua4.attributeid = 4
LEFT JOIN phplist_user_user_attribute ua5 ON ua5.userid = u.id AND ua5.attributeid = 5
LEFT JOIN phplist_user_user_attribute ua6 ON ua6.userid = u.id AND ua6.attributeid = 6
LEFT JOIN phplist_user_user_attribute ua7 ON ua7.userid = u.id AND ua7.attributeid = 7
LEFT JOIN phplist_user_user_attribute ua8 ON ua8.userid = u.id AND ua8.attributeid = 8
LEFT JOIN phplist_user_user_attribute ua10 ON ua10.userid = u.id AND ua10.attributeid = 10
LEFT JOIN phplist_listattr_usastates la10 ON la10.id = ua10.value
LEFT JOIN phplist_user_user_attribute ua11 ON ua11.userid = u.id AND ua11.attributeid = 11
LEFT JOIN phplist_listattr_countries la11 ON la11.id = ua11.value
LEFT JOIN phplist_user_user_attribute ua9 ON ua9.userid = u.id AND ua9.attributeid = 9
WHERE bouncecount > 0
ORDER BY bouncecount DESC, email
LIMIT 0, 50


I hope this will help you to find the answer...
salvadoralicea
phpLister
 
Posts: 6
Joined: 4:49am, Sat 10 May, 2014

Re: [plugin] Bounce Statistics

Postby duncanc » 7:19pm, Sat 10 May, 2014

I cannot see any obvious problem with that, so please can you run a query in phpmyadmin against your database to see whether it runs successfully
Code: Select all
SELECT u.email, u.id, u.bouncecount, u.confirmed, u.blacklisted, ub.email as ub_email , ua1.value as attr1, ua2.value as attr2, ua3.value as attr3, ua4.value as attr4, ua5.value as attr5, ua6.value as attr6, ua7.value as attr7, ua8.value as attr8, la10.name as attr10, la11.name as attr11, ua9.value as attr9
FROM phplist_user_user AS u
LEFT JOIN phplist_user_blacklist ub ON u.email = ub.email

LEFT JOIN phplist_user_user_attribute ua1 ON ua1.userid = u.id AND ua1.attributeid = 1
LEFT JOIN phplist_user_user_attribute ua2 ON ua2.userid = u.id AND ua2.attributeid = 2
LEFT JOIN phplist_user_user_attribute ua3 ON ua3.userid = u.id AND ua3.attributeid = 3
LEFT JOIN phplist_user_user_attribute ua4 ON ua4.userid = u.id AND ua4.attributeid = 4
LEFT JOIN phplist_user_user_attribute ua5 ON ua5.userid = u.id AND ua5.attributeid = 5
LEFT JOIN phplist_user_user_attribute ua6 ON ua6.userid = u.id AND ua6.attributeid = 6
LEFT JOIN phplist_user_user_attribute ua7 ON ua7.userid = u.id AND ua7.attributeid = 7
LEFT JOIN phplist_user_user_attribute ua8 ON ua8.userid = u.id AND ua8.attributeid = 8
LEFT JOIN phplist_user_user_attribute ua10 ON ua10.userid = u.id AND ua10.attributeid = 10
LEFT JOIN phplist_listattr_usastates la10 ON la10.id = ua10.value
LEFT JOIN phplist_user_user_attribute ua11 ON ua11.userid = u.id AND ua11.attributeid = 11
LEFT JOIN phplist_listattr_countries la11 ON la11.id = ua11.value
LEFT JOIN phplist_user_user_attribute ua9 ON ua9.userid = u.id AND ua9.attributeid = 9
WHERE bouncecount > 0
ORDER BY bouncecount DESC, email
LIMIT 0, 50
duncanc
Moderator
 
Posts: 2440
Joined: 6:34am, Sat 08 May, 2010
Location: London

Re: [plugin] Bounce Statistics

Postby salvadoralicea » 9:30pm, Sat 10 May, 2014

After run the query (the code you replied) on my DB on phpmy admin, this was the error i get:
Code: Select all
 #1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='


Revising the tables on the collation column I have the following:
Tables:
phplist_admin_password_request
phplist_i18n
phplist_linktrack_forward
phplist_linktrack_ml
phplist_linktrack_uml_click
phplist_user_user

As utf8_general_ci

The other 42 tables are utf8_unicode_ci

I am not sure how this happened, but the DB has two different collation.
Can you tell me if this can be the root cause of the error?
What may be the solution to this.
salvadoralicea
phpLister
 
Posts: 6
Joined: 4:49am, Sat 10 May, 2014

Re: [plugin] Bounce Statistics

Postby duncanc » 6:48pm, Sun 11 May, 2014

Yes that is the cause of the plugin error.

Using phpmyadmin you need to change the collation of the email field on the phplist_user_user table from utf8_general_ci to utf8_unicode_ci. Go to the Structure tab, click Change for the email field, and then select utf8_unicode_ci.
You can also do the same for any fields in the other tables that also are utf8_general_ci.
duncanc
Moderator
 
Posts: 2440
Joined: 6:34am, Sat 08 May, 2010
Location: London

Re: [plugin] Bounce Statistics

Postby salvadoralicea » 12:47am, Mon 12 May, 2014

Duncanc, change of email field at the user_user Table from utf8_general_ci to utf8_unicode_ci was the solution.

I really appreciate your help... Thank you :D
salvadoralicea
phpLister
 
Posts: 6
Joined: 4:49am, Sat 10 May, 2014

Re: [plugin] Bounce Statistics

Postby jalbaiges2 » 5:24pm, Tue 23 Sep, 2014

Hi duncan!

This plugin seems quite interesting, thank you for the contribution.

However, I can't make it run. After installation (everything seems fine), when trying to execute "Bounce reasons" I got this message: "reason -> Sorry this page was not found in the plugin" and nothing is shown. The same when I try to execute "Bounce domains" or "Bounced users".

Any suggestion?

Thank you very much!

Jaume
jalbaiges2
phpList newbie
 
Posts: 2
Joined: 5:19pm, Tue 23 Sep, 2014

Re: [plugin] Bounce Statistics

Postby jalbaiges2 » 5:40pm, Tue 23 Sep, 2014

Hi again duncan!

Forget my last post. The problem was the bug related to uncomplete installations that you already reported. I manually uploaded the files and everything works like a charm. Great job!

Jaume
jalbaiges2
phpList newbie
 
Posts: 2
Joined: 5:19pm, Tue 23 Sep, 2014

Previous

Return to Contributions: Plug-ins, Add-ons, Mods

Who is online

Users browsing this forum: Google [Bot] and 4 guests