One Form into Multiple Tables

[revsu]

OK, I'm worse than a coding newbie, but I'm starting to piece things together. So that's why this has thrown me for a loop. I got help from here before, so I'm hoping for 2 for 2 :-)

 

I'm building a family tree database, and have a form (submit.php) to submit the info via (addsubmit.php). I've used essentially the same code I got from phpeasystart.com to make other functions for the web site, and they work.

 

But in this case, I need some fields to go to one table, and other fields to another, and I don't know the proper syntax for doing that. It comes up with the 'successful' message, but the tables are still empty, so I just know I'm doing something wrong. Help!

 

Thanks in advance,

Rev. Su

 

<?php

$host="rinasfamilytree.db.3589040.hostedresource.com"; // Host name
$username="rinasfamilytree"; // Mysql username 
$password="******"; // Mysql password 
$db_name="rinasfamilytree"; // Database name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("Sorry, I cannot connect to the server."); 
mysql_select_db("$db_name")or die("Sorry, I cannot select the database.");

$comment = mysql_real_escape_string($_POST[$comment]);
$username = mysql_real_escape_string($_POST[$username]);
$fullname = mysql_real_escape_string($_POST[$fullname]);
$birthdate = mysql_real_escape_string($_POST[$birthdate]);
$birthplace = mysql_real_escape_string($_POST[$birthplace]);
$deathdate = mysql_real_escape_string($_POST[$deathdate]);
$deathplace = mysql_real_escape_string($_POST[$deathplace]);
$residence1 = mysql_real_escape_string($_POST[$residence1]);
$residence2 = mysql_real_escape_string($_POST[$residence2]);
$residence3 = mysql_real_escape_string($_POST[$residence3]);
$residence4 = mysql_real_escape_string($_POST[$residence4]);
$dadname = mysql_real_escape_string($_POST[$dadname]);
$dadbirthdate = mysql_real_escape_string($_POST[$dadbirthdate]);
$dadbirthplace = mysql_real_escape_string($_POST[$dadbirthplace]);
$daddeathdate = mysql_real_escape_string($_POST[$daddeathdate]);
$daddeathplace = mysql_real_escape_string($_POST[$daddeathplace]);
$momname = mysql_real_escape_string($_POST[$momname]);
$mombirthdate = mysql_real_escape_string($_POST[$mombirthdate]);
$mombirthplace = mysql_real_escape_string($_POST[$mombirthplace]); 
$momdeathdate = mysql_real_escape_string($_POST[$momdeathdate]);
$momdeathplace = mysql_real_escape_string($_POST[$momdeathplace]);
$spouse1fullname = mysql_real_escape_string($_POST[$spouse1fullname]);
$spouse1marriagedate = mysql_real_escape_string($_POST[$spouse1marriagedate]);
$spouse1marriageplace = mysql_real_escape_string($_POST[$spouse1marriageplace]);
$spouse1birthdate = mysql_real_escape_string($_POST[$spouse1birthdate]); 
$spouse1birthplace = mysql_real_escape_string($_POST[$spouse1birthplace]);
$spouse1deathdate = mysql_real_escape_string($_POST[$spouse1deathdate]);
$spouse1deathplace = mysql_real_escape_string($_POST[$spouse1deathplace]);
$spouse2fullname = mysql_real_escape_string($_POST[$spouse2fullname]);
$spouse2marriagedate = mysql_real_escape_string($_POST[$spouse2marriagedate]);
$spouse2marriageplace = mysql_real_escape_string($_POST[$spouse2marriageplace]);
$spouse2birthdate = mysql_real_escape_string($_POST[$spouse2birthdate]);
$spouse2birthplace = mysql_real_escape_string($_POST[$spouse2birthplace]);
$spouse2deathdate = mysql_real_escape_string($_POST[$spouse2deathdate]);
$spouse2deathplace = mysql_real_escape_string($_POST[$spouse2deathplace]);
$kidname = mysql_real_escape_string($_POST[$kidname]);
$kidbirthdate = mysql_real_escape_string($_POST[$kidbirthdate]);
$kidbirthplace = mysql_real_escape_string($_POST[$kidbirthplace]);
$kidname2 = mysql_real_escape_string($_POST[$kidname2]);
$kidbirthdate2 = mysql_real_escape_string($_POST[$kidbirthdate2]);
$kidbirthplace2 = mysql_real_escape_string($_POST[$kidbirthplace2]);
$kidname3 = mysql_real_escape_string($_POST[$kidname3]);
$kidbirthdate3 = mysql_real_escape_string($_POST[$kidbirthdate3]);
$kidbirthplace3 = mysql_real_escape_string($_POST[$kidbirthplace3]);
$kidname4 = mysql_real_escape_string($_POST[$kidname4]);
$kidbirthdate4 = mysql_real_escape_string($_POST[$kidbirthdate4]);
$kidbirthplace4 = mysql_real_escape_string($_POST[$kidbirthplace4]);
$kidname5 = mysql_real_escape_string($_POST[$kidname5]);
$kidbirthdate5 = mysql_real_escape_string($_POST[$kidbirthdate5]);
$kidbirthplace5 = mysql_real_escape_string($_POST[$kidbirthplace5]);
$kidname6 = mysql_real_escape_string($_POST[$kidname6]);
$kidbirthdate6 = mysql_real_escape_string($_POST[$kidbirthdate6]); 
$kidbirthplace6 = mysql_real_escape_string($_POST[$kidbirthplace6]);
$kidname7 = mysql_real_escape_string($_POST[$kidname7]);
$kidbirthdate7 = mysql_real_escape_string($_POST[$kidbirthdate7]);
$kidbirthplace7 = mysql_real_escape_string($_POST[$kidbirthplace7]);
$notes = mysql_real_escape_string($_POST[$notes]);

