Howto store about 100 row values in a setting page and access values like MySql

[cwsoft]

Good evening to all,

watched a youtube video about Processwire a few days back from Bernhard Baumrock and thought I deep dive into this CMS/CMF and try to realize a new project with it. My project basically deals with the subscription to some events (10-20). Basic idea is that potential guests get an E-Mail with the Url and a random unique personalized GUID (40 chars) used to assign to free slots via a public Form without having access to the backend at all.

Started with the Intermediate PW install and managed to setup an overview page showing a table with events pulled from it‘s child pages, a detailed page showing all infos for the actual event. Also managed to setup a custom form with three actions (subscribe, unsubscribe and check status of booking). 

For data storing I would like to create a settings page with text fields for the unique bookingID, a text field for linking a eventID to the bookingID and a datetime field to store the date of the submission. The bookingIDs will be available from start, where eventID and datetime are NULL first and get’s updated depending on the form action taken. I will have to store a table with about 100 entries (Sql rows) and 3 fields (Sql columns). Can this be realized with a repeater and the three custom fields? Should this page be a admin page, or a hidden frontend page? Are there any other options to store this values and access it via the PW API?

Any hints would be highly appreciated. 
 

Cheers zx80

[cwsoft]

Hi there,

as I did not yet understand the repeater field options, I just created a booking container page holding the booking details in three fields on various child pages (one child page per MySql row). This approach seems to work. Are there any more advanced techniques I can use to accomplish the same task?

Cheers

[BillH]

Welcome to the forums!

I have three suggestions.

First, when using PW, don't think about SQL unless you really, really have to – and it'll probably never be necessary!

Second, when deciding how to structure data, it's usually best to start by thinking of each major item as a page, and move on to complex field types only if it would improve things (usually because it will help with human editing of data). So, for example, you might handle each booking as a page, and then use a Page Reference field to link to the relevant event.

Third, if you haven't already, look in depth at selectors (very important and a great feature of PW!) for retrieving the data you need.

Let us know if further help would be useful!

[cwsoft]

Hi Bill,

thanks for your suggestions, pretty much went your proposed way already. I wrote a PHP/PW console script, deleting and adding a page for each possible end user based on a given data template with BookingId, EventId and Last access time.

Those fields gets updated based on the chosen form action. Rest was realized with default PW stuff like fields, templates and pages. The selector API you mentioned really makes it easy to find pages/fields, get/set/update field data on the fly and to sanitize user input. Quite impressing how far I got in two days from scratch with my very first PW project and some medium PHP level skills. Finished a working prototype this morning.

Now need to add some security features to the form like IP reload/retry ban and Captcha. Will check out the API documentation and see how to implement those stuff. 

[bernhard]

On 5/28/2023 at 8:56 PM, zx80 said:

watched a youtube video about Processwire a few days back from Bernhard Baumrock and thought I deep dive into this CMS/CMF and try to realize a new project with it.

Hey @zx80 welcome to the forum! Glad to hear that! Would be interested in how you found my video (which one btw)?

On 5/28/2023 at 8:56 PM, zx80 said:

For data storing I would like to create a settings page with text fields for the unique bookingID, a text field for linking a eventID to the bookingID and a datetime field to store the date of the submission. The bookingIDs will be available from start, where eventID and datetime are NULL first and get’s updated depending on the form action taken. I will have to store a table with about 100 entries (Sql rows) and 3 fields (Sql columns). Can this be realized with a repeater and the three custom fields? Should this page be a admin page, or a hidden frontend page? Are there any other options to store this values and access it via the PW API?

I'm not sure if I understand, but it sounds like you have already found a better approach? When being new to PW there are often situations where you think far too complicated, because you have learned that things work like that or have been done like that. And in PW there is often a much easier and quicker way. Not always of course ? 

20 hours ago, zx80 said:

I wrote a PHP/PW console script, deleting and adding a page for each possible end user based on a given data template with BookingId, EventId and Last access time.

As I didn't really understand what exactly you are trying to do I'm also not sure why you need a console script here?

20 hours ago, zx80 said:

Rest was realized with default PW stuff like fields, templates and pages. The selector API you mentioned really makes it easy to find pages/fields, get/set/update field data on the fly and to sanitize user input. Quite impressing how far I got in two days from scratch with my very first PW project and some medium PHP level skills. Finished a working prototype this morning.

