Inserting records into Multiple Tables at one with DataAssist is this possible

Hi I would like to insert and update, search, results and delete records from multiple tables in one form operation.

Basically I have 4 tables in mysql db.

1. Main Venue Listings
2. Meeting Rooms
3. Rates
4. Users - i.e. the inputter.

The relationship between db 1 and 3 and 4 is a one to one mapping and they have primary keys, like User id, Listing id and Rates id.
So if a user registers, they have a login, registration information and then fill out a listing and a rates forms. How do I get they to a 1 to 1 mapping between these three tables, so in admin I can see 1 user, and corresponding listing and rates records and create all this in DataAssist 2.0 please?

Secondly I need to setup another relationship, how do I do this in DataAssist 2.0?
I need 1 listings id which can a venue name to possible correspond to 1 to 40 Meetiing rooms id's (input with your multiple input, update and delete.
So the listing id and venue name from the listing table needs to be populated in the meeting rooms table on a 1 to many relationship.
How do I do this in DataAssist 2.0?

Thirdly, when you tell DataAssist 2.0 what form elements to use the default always seems to be text field. I have a lot of checkbox that are needed for the data elements. What should I change the data type to in mysql tables so that Data Assist automatically recognised the need for a checkbox. So far the intend checkbox fields are INT types.

Thank you for all your help,

Neil

Using DataAssist you can insert a single record or multiple records. A single DataAssist insert server behavior will only insert data into a single table, however you can have as many of these on a page as you need to do all of your inserting.

For example, if on a single page you would like to insert into the multiple tables using the id from the first table to record as the foreign key on the other tables you would do so like this:

create the page and form to gather the info from the user. If you use the wizard you will need to specify the first table and the page that is created will only contain form elements for that table. Next you would update this page by adding in the additional form elements that you would need to record the information you want to insert into the other tables.

Once the form elements are in place you will need to add in the additional DataAssist Insert server behaviors. You will need one of these for each table you wish to insert into. You can also use the insert multiple for a table where you need to insert multiple records.

By default the form elements will be text inputs, you can update these to be checkboxes though. It will always use the text inputs as the default input type.

I would like to send you mysql for the db structure, my workbench file for foreign keys and then how I can implement this.

At the moment I have 9 basic tables

1. Admin User Table
2. Standard User Table

3. Listings
4. Meeting Rooms
5. Rates

6. Corporate Days
7. Inspirational Locations
8. Special Offers
9. Eating Places
10. Spa and Leisure
------------------------
1. Admin has the ability to search all records on all tables - not a problem at this stage.

2. Users have the ability to register, insert their listing only, search for their listing only, update, delete and bring back results on their listing only (so table 3).

3. Users once registered can also set up a record in tables 6-10, again they can search, update and delete their own details.

4. Each registered user can have 1 or listings (table 3).
5. Each Listing can have 1 record only in the (table 5) rates table.
6. Each Listing in table 3 can have 0-40/50 or more meeting rooms (table 4).

SO I have setup data assist insert, update and delete, search and results pages for 3-10.
I have not setup the registration yet for table 2.

What I am unsure of is how to modify the forms in the already existing insert, update and delete, search and results pages for 3-10.

also filling out forms 3-6 has to occur in one session for one user.

Can I email you the files and mysql etc so you can advise me of how to complete this please?

Many thanks,

Neil

It would really appreciate some help with the DataAssist tools like session variables and cookies toolkit.

I have set up foreign keys across tables but now I have no idea what to implement on the Webassist side (tools) to make sure that all the data input in one session will populate the mysql database and across tables/records relate to eachother.

Urgent deadline looming!

Thanks,

Neil

Before you can begin trying to implement DataAssist for the inserts and updates you will need to have your db structure defined.

Based on what you have posted I think you will need to have a userID column in the following tables:

3. Listings

6. Corporate Days
7. Inspirational Locations
8. Special Offers
9. Eating Places
10. Spa and Leisure

The following tables will need to have a column to hold the listing id:
4. Meeting Rooms
5. Rates

