inserting data into multiple tables using php via a web form

Question

i have been recently studying php and mysql and also is currently developing a online web form using php and mysql. in the form i have multiple tables that i need to submit data to. i have tried this code;

 $required_fields = array('event_type','accommodation_type','public_user','comments','grand_total');
    $required_fields2 = array('first_name','last_name','gender','age_group');
    $errors = array_merge($errors, check_required_fields($required_fields, $_POST));
    $errors2 = array_merge($errors2, check_required_fields($required_fields2, $_POST));

    $fields_with_lengths = array('event_type' => 50, 'accommodation_type' => 50, 'public_user' => 50,'comments' => 10000,'grand_total' => 50);
    $fields_with_lengths2 = array('first_name' => 50, 'last_name' => 50, 'gender' => 50,'age_group' => 50);
    $errors = array_merge($errors, check_max_field_lengths($fields_with_lengths, $_POST));
    $errors2 = array_merge($errors2, check_max_field_lengths($fields_with_lengths2, $_POST));

    $event_type = trim(mysql_prep($_POST['event_type']));
    $accommodation_type= trim(mysql_prep($_POST['accommodation_type']));
    $public_user = trim(mysql_prep($_POST['public_user']));
    $comments = trim(mysql_prep($_POST['comments']));
    $grand_total = trim(mysql_prep($_POST['grand_total']));

    if (empty($errors)){
        $query = "INSERT INTO event_registration (event_type, accommodation_type, public_user, comments, grand_total) VALUES ('{$event_type}','{$accommodation_type}','{$public_user}','{$comments}','{$grand_total}')";
        $result = mysql_query($query, $connection);

        if($result){
            $message = "The User was successfully registered";
        } else{
            $message = "The User could not be registered";
            $message .= "<br />" . mysql_error();
        }
    } else{

        if(count($errors)==1){
            $message = "There was 1 error in the form.";
        } else {
            $message = "There were" . count($errors) . " error in the form.";
        }   
    }

} else { // Form has not been submitted
    $event_type = "";
    $accommodation_type = "";
    $public_user = "";
    $comments = "";
    $grand_total = "";
}

Could you please edit your question to add information about what exactly does not work? Are you getting an error? — Peter Lang, Feb 14 '10 at 21:07
Is there any reason you have the brackets surrounding the variable values in the query? — malonso, Feb 14 '10 at 21:16
@malonso> This is called "complex syntax" in that {braces} can interpret variable values and expressions inside strings. Lookup 'strings' in the PHP language reference. I often use this syntax myself when referencing object or array index values in a "quoted string". — bdl, Feb 14 '10 at 21:27
when i do submit the form on the web browser, the data entered did not enter into the data base. one table is event_registration and this table has user as a foreign key. i am believeing that this is what is causing it to not submit the data. this form also has a miscellaneous table. so when a user submit a form, the event_registration would be updated also the miscellaneous which has event_registration as a foriegn key. the problem is that i don't know why the data is not submitting. — pundit, Feb 14 '10 at 23:17
@pundit: In that case, you should be getting an error. What's the *exact* error message, and what's the exact query that's failing? Generally speaking, a request for help should include the behavior you expect and the behavior you get, which includes errors. Does the script fail for every input, or only certain input? If only certain input, the question should say which input fails and which succeeds. Since table definitions might be involved, you should also include the `create table` statements in your question. http://catb.org/~esr/faqs/smart-questions.html — outis, Feb 15 '10 at 00:22
@bdl - Sorry, random brain-fart. I was doing a bunch of other stuff when I glanced at his post and misinterpreted what I was seeing. @pundit - Can you print $query right before you it attempts to run it? Is it what you expect? What happens if you run that query manually? — malonso, Feb 15 '10 at 12:25

score 2 · Accepted Answer · edited Feb 22 '16 at 13:42

In general, here's how you post data from one form into two tables:

<?php
$dbhost="server_name";
$dbuser="database_user_name";
$dbpass="database_password";
$dbname="database_name";

$con=mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to the database:' . mysql_error());

$mysql_select_db($dbname, $con);

$sql="INSERT INTO table1 (table1id, columnA, columnB) 
         VALUES (' ', '$_POST[columnA value]','$_POST[columnB value]')";

mysql_query($sql);

$lastid=mysql_insert_id();

$sql2="INSERT INTO table2 (table1id, table2id, columnA, columnB)
              VALUES ($lastid, ' ', '$_POST[columnA value]','$_POST[columnB value]')";

//table1id & table1id are auto-incrementing primary keys 

mysql_query($sql2);

mysql_close($con);

?>

This example shows how to insert data from a form into multiples tables, I have not shown any security measures.

inserting data into multiple tables using php via a web form

1 Answers1

Linked