DB Read action not working

ro

royvdberg 27 May, 2019

Hi all,

I am trying to use the same form I use to save data to a database table to read and show 1 existing record. This form will be used for speakers at a conference to create a speaker profile. I want to send them an email containing a hyperlink to this form to update their data.

I put a DB Read action in the Load event, put the PHP-code in to be able to use 'id' in the URL and map this 'id' from the URL to the form hidden field 'aid'.

The form works fining for adding new data and the debuggers shows no errors when opening the form from an URL without the '&id=..' part.

However, when using the URL with '&id=1' (for example) at the end, the debugger keeps giving back error 1054 and shows the name of the Model ('sprekers').

I have tried all other Topics, manuals from previous Chronoforms versions but got stuck after several hours.

Anyone any idea how to get this to work?

he

healyhatman 28 May, 2019

You don't need PHP to get a value from the URL, just use {data:url_parameter_name}
Show us the URL you're using and a screenshot of your read data action.

ro

royvdberg 29 May, 2019

Hi there,

I removed the PHP part in the condition box and set the condition to be 'aid = {data:token} where token is the final part of the URL (....&token=1). The form contains a hidden field with the aid field. Unfortunately, the form is still not loading previously saved data.

Uploading a screenshot from this post leads to an error (error in saving attachment) for jpg, png and pdf files.

he

healyhatman 29 May, 2019

if you're using the text / manual conditions then it's aid:{data:token}

he

healyhatman 29 May, 2019

Also what have you got for the data source of the field because that needs to be right as well

ro

royvdberg 01 Jun, 2019

Hi there,

It seemed my database table got damaged: the aid column wasn't auto_increment so 3 records had an aid value of 0. I created a new table from the form and got the DB Read action to work according to the debugger:

Array
(
    [read_data16] => Array
        (
            [log] => Array
                (
                    [0] => SELECT `sprekers`.`aid` AS `sprekers.aid`, `sprekers`.`user_id` AS `sprekers.user_id`, `sprekers`.`created` AS `sprekers.created`, `sprekers`.`modified` AS `sprekers.modified`, `sprekers`.`voornaam` AS `sprekers.voornaam`, `sprekers`.`tussenvoegsel` AS `sprekers.tussenvoegsel`, `sprekers`.`achternaam` AS `sprekers.achternaam`, `sprekers`.`emailadres` AS `sprekers.emailadres`, `sprekers`.`telefoonnummer` AS `sprekers.telefoonnummer`, `sprekers`.`organisatie` AS `sprekers.organisatie`, `sprekers`.`functie` AS `sprekers.functie`, `sprekers`.`bignummer` AS `sprekers.bignummer`, `sprekers`.`linkedin_profiel` AS `sprekers.linkedin_profiel`, `sprekers`.`foto` AS `sprekers.foto`, `sprekers`.`iban` AS `sprekers.iban`, `sprekers`.`tenaamstelling` AS `sprekers.tenaamstelling`, `sprekers`.`plaats` AS `sprekers.plaats`, `sprekers`.`voorlopige_titel_presentatie` AS `sprekers.voorlopige_titel_presentatie`, `sprekers`.`leerdoelen` AS `sprekers.leerdoelen`, `sprekers`.`literatuurverwijzingen` AS `sprekers.literatuurverwijzingen` FROM `topics_chronoforms_data_aanmelden_spreker_topics` AS `sprekers` WHERE `sprekers`.`aid` = '1' LIMIT 100;
                )

            [var] => Array
                (
                    [sprekers] => Array
                        (
                            [aid] => 1
                            [user_id] => 393
                            [created] => 2019-06-01 15:36:03
                            [modified] => 
                            [voornaam] => Peter
                            [tussenvoegsel] => 
                            [achternaam] => Spronk
                            [emailadres] => pspronk@testmail.nl
                            [telefoonnummer] => 0131234567
                            [organisatie] => Gelre ZH
                            [functie] => Intensivist
                            [bignummer] => 
                            [linkedin_profiel] => 
                            [foto] => 
                            [iban] => NL56RABO0164525347
                            [tenaamstelling] => Peter
                            [plaats] => Apeldoorn
                            [voorlopige_titel_presentatie] => Titel Peter
                            [leerdoelen] => 
                            [literatuurverwijzingen] => 
                        )

                )

        )

)

However, the form still shows empty textboxes.

Should I do anything else to have the form fields show the data that is in the array shown in the Debugger output above?

he

healyhatman 01 Jun, 2019

{var:read_data16.sprekers.fieldname} as the value for each field. Replace fieldname obviously

ro

royvdberg 02 Jun, 2019

Hi there,

That was very helpfull...I got my form to work in the way I want it to.

Only one remaining question: as soon as a new form is saved, an email is sent to the email address that is documented in the form. This works great. However, inside the body of the email message I want to show a dynamic hyperlink so that the applicant can change his details.

The message is now setup like this:

Beste spreker, 
Wij hebben uw aanmelding / wijziging gegevens in goede orde ontvangen. 
U heeft de volgende gegevens aangeleverd: 
{AUTO_FIELDS} 
Indien u wijzigingen wilt aanbrengen in uw gegevens kunt u hiervoor de volgende hyperlink gebruiken: 
https://www.topicsinic.nl/index.php/event-pass/spreker-aanmelden&id={data:aid} 
Uiteraard kunt u ook contact met ons opnemen door het beantworden van dit emailbericht. 

Met vriendelijke groet,
het Topics in IC team

In bold I have emphasized the URL which should be sent to the applicant. Unfortunately, the {data:aid} part is not working.

Is this the consequence of aid being a hidden field in the form? And if so, do you have a proper solution to create the hyperlink without showing the aid field in the form? The latter could lead to an applicant changing the aid, thereby messing up our database.

Thanks again for your help!

he

healyhatman 02 Jun, 2019

You could add a verification field to the table, with the value being. {UUID:} and have that in your link instead

ro

royvdberg 03 Jun, 2019

Hi healyhatman,

Thanx for all your help so far. I don't really understand what you mean by a verify field, but looking at the debug info again I saw the following:

So on saving the record to the database, the field 'aid' is empty, which is logical because the aid is set by the database itself. So in the 'data' array the aid field is empty. However, after saving the record a new array is created, which seems to have the name 'var'. In this data array the aid field actually shows the id the database gave to the new record.

It is THIS aid value that I want to use in the email-action in my form. When I use {data:aid} or {var:aid} or {var:sprekers.aid} in my email-action nothing is shown in the email sent to the user.

Any idea how to get this aid item, which obviously exists in the array, into my email message?

he

healyhatman 03 Jun, 2019

{var:save_data9.aid}

Forums

DB Read action not working