PHP scripting to POST to external MySQL for new registrations

Server-Side Scripting
1

Hi All

I have DFV2 connected to an external MySQL as a service so I can do a lot with API calls. But I want to add the user account both internally (so as to get the security features of DF) and externally so I can add more fields such as profile and chat logs. The solution I believe is a post POST script on user registration that adds the user to the external MySQL. I would like to do it in PHP.

According to https://github.com/dreamfactorysoftware/dsp-core/wiki/API-Access-via-Scripts the format is this

platform.api.get( “service[/resource[/resource_id]]”[, payload] );

But how can I turn that into a PHP script that adds the username , user.id and other data to my external db? All the examples are in javascript. What does the payload look like for adding a record? What is the resource_id? Any help most gratefully received

Charlie

2

Hi @charliefinale,

if I understand what you’re saying, you will need a Custom Script that receives the POST of your PHP code. The routine is:
PHP -> Script DF -> Save to DF Users -> Save to MySql Users

So, what you could do is:

  1. Make your PHP code call the DF user/register with the user data to the DF db and the data to MySql db.
  2. In scripts you have the post and pre process, there(IMO pre_process, because if it fails you stop the requisition right away) you need to make a V8js script to call your mysql db service to save in your db: platform.api.get('you_service/method/some_options_if_needed', user_data);

Example of requisition:

var new_user = platform.api.post('my-db-service/users?fields=id,name,email,login', user_data);

where user_data:

var user_data = {
  "login": params.login,
  "email": params.email,
  "name": params.name,
  encrypted_password: {
    expression: 'MD5("' + params.password + '")'
  },
  created_at: {
    expression: 'NOW()'
  },
  updated_at: {
    expression: 'NOW()'
  }
};

expression is for execute SQL commands in the query to that field.

Where is the post process:

script_post.png1428×500 24.7 KB

3

Also I must to say that in the requisition:

platform.api.post('my-db-service/users?fields=id,name,email,login', user_data);

the “fields”, in get params, is the fields that will return after the requisition is done.

4

Sorry for the slowness in thanking you, this is extremely helpful and I will be working on this today.

5

OK This is what I have tried

var user_data = {
“Login”: params.login,
“MobHeroID” , params.id,
“Email”: params.email,
“LastName”: params.name,
Password: {
expression: ‘MD5("’ + params.password + ‘")’
},
RegDate: {
expression: ‘NOW()’
},
UpdateDate: {
expression: ‘NOW()’
}
};

var new_user = platform.api.post(‘MobileHeroDB/Users?fields=MobHeroID,LastName,Email,Login’, user_data);

I have made sure that my database has the following fields in the Users table

Login
MobHeroID
Email
LastName
RegDate
UpdateDate
Password

Now using API Docs I registered a new user. To my surprise I got a 200 OK Success but no record was added to the external DB. It is set up as pre_process, so I was surprised at that, as I thought if the MySQL failed at the pre_process stage so would the user registration. Any help most welcome.

Also I am foggy on the MD5 part. Do I need to hold the password twice?..I do not see that as required.

Also if anyone has an example that is done in PHP rather that v8js that would be very helpful too.

6

Hi @charliefinale,

umm, this “MobileHeroDB” service, have SCRIPT permission access Role?

What do you mean about “hold the password twice?” ?

7

To answer you first question, probably not because I do not know how to do this. Could you show me how?

To answer you second, it appears you are creating a password to be held in the remote database. Since the remote database can be accessed using the internal password, is it necessary? i cannot see it will ever get used.

8

Can anyone out there give me just one simple example of a script written in PHP that can post to an external MySQL database (registered with DF). Any help would be much appreciated. Kimpatro was kind enough to get me started in jsv8, but the code did not work so I am a bit stuck. There are no PHP examples in the documentation so I am sure that others will find the answer useful too. Thanking you in advance.

9

Here is all the PHP scripting info currently available: https://community.dreamfactory.com/t/best-way-to-familiarize-with-df/1858/2

10

Oh man, really sorry not replying, I was on vacation.
Hope you had resolved that issue.

11

Thanks for your consideration…I mainly solved the problem and will post back my solution for other users in a short while.But it was based on your help so thanks.

1 Like
12

charlifinale, could you please share your solution? I’m writing a similar script and any help would be appreciated

13

Sure…This may be overkill because it captures Stripe details too…but this might help

var params,
base_url,
request_url,
token,
stripe_customer,
stripe_customer_id,
user_data,
new_user,
session_token,
twilio;

params = event.request.payload;
session_token = event.response.session_token;

if (params.stripeToken) {
stripe_customer = platform.api.post(‘stripe/customers?source=’ + params.stripeToken);
stripe_customer_id = stripe_customer.content.id;
}

user_data = {
“MobHeroID”: platform.session.user.id,
“Email”: params.email,
“LastName”: params.last_name,
“FirstName”: params.first_name,
“StripeCustomerId”: stripe_customer_id,
“Telephone”: params.phone,
“City”: params.city,
“IsEarner” : params.isEarner,
RegDate: {
expression: ‘NOW()’
},
UpdateDate: {
expression: ‘NOW()’
}
};

// twilio = platform.api.get(‘twilio’);
new_user = platform.api.post(‘MobileHeroDB/_table/Users?fields=MobHeroID,LastName,Email,StripeCustomerId,Telephone,City,IsEarner’, user_data);

// event.response.twilio = twilio;
event.response.new_user = new_user;
event.response.stripe_customer = stripe_customer;

3 Likes