CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

 Automated creation of "child" records? Advice needed!

Print topic Send  topic

Author Message
VBAjedi


Posts: 34
Posted: 01/28/2004, 4:23 PM

How can I automatically create "child" records? Hypothetical scenario (Using CCS, PHP, & MySQL):

My MySQL database has a "Houses" table, a "HousePlan" table, and a "Rooms" table.

Using a CCS Record Form or Editable grid, the user creates a new record in the "Houses" table, specifying which house plan number (from the "HousePlan" table) was used to build this house.

When that new house record is created, I want to automatically look at the corresponding "HousePlan" record, see the definition of how many of each type of room that plan calls for (ex. "2 bedrooms, 1 bath, 1 kitchen"), and create a record in the "Rooms" table for each (which would be 4 rooms in this example).

Is there a standard approach to this type of issue? Any suggestions on how I can go about doing this?
_________________
VBAjedi
jedi at NOSPAM dot obie dot com
View profile  Send private message
peterr


Posts: 5971
Posted: 01/28/2004, 5:20 PM

"Working with Multiple Selections" examples in CCS Example Pack may be helpful here:
http://docs.codecharge.com/studio/html/ProgrammingTechn...xamplePack.html

Almost all of them show how to programmatically insert or update records in related tables after a record was submitted. This is not exactly what're doing but should give you some good ideas.

"Execute Custom SQL" example may also be good:
http://docs.codecharge.com/studio/html/ProgrammingTechn...eCustomSQL.html

_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com
View profile  Send private message
DonB
Posted: 01/28/2004, 5:47 PM

The preferred way is to put an insert trigger on the Houses table. MySQL
isn't capable of that yet. So, you have to resort to logic in the After
Insert event of your form, that executes an INSERT query using a table
select in the FROM. IF that doesn't mean much, then look at this:

--- in the After Insert event ---

myForm->Datasource->Execute("INSERT INTO Rooms (column1, column2, etc)
SELECT column1, column2, etc FROM HousePlan WHERE HousePlanID = " .
HousePlanID);

I don't claim this to be completely correct syntax, but it illustrates the
idea: Use the connection for your form to select appropriate data from
HousePLan table and insert it into the Rooms table. The AfterInsert event
fires (as the name suggests) after the new house has been inserted. This
does not happen within the context of a transaction, so there is a tradeoff
to not having triggers to do ths with. Such is life.

Since you probably will need to insert the House's primary key, note that
you can retrieve it with the query "SELECT LAST_INSERT_ID ()", so you can
Execute that right before the INSERT, or combine it into the INSERT
directly.

--
DonB

http://www.gotodon.com/ccbth


"VBAjedi" <VBAjedi@forum.codecharge> wrote in message
news:54018529ce16b6@news.codecharge.com...
> How can I automatically create "child" records? Hypothetical scenario
(Using CCS, PHP, & MySQL):
>
> My MySQL database has a "Houses" table, a "HousePlan" table, and a "Rooms"
table.
>
> Using a CCS Record Form or Editable grid, the user creates a new record in
the "Houses" table, specifying which house plan number (from the "HousePlan"
table) was used to build this house.
>
> When that new house record is created, I want to automatically look at the
corresponding "HousePlan" record, see the definition of how many of each
type of room that plan calls for (ex. "2 bedrooms, 1 bath, 1 kitchen"), and
create a record in the "Rooms" table for each (which would be 4 rooms in
this example).
>
> Is there a standard approach to this type of issue? Any suggestions on how
I can go about doing this?
> _________________
> VBAjedi
> jedi at NOSPAM dot obie dot com
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>

VBAjedi


Posts: 34
Posted: 01/29/2004, 9:07 AM

Thanks, Peter and Don! I'm new at this so it will take me several days to process all that information and try out the techniques involved. . . will post back if I can't solve it from here.
_________________
VBAjedi
jedi at NOSPAM dot obie dot com
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.