Once you have these tables properly crafted you will use DataAssist to generate a set of pages to manipulate the data in these tables. DataAssist does not incorporate your requirements for data integrity, this is something you will need to take care of in the design of your pages. For example since you would like to have only one listing per user you should do a check of the listing table for the current user to ensure that a record for that user does not already exist.

From the perspective of the user rite now it would go like this:

A new user registers
Once logged in the user can add a listing
On the insert page for the listing you will bind the id for the currently logged in user with the userID column you have in this table. This is the same for the other tables that have the userID column, it will be bound to the id for the currently logged in user.
After the listing has been inserted you will have the id for that listing record available from the session, you then make sure to reference this session variable for the listing id when inserting into tables 4 and 5.

Please post back with any questions that you have about creating the pages or using DataAssist to edit the insert and update statements. When you reply make sure to post questions about the functionality from the perspective of the end user that will be interacting with the pages, I find it much easier to address situations when questions are posed in this way.

As far as obtaining your files to evaluate and offer guidance that really goes beyond the scope of the support that can be offered. I can address specific questions about use and functionality of the tools, or how to implement certain features in the context of our tools but tutoring or guiding is not something that I can provide.

Eric,

Thank you for your help.

1. Do I need to add in a user id as a primary key, as a foreign key or just another column?
2. I have already does this using foreign keys but I am fairly new to mysql so I am not sure if this will work.

If a user register's and then logs in they will have a user id unqiue to them.
There it is a question of specially how I use data assist to insert for example a listing record, so that from the users point of view the record is populated with a new unique listing id, and their user id.

Therefore from a dataAssist point of view when a clicks on add a listing how I populate the listing with their user id.

Secondly for example when they click update, or delete listing, I use data assist to create the update/delete page, but ensure is only brings back the record with the user id of the current session??

Is that descriptive enough? Or sufficient from the point of view of interactivity with the user. I suppose you can liken this example to a shopping cart in eCart where users can only create, update, delete and search on their orders.
So I think of tables 3 and 6 to 10 as different order types in different order tables that a customer may enter.

Thank you very much, your time and help is greatly appreciated.

Thanks,
Neil

The user id that you would have in any table other than the user's table would technicallly be a foreign key, but it is not necessary to define this in the table, you can just have it be another column in the table.

How you go about associating a record with a user is to ensure that your login server behavior is storing the userID in a session variable. When you insert a record into a table where you want also record the user you will use the DataAssist insert server behavior, in the list of column select the userID column in this table and use the lightning bolt icon to associate this field with your userID session variable. This goes for any table that you have the userID column added to associate those records with a particular user.

When it comes to updating and deleting after these pages are created by DataAssist you will want to update the recordsets on these pages. You will need to add in a where clause and say to make it select the records where the userID is equal to the session variable for the logged in user.

If you have any trouble with any part of this let us know and we can provide more info or details depending on what you need.

Hi Eric,

I have the USER id in every other table using Foreign keys.
I will as you say insert the userID in the session variable for each form/page.
I will try the insert server behaviour tonight.
In addition if there are other foreign key columns I want to record do I insert these as a hidden field?

I am not sure hopw to do this: "add in a where clause and say to make it select the records where the userID is equal to the session variable for the logged in user."

I also want to search where the userID is equal to the session variable for the logged in user.I think this is all generally part of the same question.

Thanks,
Neil

Eric,

So I have now associate the Listing User ID (one of the primary keys on the listings table) to the USER ID (one of the primary keys on the USER table), using the following code.
Data Assist Insert record, then lightening bolt, then in please select a server entity field: Session: Select USER_ID.
<?php echo $_SESSION['USER_ID']; ?>

I did a document of screenshots but its 200k and I cant compress it.

Thanks,
Neil

This sounds like you are on the rite track, if the user is logged in the session variable that holds the userID should be available to you. So you just reference it for the userID column value when inserting or updating your listing table. I think you are doing this part ok.

As for the where clause you will need to update the recordsets to ensure that they only let you update or delete entries that match for that currently logged in user. To modify these recordsets you will need to edit them from the server behaviors. In the simple view you would need to update the filter section and filter based on the userID column equal to 'session value' then enter the name of your session variable that holds the userID.

If the recordset it too complex then you may need to make this update manually in the sql that is on the page.