Custom profile field with value from excel

Looking for an Extension? Have an Extension request? Post your request here for help. (Note: This forum is community supported; while there is an Extensions Development Team, said team does not dedicate itself to handling requests in this forum)
Get Involved
Post Reply
Lord Phobos
Registered User
Posts: 771
Joined: Tue May 18, 2004 11:41 pm

Custom profile field with value from excel

Post by Lord Phobos »

Hi

I run a very long tournament of a great number of board games with many players in my association.
I assign each player points based on their plays and their victories.
All of this is managed by a very complex excel file.

What I'm asking for now (as I have more ambitious plans in my mind) is not to manage all this stuff via phpbb and sql tables, altough it would be very cool (contact me in pm if you want details, we can discuss the payment).

I created a custom field named "Alias" in on my board.

Would it be possibile to automatically populate another field (named "score") with a numeric value taken from an excel table (the final pivot of the score) where the "Alias" has the exact name of a cell in the table? Maybe creating a phpbb table in the sql, if you can tell me how (and how to update it weekly).

In that way, users with an alias corresponding to the player's name in the table would have the score displayed, others won't display anything.

Is that feasible?
I can do all of this manually, but as I said I have many players and I have to do it weekly.
Also nice would be the capability of sorting memberlist by this score...

Can you tell me something?

Thanks in advance!
Lord Phobos
Registered User
Posts: 771
Joined: Tue May 18, 2004 11:41 pm

Re: Custom profile field with value from excel

Post by Lord Phobos »

The best solution I can think of is to create a table in SQL named phpbb_players. and update it weekly via a csv file converted from excel.
This table would contain the player's name, the number of plays, the number of victories, the victory percentage and finally its score.
Then, I should create a page on my board parsing this table, showing all of this data in nice columns.

Finally, I should have an extension able to make a check between the "Alias" custom field in the user's profile and the player's name in the newly created sql phpbb_players table.
If the values match, then show the value of the score in another custom profile field.
If the values don't match, show "0" or "Unknown", or even better don't show the field at all.

Can someone help me out on all of this as I'm unable to code even this simple stuff?

Thanks a lot!
Lord Phobos
Registered User
Posts: 771
Joined: Tue May 18, 2004 11:41 pm

Re: Custom profile field with value from excel

Post by Lord Phobos »

No one can help me with this project?

I can pay (but fairly, I assume it is quite simple).
User avatar
Restless Rancor
Registered User
Posts: 196
Joined: Tue Sep 18, 2018 1:51 pm

Re: Custom profile field with value from excel

Post by Restless Rancor »

To achieve what you want I think you need to move away from the idea of using CPF's.

I used to use a script to pull data from Google Sheets and display it externally (it was nothing to do with phpBB and was not used on phpBB), but my initial thinking is a script shouldn't be too difficult to write to read the data - then it's a simple case of displaying it cleanly in a table on a new page.

Then, we can add the user ID to the global template variables (if we need to) and use it to lookup the value on the new page. (This would require the spreadsheet data to have the phpBB user ID to identify the member's scores).
From there it'd be a simple task of assigning it to <!-- EVENT viewtopic_body_postrow_custom_fields_before --> in viewtopic_body.html and <!-- EVENT memberlist_view_non_contact_custom_fields_before --> in memberlist_view.html for example.

I'm not too confident in my ability to do this, but am willing to give it a shot starting with manual code edits to the core files, and if I get anywhere then to an extension.

Just a quick note: any solicitation has to be done here. I'm happy to (attempt) this for you for no cost, but being realistic it's going to take me some time to complete (but I should know sooner if it's beyond my knowledge)- it's also assuming you're willing to migrate onto Google Sheets and possibly Google Scripts.

If you have some sample data I could use that would be helpful.

Thanks.
These are not the droids you're looking for...
Lord Phobos
Registered User
Posts: 771
Joined: Tue May 18, 2004 11:41 pm

Re: Custom profile field with value from excel

Post by Lord Phobos »

I cannot go in depth in explaining why, but I can't move away from the excel file I'm using.
So, I cannot migrate to google sheets.

In this file (totally external to phpbb) I obviously don't have username or userid included, so I thinked about the "alias" workaround.

In short, what I need is a CPF looking in a table named phpbb_players for each user if his "alias" CPF is the same as "alias" in that table.
If a corresponding alias is found, THEN populate another CPF ("Points", or something like that) with the points value.

This is all I need. I also need an advice on the way to populate that table (yes, I am SO ignorant :lol: ).

Last thing, if the memberlist could then be sorted by the "points" number this would be wonderful.

Thanks a lot again.
User avatar
Kailey
Community Team Leader
Community Team Leader
Posts: 3734
Joined: Mon Sep 01, 2014 1:00 am
Location: sudo rm -rf /
Name: Kailey Snay
Contact:

Re: Custom profile field with value from excel

Post by Kailey »

Lord Phobos wrote: Tue Nov 06, 2018 2:35 am I can pay (but fairly, I assume it is quite simple).
It's not allowed to offer payment in any form for assistance in this forum. If you feel what you want to do is beyond your capability, then please post in the Wanted! forum where you may find someone willing to take on your project.
Kailey Snay - Community Team Leader
Knowledge Base | Documentation | Community rules

If you have any questions about the rules/customs of this website, feel free to send me a PM.
Lord Phobos
Registered User
Posts: 771
Joined: Tue May 18, 2004 11:41 pm

Re: Custom profile field with value from excel

Post by Lord Phobos »

I can, not I must.
User avatar
david63
Registered User
Posts: 20646
Joined: Thu Dec 19, 2002 8:08 am

Re: Custom profile field with value from excel

Post by david63 »

If you are still wanting this as an extension there is a possibility that I might have found a way to do it.

I have just come across PhpSpreadsheet which, in principle, works with phpBB. I have a very basic extension that will load an Excel spreadsheet's data - but that is as far as I have got.

It will take some time to find my way round this class but more so I would need access to a copy of your spreadsheet to ensure that your data is compatible with this class.

And no I am not asking for any payment!
David
Remember: You only know what you know and - you don't know what you don't know!

I now no longer support any of my extensions but they will start to become available here
Post Reply

Return to “Extension Requests”