How can I link two DB Multi Record Loaders?

Hello!
I've tried the faq with the same title but it's not working for me.
I've got the first line with Title and Author but nothing in the table.
I think it's a problem with the join because I can have a table with the title of my articles and an other one with the users when I make simple DB multi record loaders without join.
I really do exactly what it's explain in the faq so I don't know what to do.
Is anyone can tell me what's wrong please?
Thanks
Florence

Hi Florence,

I'm afraid that there isn’t enough information here to start to work out what is wrong :-(

Bob

More informations:
[list]I work on Joomla!2.5.8[/list]
[list]ChronoForms 4.0[/list]
[list]I work on a local server so you can't have an access to my forms 😟 [/list]

First DB multi record loader:
Basic
Table : jooc_users (as jooc is the name of my db)
Model ID : User
Fields : name
Advanced
Load data : no
Enable association : no
Join type : It's not in the faq so the default choice is INNER, I don't change it
Join rule : `Articles`.`created_by` = `User`.`id`
Associated models : Articles

Second DB multi record loader:
Basic
Table : jooc_content (as jooc is the name of my db)
Model ID : Articles
Fields : title
Advanced
Load data : yes
Data load type : all (default)
Enable association : yes
Join type : It's not in the faq so the default choice is INNER, I don't change it
Join rule : nothing
Associated models : User
Data displayer
Enable data displayer : yes
Displayed fields : title:Title,User.name:Author

When I test the form, I got the first line of the table with ''Title'' and ''Author'' but nothing else.

I think I've done it right exactly as you discribe in the faq so I don't know what's wrong.

I think it's the join that is not correct because the data displayer works well (show the title of the article) if I set Enable Associations to No on the second DB multi record loader (Articles).

I wish this would help you understanding my problem.

Please if anybody can help me!
I'm new in Chronoforms, in php and sql query.
I first tried the faq about the dynamic dropdown and double dropdown and it works well! Thanks
That's why I'm really lost, the faq about the linked db multi record loaders isn't working properly with my form.
If someone has a clue or something even if it's really thin...

Hi FloB,

I checked your entries against my test form and they all look correct.

One question I guess is 'Does your site have any articles?' Sometimes test sites don't have any.

Please drag a Debugger action into the On Submit event, then submit the form and post the debug results here.

Bob

Hello Bob!
Thanks for your replie!
Yes I've got only three articles on my test site, but they exist :wink:
I'll try the debugger action this evening (can't work as much time as I want on this project 😢 )
Thanks again for taking time to solve this problem.
Florence

Hello again!
Here the result of the debugger action:
[attachment=0]debugger.PNG[/attachment]

I must precise that I don't have anything in my form but the data displayer, so I put the debugger action in the OnLoad event, after the two db multi record loader.
So, as I can't see what wrong with this (I don't speak very well this language), maybe you can!
Waiting for your post...

PS : I have rectified `articles`.`ceated_by` in `articles`.`created_by` but I've got the same result.

Hi FloB,

I tried the MySQL query in PHPMyAdmin and it give the list of articles and author names correctly

SELECT `title`, `user`.`name` AS `user.name` FROM `#__content` AS `articles` INNER JOIN `#__users` AS `user` ON `articles`.`created_by` = `user`.`id`

Do your article have values set in 'created_by'?

Bob

Hello Bob!
Yes they have...

I'll try the request directly with mysql this evening and let you know.

Thanks for your time
Florence

Hello!
I tried the request

SELECT `title`, `user`.`username` AS `user.name` FROM `#_content` AS `articles` INNER JOIN `#_users` AS `user` ON `articles`.`created_by`=`user`.`id`

in the MySQL query in PhpMyAdmin and it works perfectly.
So the problem is in the form right?
What can be wrong?
I'm really lost 😟
Have you ever seen that?

Florence