("INSERT INTO relatives (username, fullname, birthdate, birthplace, deathdate, deathplace, residence1, residence2, residence3, residence4, spouse1fullname, spouse1marriagedate, spouse1marriageplace, spouse1birthdate, spouse1birthplace, spouse1deathdate, spouse1deathplace, spouse2fullname, spouse2marriagedate, spouse2marriageplace, spouse2birthdate, spouse2birthplace, spouse2deathdate, spouse2deathplace, notes) VALUES ($username, $fullname, $birthdate, $birthplace, $deathdate, $deathplace, $residence1, $residence2, $residence3, $residence4, $dadname, $spouse1fullname, $spouse1marriagedate, $spouse1marriageplace, $spouse1birthdate, $spouse1birthplace, $spouse1deathdate, $spouse1deathplace, $spouse2fullname, $spouse2marriagedate, $spouse2marriageplace, $spouse2birthdate, $spouse2birthplace, $spouse2deathdate, $spouse2deathplace, $notes)");

mysql_query("INSERT INTO parents (username, dadname, dadbirthdate, dadbirthplace, daddeathdate, daddeathplace, momname, mombirthdate, mombirthplace, momdeathdate, momdeathplace) VALUES ($username, $dadname, $dadbirthdate, $dadbirthplace, $daddeathdate, $daddeathplace, $momname, $mombirthdate, $mombirthplace, $momdeathdate, $momdeathplace)");

mysql_query("INSERT INTO kid1 (username, kidname, kidbirthdate,kidbirthplace ) VALUES ($username, $kidname, $kidbirthdate, $kidbirthplace)");

mysql_query("INSERT INTO kid2 (username, kidname2, kidbirthdate2, kidbirthplace2) VALUES ($username, $kidname2, $kidbirthdate2, $kidbirthplace2)");

mysql_query("INSERT INTO kid3 (username, kidname3, kidbirthdate3, kidbirthplace3) VALUES ($username, $kidname3, $kidbirthdate3, $kidbirthplace3)");

mysql_query("INSERT INTO kid4 (username, kidname4, kidbirthdate4, kidbirthplace4) VALUES ($username, $kidname4, $kidbirthdate4, $kidbirthplace4)");

mysql_query("INSERT INTO kid5 (username, kidname5, kidbirthdate5, kidbirthplace5) VALUES ($username, $kidname5, $kidbirthdate5, $kidbirthplace5)");

mysql_query("INSERT INTO kid6 (username, kidname6, kidbirthdate6, kidbirthplace6) VALUES ($username, $kidname6, $kidbirthdate6, $kidbirthplace6)");

