Hi
I am new to Joomla, and so I am not sure if I am doing this right...
I am trying to link 2 tables in a component. I have a #_person table with boolean offices and an #_office table with office addresses. I need to update the combined table #_officepeople that has the #_person.id and the #_office.id so that you can know who is in what office. How do I update this table from the #person.boolean data?

Have I even structured this data correctly? By now, I am questioning everything!

From what I have read, I need to use the controller #person to save the data to #officepeople, right? Not sure what I need to do there, exactly...query then update?post?

Again, new to Joomla, but not code, but certainly, no expert, just trying to learn!

Hi @chmchm and welcome!

How are they related m:n, 1:n or 1:1? What is the boolean for, to switch on/off adding an office?

I'd highly recommend looking at the tutorial: www.j-cook.pro/docs/tutorials - includes customised code, step by step plus the finished component.

Hope it helps,

Gez

Gez,
Thanks for the reply. Yes, I did look at the tutorial, the only difference is that I need to be able to update persons and offices on the same form.
Yes, the Boolean is to switch on and off office.
So I have a table that has offices and addresses and a table that has people who work in the offices, some work at more than one office. They are joined in the office-people table using the id for people and the id for offices so you can have many instances of both. How do I update/ populate the office people table with the Boolean fields that represent the offices the people table?
I need to have a single entry form for the adding people. This is where it differs from the tutorial, I think. I got it working fine the other way, but the data is all in the people file, and I don't want to add it manually.
Would I do it in the office- people table model/officepeople.php using a query? Or would I do it in the controller file? Or do I need to rethink my strategy all together?
If you need to see my files, let me know, I am not always the best communicator!
Thanks again for the reply!
Christina

Hi Christina,

OK, I suppose there are 2 clear options to do this; AJAX or calling the form for the join table into the person form;

1. AJAX - In your 'people' table & 'add person' form layout, add the FK field 'office' if you've not already. You could delete this field from the database table after download as we're not going to store it in this table. Ok, since you already have person.id from the current view, make an ajax request to post the pair of values into the join table (the form that you've created for this table.
   
   If you're not familiar with ajax, there are plenty of great tutorials on the net + JQuery condenses the required code to make such a call into a line or 2. It's therefore pretty well recommended to include jQuery in your component (Config in the builder), especially since Joomla is finally moving away from Mootools!
   
   This may help you getting started with jQuery in your component: www.j-cook.pro/forum/35-jquery/4481-show...ds-in-front-end-form should you choose to add a dynamic 'add another office' button

Alternatively:

Calling the join form into the layout - Please see: www.j-cook.pro/forum/9-coding-inside-you...les-in-one-view#1882

Please note, the forms/code in this thread refer to JDom calls to render forms as it was prior to the move to JForms (xml definitions) although, it should get you started.

In the same way as the tutorial, you could include a grid below the person info listing any offices they are associated with.

Anyway, I hope this helps get you going!

Best of luck,

Gez

Will the AJAX function save new people and their office data into the joined table?

This is my view for people:

and for the relational table

And the office view:


So, I should add another office field to the people table? Just to add a relation to it?

I am wondering if I can make this work, and if so, do I write UPDATE or POST statement? Because, I do want to dynamically add or even change the offices that these people are associated with. And I still have the question, do I do it in the controller file or the model file? Or both? The model collects the data to send to the view, right? But doesn't the controller save the data? If I have saved it, the model can pull it the way I have it written, I think.... I just need that joined table to be populated...

I am a little intimidated by the MVC and find it a little confusing.

I would much rather use jQuery, because I am more familiar with it than jDom. Again, intimidating!

Hi Christina,
chmchm wrote: Not exactly. I would propose you save your 'person' in the usual way. Upon success of that save, send the ajax request with the newly created person.id & the office.id (s).

TODO:

1. Create a table for your join table
   1. Add 2 Foreign Key type fields
   2. Drag your people table (tables list) to the first and name accordingly
   3. Drag your offices table to the second and name accordingly
2. Create a form layout for your join table
   1. From your fields list, expand your person table node to reveal the fields in that table and drag one of them to the form. Choose a unique field like username as this will serve as a label for the record (person) when picking it in a combobox and you wouldn't want duplicates causing confusion.
   2. Repeat previous step for your office field
3. On your person form layout (and possibly the item view if you wish), call the person_offices using either option 1 or 2 of my previous post.

---

Rest assured that you are not the first and certainly not the last to be a little intimidated by the MVC structure!

I once heard a really great analogy to describe MVC. Think of it as an entertainment system;

1. Model - handles retrieving your data much like a CD, DVD, VHS or Set-top box
2. View - displays your data much like a TV or projector
3. Controller - much like a remote control, your controller is where you 'request' or check what's been requested and call the relevant model/view.

Of course, this analogy only works to a point but should get you going.

Have you seen this post: www.j-cook.pro/forum/9-coding-inside-you...comment-section#5555 ? It's the same kind of principle as what you're trying to achieve using a standard (not ajax) approach.

Hope it helps,

Gez

Sorry, one last thing:chmchm wrote: On the contrary, you don't need to add any fields relating to offices on the person table. All of this is then handled by including the form to the join table.

Also (and on a separate note), do you really want to recreate all of the user fields when most of that data already resides in the core users table?

If every person in your component is a joomla user on your site then you only need to add fields that don't already exist such as avatar + the joomla user type field. All of the other info such as email etc can be displayed in item/collection layouts for a person by dragging the related user fields.

Also, you may consider writing your person info as a profile plugin as this will give you extra leverage of user orientated events such as login, register, logout etc. They're pretty straightforward and have been written about plenty on the forum if you need any pointers. It would also be quite strightforward to adapt a joomla dropdown field to list your offices from the offices table - either the 'proper' way by subclassing the combo/checkbox type OR by using the SQL type and entering the query...

Anyway, if this is something that may help, just search the forum for 'Profile Plugin' and/or visit : docs.joomla.org/Creating_a_profile_plugin & www.compassdesigns.net/joomla-17-expanded-user-profiles/

Hope it helps!

Gez