Adobe Community: Posting data to multiple linked tables

Greg Heffner Calculating status...

Currently Being Moderated

Posting data to multiple linked tables

May 19, 2012 7:49 PM

I am trying to post data from a single form into 3 seperate tables linked by primary and forign keys (all one to many relationships).

I am just a beginner, but it sounded simple to me anyway. I spent several days trying to get it to work, Nada!

The form is broken down into 3 insert statements

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "Driver Registration")) {
$insertSQL = sprintf("INSERT INTO race_database (association, district, event_name, event_date, event_location, open_registration, close_registration, first_boat_cost, add_boat_cost, additional_info) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['association'], "text"),
                       GetSQLValueString($_POST['district'], "text"),
                       GetSQLValueString($_POST['event'], "text"),
                       GetSQLValueString($_POST['event_date'], "date"),
                       GetSQLValueString($_POST['event_location'], "text"),
                       GetSQLValueString($_POST['open_registration'], "date"),
                       GetSQLValueString($_POST['closed_registration'], "date"),
                       GetSQLValueString($_POST['first_boat_cost'], "int"),
                       GetSQLValueString($_POST['add_boat_cost'], "int"),
                       GetSQLValueString($_POST['additional_info'], "text"));

mysql_select_db($database_RTP_1080, $RTP_1080);
$Result1 = mysql_query($insertSQL, $RTP_1080) or die(mysql_error());
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "Driver Registration")) {
$insertSQL = sprintf("INSERT INTO driver_database (first_name, last_name, address, city_town, zip_code, `state`, phone_number, mobile_number, e_mail, association_number) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, now())",
                       GetSQLValueString($_POST['firstname'], "text"),
                       GetSQLValueString($_POST['lastname'], "text"),
                       GetSQLValueString($_POST['address'], "text"),
                       GetSQLValueString($_POST['citytown'], "text"),
                       GetSQLValueString($_POST['zipcode'], "int"),
                       GetSQLValueString($_POST['state'], "text"),
                       GetSQLValueString($_POST['phonenumber'], "int"),
                       GetSQLValueString($_POST['mobile'], "int"),
                       GetSQLValueString($_POST['email'], "text"),
                       GetSQLValueString($_POST['associationnumber'], "text"));

mysql_select_db($database_RTP_1080, $RTP_1080);
$Result1 = mysql_query($insertSQL, $RTP_1080) or die(mysql_error());
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "Driver Registration")) {
$insertSQL = sprintf("INSERT INTO boat_database (boat_class, main_frequency, alt_frequency, high_points, cost) VALUES (%s, %s, %s, %s, %s, now())",
                       GetSQLValueString($_POST['boatclass'], "text"),
                       GetSQLValueString($_POST['mainfreq'], "text"),
                       GetSQLValueString($_POST['altfreq'], "text"),
                       GetSQLValueString(isset($_POST['highpoints']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString($_POST['cost'], "int"));

mysql_select_db($database_RTP_1080, $RTP_1080);
$Result1 = mysql_query($insertSQL, $RTP_1080) or die(mysql_error());
}

The relationships are shown here

My best results, are that I get data in the first table but none in both joined tables.

I am asuming it has to do with the way im inserting the data, but after some reseach, i have seen others have had similar problems, all which I could not understand.

Im sure this is a common problem just need some direction.

Thanks

Greg

883 Views 4 Replies Latest reply: Greg Heffner, May 24, 2012 4:02 PM

Replies

Currently Being Moderated

1. Ben Pleysier,

May 20, 2012 5:08 PM in reply to Greg Heffner

Report

You also need to include the linked ID into your table. To find the ID of the last inserted record see here http://php.net/manual/en/function.mysql-insert-id.php

Gramps

|
Mark as:
Currently Being Moderated

2. Greg Heffner,

May 23, 2012 4:40 PM in reply to Ben Pleysier

Report

Thanks Gramps for the information.

I'm sure this is the correct method, I just am unable to get it working.
I have tried for several days and looked online at the information regarding this.
printf("Last inserted record has id %d\n", mysql_insert_id());

I cannot find any examples that are similar enough to my set up to get it. Below is the error message I am getting and below that is the code I am tring. Does this look anywhere close to correct?

Last inserted record has id 0 Last inserted record has id 26 Cannot add or update a child row: a foreign key constraint fails (`1080_test2`.`driver_database`, CONSTRAINT `driver_database_ibfk_1` FOREIGN KEY (`race_id_link`) REFERENCES `race_database` (`race_id`) ON DELETE CASCADE ON UPDATE CASCADE)
---------------------------------------------------------------------- --------------------------
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "Driver Registration")) {
$insertSQL = sprintf("INSERT INTO race_database (association, district, event_name, event_date, event_location, open_registration, close_registration, first_boat_cost, add_boat_cost, additional_info) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

                       GetSQLValueString($_POST['association'], "text"),
                       GetSQLValueString($_POST['district'], "text"),
                       GetSQLValueString($_POST['event'], "text"),
                       GetSQLValueString($_POST['event_date'], "date"),
                       GetSQLValueString($_POST['event_location'], "text"),
                       GetSQLValueString($_POST['open_registration'], "date"),
                       GetSQLValueString($_POST['closed_registration'], "date"),
                       GetSQLValueString($_POST['first_boat_cost'], "int"),
                       GetSQLValueString($_POST['add_boat_cost'], "int"),
                       GetSQLValueString($_POST['additional_info'], "text"));
printf("Last inserted record has id %d\n", mysql_insert_id());
mysql_select_db($database_RTP_1080, $RTP_1080);
$Result1 = mysql_query($insertSQL, $RTP_1080) or die(mysql_error());
}
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "Driver Registration")) {
$insertSQL = sprintf("INSERT INTO driver_database (first_name, last_name, address, city_town, zip_code, `state`, phone_number, mobile_number, e_mail, association_number) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

                       GetSQLValueString($_POST['firstname'], "text"),
                       GetSQLValueString($_POST['lastname'], "text"),
                       GetSQLValueString($_POST['address'], "text"),
                       GetSQLValueString($_POST['citytown'], "text"),
                       GetSQLValueString($_POST['zipcode'], "int"),
                       GetSQLValueString($_POST['state'], "text"),
                       GetSQLValueString($_POST['phonenumber'], "int"),
                       GetSQLValueString($_POST['mobile'], "int"),
                       GetSQLValueString($_POST['email'], "text"),
                       GetSQLValueString($_POST['associationnumber'], "text"));
printf("Last inserted record has id %d\n", mysql_insert_id());

|
Mark as:
Currently Being Moderated

3. Ben Pleysier,

May 23, 2012 7:20 PM in reply to Greg Heffner

Report

The following will illustrate how to insert the data into the database using UserID as the relationship between the two tables
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO users (UserID, UserFirstName, UserLastName) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['UserID'], "int"),
                       GetSQLValueString($_POST['UserFirstName'], "text"),
                       GetSQLValueString($_POST['UserLastName'], "text"));

mysql_select_db($database_myConnection, $myConnection);
$Result1 = mysql_query($insertSQL, $myConnection) or die(mysql_error());

$insertSQL = sprintf("INSERT INTO user_address (UserAddressID, UserID, Street, City) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['UserAddressID'], "int"),
                       GetSQLValueString(LAST_INSERT_ID(), "int"), // the ID value from the 1st table
                       GetSQLValueString($_POST['Street'], "text"),
                       GetSQLValueString($_POST['City'], "text"));

mysql_select_db($database_myConnection, $myConnection);
$Result1 = mysql_query($insertSQL, $myConnection) or die(mysql_error());
}
Gramps

|
Mark as:
Currently Being Moderated

4. Greg Heffner,

May 24, 2012 4:02 PM in reply to Ben Pleysier

Report

Thanks very much Gramps. Now where did I see an example like this. You made this so much eaiser.
I had to use msql_insert_id() to get it to work.

2nd time you helped me...

|
Mark as:

Dreamweaver

Posting data to multiple linked tables

May 19, 2012 7:49 PM

More Like This

Bookmarked By (0)

Answers + Points = Status