mysql_query("INSERT INTO kid7 (username, kidname7, kidbirthdate7, kidbirthplace7) VALUES ($username, $kidname7, $kidbirthdate7, $kidbirthplace7)");

$result = mysql_query

//check if query successful 
if($result){
echo "Thanks for submitting a relative to the Rinas Diaspora database.";
echo "<BR>";
echo "<Use the back button in your browser to submit another relative or go to the Rinas Family Forest link on the navigation menu to see all the relatives that have been gathered so far! "; // 
}

else {
echo "Sorry, there has been an error writing to the database. Please use the back button on your browser and try again. If you get this error message again, please notify the Curator by emailing su at suleone dot com. Thanks!";
}


?>

 

[nomadrw]

I would first start off small only have 3 tables. When you figure how to make it work.

 

I think you need to add an ID field to your tables, an a master id as well.

 

will look something like this.

table one

id int not null primary auto increment,

date_added datetime,

date_modified datetine,

f_name varchar (75),

l_name varchar (75),

and soon

 

table 2

id int not null primary auto increment,

id_master int not null

date_added datetime,

date_modified datetine,

username varchar (75),

password varchar (75),

and soon

 

table 3

id int not null primary auto increment,

id_master int not null

date_added datetime,

date_modified datetine,

kid_fname varchar (75),

kid_lname varchar (75),

and soon

 

in the php

you will need to figure out how to connect the table using the id_master field.

//get mater_id for use with other tables

$master_id =

good luck

 

[ignace]

$comment = mysql_real_escape_string($_POST[$comment]);
$username = mysql_real_escape_string($_POST[$username]);
$fullname = mysql_real_escape_string($_POST[$fullname]);
$birthdate = mysql_real_escape_string($_POST[$birthdate]);
$birthplace = mysql_real_escape_string($_POST[$birthplace]);
$deathdate = mysql_real_escape_string($_POST[$deathdate]);
$deathplace = mysql_real_escape_string($_POST[$deathplace]);
$residence1 = mysql_real_escape_string($_POST[$residence1]);
$residence2 = mysql_real_escape_string($_POST[$residence2]);
$residence3 = mysql_real_escape_string($_POST[$residence3]);
$residence4 = mysql_real_escape_string($_POST[$residence4]);
$dadname = mysql_real_escape_string($_POST[$dadname]);
$dadbirthdate = mysql_real_escape_string($_POST[$dadbirthdate]);
$dadbirthplace = mysql_real_escape_string($_POST[$dadbirthplace]);
$daddeathdate = mysql_real_escape_string($_POST[$daddeathdate]);
$daddeathplace = mysql_real_escape_string($_POST[$daddeathplace]);
$momname = mysql_real_escape_string($_POST[$momname]);
$mombirthdate = mysql_real_escape_string($_POST[$mombirthdate]);
$mombirthplace = mysql_real_escape_string($_POST[$mombirthplace]); 
$momdeathdate = mysql_real_escape_string($_POST[$momdeathdate]);
$momdeathplace = mysql_real_escape_string($_POST[$momdeathplace]);
$spouse1fullname = mysql_real_escape_string($_POST[$spouse1fullname]);
$spouse1marriagedate = mysql_real_escape_string($_POST[$spouse1marriagedate]);
$spouse1marriageplace = mysql_real_escape_string($_POST[$spouse1marriageplace]);
$spouse1birthdate = mysql_real_escape_string($_POST[$spouse1birthdate]); 
$spouse1birthplace = mysql_real_escape_string($_POST[$spouse1birthplace]);
$spouse1deathdate = mysql_real_escape_string($_POST[$spouse1deathdate]);
$spouse1deathplace = mysql_real_escape_string($_POST[$spouse1deathplace]);
$spouse2fullname = mysql_real_escape_string($_POST[$spouse2fullname]);
$spouse2marriagedate = mysql_real_escape_string($_POST[$spouse2marriagedate]);
$spouse2marriageplace = mysql_real_escape_string($_POST[$spouse2marriageplace]);
$spouse2birthdate = mysql_real_escape_string($_POST[$spouse2birthdate]);
$spouse2birthplace = mysql_real_escape_string($_POST[$spouse2birthplace]);
$spouse2deathdate = mysql_real_escape_string($_POST[$spouse2deathdate]);
$spouse2deathplace = mysql_real_escape_string($_POST[$spouse2deathplace]);
$kidname = mysql_real_escape_string($_POST[$kidname]);
$kidbirthdate = mysql_real_escape_string($_POST[$kidbirthdate]);
$kidbirthplace = mysql_real_escape_string($_POST[$kidbirthplace]);
$kidname2 = mysql_real_escape_string($_POST[$kidname2]);
$kidbirthdate2 = mysql_real_escape_string($_POST[$kidbirthdate2]);
$kidbirthplace2 = mysql_real_escape_string($_POST[$kidbirthplace2]);
$kidname3 = mysql_real_escape_string($_POST[$kidname3]);
$kidbirthdate3 = mysql_real_escape_string($_POST[$kidbirthdate3]);
$kidbirthplace3 = mysql_real_escape_string($_POST[$kidbirthplace3]);
$kidname4 = mysql_real_escape_string($_POST[$kidname4]);
$kidbirthdate4 = mysql_real_escape_string($_POST[$kidbirthdate4]);
$kidbirthplace4 = mysql_real_escape_string($_POST[$kidbirthplace4]);
$kidname5 = mysql_real_escape_string($_POST[$kidname5]);
$kidbirthdate5 = mysql_real_escape_string($_POST[$kidbirthdate5]);
$kidbirthplace5 = mysql_real_escape_string($_POST[$kidbirthplace5]);
$kidname6 = mysql_real_escape_string($_POST[$kidname6]);
$kidbirthdate6 = mysql_real_escape_string($_POST[$kidbirthdate6]); 
$kidbirthplace6 = mysql_real_escape_string($_POST[$kidbirthplace6]);
$kidname7 = mysql_real_escape_string($_POST[$kidname7]);
$kidbirthdate7 = mysql_real_escape_string($_POST[$kidbirthdate7]);
$kidbirthplace7 = mysql_real_escape_string($_POST[$kidbirthplace7]);
$notes = mysql_real_escape_string($_POST[$notes]);

 

