Display linked content from 3 tables on one profile page

Another stupid issue...

I have 3 tables in my form
jml_chronoforms_registration with cf_user_id as unique also equal to the jml_users.id
rex_main with id_link as unique (same value as cf_user_id and id)
also with rex_level_link (tinytint 3)
rex_level with level_id as unique
also with level_name (char 35)

My goal is just to display the form page showing most of the fields from the rex_main table and the level_name from the rex_level table. I don't want to save or edit or update the data I just want to display it.

I have it setup as a profile page and I am using the jml_chronoforms_registration cf_user_id as the target and the cf_id as the request. I can put the jml_chronoforms_registration fields at the top of the Form Code HTML page like {cf_id} and it displays, so I know I am getting the link to the first table, but how do I make a link to the rex_main record that I want. I need to somehow set the rex_main.id_link = jml_chronoforms_registration.cf_user_id and set the rex_level.level_id = rex_main.rex_level_link so that fields from all 3 tables display on the page at once.

I have tried doing equalities with the {fields} but that dosen't seem to work.

And the truth is I really don't even need the jml_chronoforms_registration table if I could use the jml_users.id field for the link to rex_main.id_link. That is if I could figure it out!

But I can't get it.

Please help. I'm drowning.

Thanks, in advance
The_fitz

Hi the_fitz,

I'm not sure I understood the complications here! Here are some bullets that might help:

[list]

The Profile Page plug-in is designed to work with a single table

You can access any of the User information from jos_users (jml_users in your case) from the Joomla! User Object using $user =& JRequest::getUser(); then $user_id, $user->name, etc.

You can add PHP to the Form HTML of the Profile Plug-in form to look up any table you like manually using the 'usual' SELECT . . . query.

Generally you can't relaibly use the {input_name} syntax in PHP - the sequence of operations sometimes doesn't allow it so better not to do it at all.

[/list]

All that said, if you have a complex query like this you might be better off not using the Profile Plug-in but just adding a complex mYSQL query at the start of the Form HTML to look up all the data at once. 'Complex' here meaning that it uses JOIN to access data from more than one table in a single query.

Bob

No, Bob, You hit it right on the head, as usual. Your answer is perfect. I'm sure that this is going to "get it done" for me. Thanks again.
The_fitz