Edit :
I just find that the type of #_content.created_by is int(10) and #_users.id is int(11).
Does it have an influence?
And if yes, why does this difference exist (I'll need to check all my db and rectify...)?

Hi Flob,

Please take a Form Backup using the icon in the Forms Manager and post it here (as a zipped file) or PM or email it to me and I'll take a closer look.

I just find that the type of #_content.created_by is int(10) and #_users.id is int(11).

This will only be a problem if you have more than 10,000,000,000 users.

Bob

Hello!
Here the backform...
[attachment=0]CFV4_FormsBackup_ON_127.0.0.1_14_Feb_2013_10_52_22.zip[/attachment]
Hope you'll find a solution...
Thanks
Florence

Hi FloB,

Found it, you need to set 'Group Model Data' to No on the second DB Multi-Record Loader Advanced tab.

Bob

Thanks a lot!
It works...
But I want to put a drop down in my form with dynamic data enable : data path = articles, value key = id and text key = title).
So I go to the db multi record loader about articles, set id,title in the fields (basic)... and nothing happen because of a conflict between the id of #_content and id of #_users (dixit mysql query in phpmyadmin).
How can I parameter the db multi record loader to have two distinct `id`?
Is that possible?

It's a bit complicated when you want to do something more sophisticated than a simple contact form 😑

But thanks to all the admin who help a lot!

Florence

HI Florence,

I'm not quite clear what you are doing here? Is this as well as the listing?

You can use the Model ID to separate similar data sets.

Bob

Hi Bob
I just want a drop down connected with the #_content table value key=id and text key=title.
To do that I need to load the id of the #_content table, so in the db multi record loader I need to put id in the Basic tab, at Fields.
See the back up
[attachment=0]CFV4_FormsBackup_ON_127.0.0.1_14_Feb_2013_15_25_54.zip[/attachment]
But back to the begin, nothing is returned.
I tried the query with mysql and it says that the field `id` is ambiguous (suppose because there is an `id` in the #_content table and an other in the #_users table).

So how do you parameter the db multi record loader to make it work?

Florence

Hi Florence,

ChronoForms uses Model IDs to allow you to load two tables that have some of the same column names. Then you can use, for example: users.id and articles.id

Bob

Hello!
Thanks to you
Seems to me that I had try this...
I'll check this afternoon.
Florence

Hi all,

I have a question. How can I put a variable to the where statement in DB Multi record loader?
I mean something like

`User`.`id`=$test

It is give me an error

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}' at line 1 SQL=SELECT `title`, `User`.`name` AS `User.name` FROM `yjw8h_content` AS `Articles` INNER JOIN `yjw8h_users` AS `User` ON `Articles`.`created_by` = `User`.`id` WHERE `User`.`id`=$test 

thx a lot

Hi homeopat,

Have you defined $test somewhere? It should probably be something like

`User`.`id` = <?php echo $test; ?>

or

`User`.`id` = <?php echo $form->data['test']; ?>

or

`User`.`id` = {test}

Bob

Hi Bob,
I have put custom code action before DB Multi Record Loader where I defined $test=555
variable $test is empty in first two cases, it shows

... WHERE `User`.`id` = 

in last case shows:

... WHERE `User`.`id` = {test} 

Hi homeopat,

There's a 'scope' problem here. If you define a variable like $test in one action then is is normally only valid in that action.

You have a few choices, I'd probably put the PHP to define $test in the WHERE box in this case.

You could also define $test as global to make it available between actions.

But the best ChronoForms solution is to add it to the $form->data array so that it is available across all actions:

<?php
$form->data['test'] = 555;
?>

`User`.`id` = {test}

Bob

Hi homeopat,

There's a 'scope' problem here. If you define a variable like $test in one action then is is normally only valid in that action.

You have a few choices, I'd probably put the PHP to define $test in the WHERE box in this case.

You could also define $test as global to make it available between actions.

But the best ChronoForms solution is to add it to the $form->data array so that it is available across all actions:

<?php
$form->data['test'] = 555;
?>

`User`.`id` = {test}

Bob

HiBob
thanks a lot.
I have put this code to the where statement and it works.

<?php
$form->data['test'] = 555;
?>
`User`.`id` = <?php echo $form->data['test']; ?>