One to n relationship

I need to create a single form that would insert records in multiple tables in a 1 to n relationship. Basically, I need to fill in multiple fields of a form (a through n) and then allow another pass through the same fields for a new item, and then submit them to multiple tables.

For example, the customer would specify a tee shirt and fill in size, style, color, and text, then they could specify that want 5 of them. Then they could add another tee shirt with its size, style, color and text and 4 of those. Then a third time and 4 of those, resulting in 13 individual records in an item table that are all related back to the customer's order ID.

1 - Is that something doable with your component?
2 - What solution would be recommended?
3 - Can you provide some samples and documentation?

Regards,
SOHO Prospecting Team

Hi sohopros,

The answer is that yes, it is entirely possible, but it's not 'built in' and would require some knowledge of PHP and MySQL to implement. Exactly what code is requires will depend on what the requirement of your store are and how the tables are set up.

Here's an example of the kind of code required. I used this to update (rather than create) some similar records for a client.

. . .
if ( !count($results) ) {
	// no updates required
	return false;
}
foreach ($results as $k => $v ) {
	$results[$k] = '( '.implode(', ', $v).')';
}
$results = implode(', ', $results);
$query = "
    INSERT INTO `#__some_table` (`id`, `user_id`, `field_id`, `value`)
    	VALUES {$results}
    	ON DUPLICATE KEY UPDATE `value` = VALUES(`value`) ;
";
$db->setQuery($query);
$db->query();
?>

Bob

Hi Bob!

Thank you for your answer. I still have one doubt.
How do I proceed if I want the frontend to look something similar to the attached image file. Where I have a grid with the current records for my child table and fields to add new record.


Appreciated,
SOHO Prospecting Team

Hi sohopros,

I'm not quite sure what you want to do here. Does this FAQ help?

Bob

Yes, this faq page is going to help us!

Thank you, so much!

Appreciated,
SOHO Prospecting Team