This is tiresome work as these values come from a form. You can ease this process by naming your fields in the following format:

 

<tablename>_<columnname>

 

Then using something similar like:

 

function clean($value, $key = null) {
    $value = trim($value);
    $value = strip_tags($value);
    $value = htmlentities($value, ENT_QUOTES);
    $temp = @mysql_real_escape_string($value)
        ? $value = $temp
        : $value = addslashes($value);
    return $value;
}

$_POST = array_walk('clean', $_POST);

$tableData = array();
foreach ($_POST as $key => $value) {
    $fieldParts = explode('_', $key, 1); // tablename_column_name => tablename, column_name (note: 1)
    $tableName = current($fieldParts);
    if (!isset($tableData[$tableName])) {
        $tableData[$tableName] = array();
    } else {
        $columnName = end($fieldParts);
        $tableData[$tableName][$columnName] = $value;
    }
}

foreach ($tableData as $table => $data) {
    $keys = implode(', ', array_keys($data));
    $values = implode(', ', array_values($data));
    $sql = "INSERT INTO $table ($keys) VALUES ($values)";
    mysql_query($sql) or trigger_error("Query $sql failed.", E_USER_WARNING);
}

 

Will input the data into the correct tables.

[revsu]

Oh, dear... That was rather scary for me to look at, Mr. Ignace :-) Thank you for your answer though. I appreciate your help!

 

Firstly, all my tables do have auto_increment primary key ID's so I can make a relative equal a parent and/or child manually. I would prefer to do it manually because there can be such overlap in relations, and people from all over the world will be contributing to this database. Often the wording or spelling may be slightly different but be fundamentally the same and a computer wouldn't catch that unless I did some major programming which is redundant because I have a supercomputer inside my skull. ;-)

 

I had thought at first to have one big "children" table. But I decided to have one for each "kid" because every person in the family will eventually be a relative AND a child (and possibly a parent as well), and I won't know which kid position they will be listed in.

 

A few questions:

Where would I plug your code into mine? Or what would it replace?

 

Is there syntax punctuation I can add to my code to make it work (I understand mine)?

 

Where would I put my user friendly success and error messages in your code?

 

Thanks again,

Rev. Su

www.suleone.com/rinas (the site in question)