Setting up Relationship in SQL

💬 General Discussion
1

Hey guys and good day to you!

Another dumb question coming right up :cheer:

So I am dealing with this - I have a FORM on a remote server. I need to write fields from this form to my CRM.

Here is the thing - I couldnt use Beans (obviously - I am on remote) and API is dead slow for this. So I am writting it directly to MySQLi.

I dealt with most of it but there is one thing I am not sure about:

How can I set up a relationship? I have a new custom module and it has a submodule (something like Projects -> ProjectTasks). So I need the form to create a new Project and then inject a few Project Tasks but how to create a connection between them using PHP -> SQL?

In Beans its very easy but in PHP to DB directly I am not really sure :huh:

Is there anyone who could help me?

Thank you!

2

Although it’s possible is not recommended (because it might be a security risk by exposing the DB configuration to an external source)…

Now regarding the affected tables, not all relationship fields are stored the same. Some will be stored on a special table named with both modules (accounts_contacts for example), others will be saved in the secondary module. In the case of Projects -> ProjectTasks. I’m 95% positive :slight_smile: that the relationship is stored on module tasks. Every record on the tasks table include two fields that indicate the relationship. They are: parent_type and parent_id.

Hope it gives you some light on what you want to achieve.

Thanks,

AlxGr

3

Hi AlxGr thanks a lot :wink: Both servers are under my control so hopefully there is no security issue with this. Its Joomla vs SuiteCRM and I tried using SuiteCRM api for this but given the number of ajax calls you need to have it took around 20 to 30 seconds to finish and thats simply way too much :frowning: Thats why I went with DB connection…

Will type in here if I succeed :wink:

4

So - its all working except for one thing that I am fighting. And thats ID’s. I understand that ID’s in SuiteCRM must be unique and it doesnt have to have the same length as default suitecrm ID’s.

Thats fine - the ID’s of Project will be typed in the form by user (user recieves a barcode number - each one is different so each user will have a unique number). The only problem is Project Task ID. Thats one thing I am not really able to control and suitecrm is not using auto increment so here is the question - CAN the project task exist with empty ID?

Check the attachment… Thats what I am talking about. The yellow ID. Can it be empty? Or is there a way to tell suitecrm that when I am creating something in database it should add its own number to it?

5

Soooo… I actually solved this using:

uniqid(mt_rand(),false);

so it creates a uniqueID + adds a random prefix (guess I will add some restrictions to random so its not too long) and it works well!