Great! If you still need help or have questions it might help to give more detailed examples. If you are already happy with what you have congrats for your first steps in PW ? 

[cwsoft]

@bernhardThanks for your reply. Video was this one 

Process looks like this. About 100 people get an E-Mail with Url to the PW booking page and a personalized random BookingID (40 random chars) which is not guessable. This part is out of my control and lets say historical grown. The people can check for about 10 events in 2024 and subscribe to one event using their personalized bookingID. Due to data protection concerns, I am not allowed to store any personal data like E-Mail, Name etc. online. Not in PW, nor in textfiles on the server. Thats the constraints I have to fulfill. 

Hence I wrote a script, which creates 100 booking pages (one per person) which holds the unique bookingID and stores the subscribed eventID and last access time when the form is submitted. Every week a CSV file is created by PW which gets imported into a master Excel spreadsheet (historical grown) to check who subscribed for what event. 

Thats why I can‘t hide the booking form behind a PW login form per user and hence want to make the form more robust against spam bots and script kids attacks. 

If I would be free, I would create a different solution, with 100 login pages in the PW backend and without all the hassle of freakling around with Excel sheets at all. But I was really impressed how far I got over the weekend with my first PW installation setup on Friday afternoon last week. 

But I agree. It‘s important to understand the concept of PW of everything being a page with fields assigned via templates. Thats different from many other CMS I used in the past, but once you got the idea, it starts to make sense quite fast. 

Cheers zx80

[flydev]

I will read my answer again tomorow on the morning, but just throwing an idea about the form. 
Instead of trying hard to fight bots and potential hackers, you could benefit from existing protections of the login process by using a custom login form, by storing the bookingID in a field of the template user, you could differentiate each user by generating a username, for example "user_`$bookingID`", the username is not important here, and the password (which could be generated from the bookingID and a salt), not important either.
However, bots or hackers will have trouble getting through. You can also add a captcha like Mosparo to respect data collection.

 

[cwsoft]

@flydevThanks for this suggestion. Already thought about this too. Guess this would indeed be the best approach. However I came up with another option, as the entire PW booking site should also not show up in Google at all. 

I simply hid the entire PW side behind a .htaccess AuthBasic login. I may update my PW PHP script creating the booking pages to add .htusers credentials as well or just use a global AuthBasic login for all of the 100-150 users and a unique BookingID per user. So far I am good to go for a limited internal test, just two days ahead of the deadline for the concept study ?. 

However I will play around with PW frontend login and the FrontendForm module until the project goes live, as I think that‘s indeed the way I should go, as PW already adds security measures for free.

Thanks for all your suggestions so far. Highly appreciated. 

[flydev]

Yes, that's what is good with this tool, you are not required to be stick with a fixed solution. You can implement what's you have in mind and how you want to implement it. Really glad to see where you already are on using ProcessWire, it seem you got the freedom philosophy it bring to you ??

The module FrontendForms from @Juergen look a good candidate to me as he maintain it and did a fantastic job on it.

Also, I saw @AndZyk linked LoginRegister Module in other thread which could fit good here, for free. I remember we discussed years ago on some customization tricks that could give you ideas. Below you can find a thread about it, but keep it for later as I see you are short on your deadline ?

The original blogpost for the Pro version, just in case: https://processwire.com/blog/posts/login-register-pro/

You will find also a lot of tips (generally using code from quite deep in the core) from @Robin S in the forum, there are so much of them that you will need google to find them ?

PS: I am going to add a comment on your other threads about the spam protection.

[cwsoft]

@flydevThanks for your suggestions and links to posts and code examples. Highly appreciated.

Quote

Yes, that's what is good with this tool, you are not required to be stick with a fixed solution. You can implement what's you have in mind and how you want to implement it. Really glad to see where you already are on using ProcessWire, it seem you got the freedom philosophy it bring to you

Got the idea and philosophy already partially by Bernhards YT video, which brought me to PW first place. Was about to start with pure PHP/MySQL, HTML/CSS/JS. Pretty sure I wouldn‘t be that far, as I did my last serious PHP/MySQL project about 5 years ago. So far I am pretty happy with PW and I know I just revealed the absolut basics yet. Looking forward what more to come in the future. 

[cwsoft]

Just to let you know. Todays demo was a full success. Online booking project will enter next testing stage. However it‘s not yet decided if the project will be realized with PHP/MySQL and Processwire, or Python/Django as the companies internal CMF/CMS of choice used for about 80% of all internal projects.