douna1892
2013-10-24 19:52 阅读 29
已采纳

使用单个php表单将数据添加到两个表

Using MySql and phpmyadmin, I have two tables person and report which are linked using the field person_id as a foreign key in the report table. Using one php form I am trying to add a persons details to the person table. Using the same form I want it to post the date to the report table which will then generate an auto increment report_id and link that person with a specific report on that date using the person_id foreign key.

Here is my current code

<?php

if (isset($_POST['submitted'])) {

    include('dbcon.php'); //link to connection file

    $pid = "SELECT person_id FROM person
"
         . "ORDER BY person_id DESC
"
         . "LIMIT 1"; //variable finds last generated person_id

    $sqlinsert1 = "INSERT INTO person (person.title, person.first_name, person.last_name,  person.address, person.contact_no, person.email, person.ha_id) VALUES ('$_POST[title]' , '$_POST[first_name]' , '$_POST[last_name]' , '$_POST[address]' , '$_POST[contact_no]' , '$_POST[email]' , '$_POST[ha_id]')";

    $sqlinsert2 = "INSERT INTO report (report.date) VALUES ('$_POST[date]') WHERE ($pid = report.person_id)";

    if (!mysqli_query($dbcon, $sqlinsert1)) {
        die('Error inserting record');
    } //end of nested if statement

    if (!mysqli_query($dbcon, $sqlinsert2)) {
        die('Error inserting record');
    } //end of nested if statement

    $newrecord = "new record added to database";
} //end of if statement

?>

I have created the variable $pid which will find the last person_id generated in the person table, I have tested this and it works in phpmyadmin. I want to then use this variable to link the date with the person_id and place it into the report table.

This may sound quite complicated but I'm sure there is an easy answer.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    dongyi9783 dongyi9783 2013-10-28 21:54

    After talking with my lecturer and spending hours echoing out all the outcomes, we found that the connection sqli statement ('mysqli_query') was connecting to the server but wasn't actually connecting to the database itself, therefor not allowing the person_id to be pulled from the person table with the select statement.

    We put in a standard sql connection statement and specified the database name separately. This now seems to work and I have added another foreign key from another table ('defect_id') and have added another insert statement for the defect table. I can now run this code and it works fine.

    <?php
    
                if (isset($_POST['submitted'])) {
    
        include('dbcon.php');
    
                //insert statement 1    
            $sqlinsert1 = "INSERT INTO person (title, first_name, last_name, address, contact_no, email, ha_id) VALUES ('$_POST[title]' , '$_POST[first_name]' , '$_POST[last_name]' , '$_POST[address]' , '$_POST[contact_no]' , '$_POST[email]' , '$_POST[ha_id]')";
    
            if (!mysqli_query($dbcon, $sqlinsert1)) {
            die('Error inserting record1');
            } //end of nested if statement1
    
    
                        // connect to database      
    $dbcon2 = mysql_connect('localhost' , 'root' , '' ); //mysqli query would not connect to db so using mysql connection
    
            if (!$dbcon2){
            die('error connecting to database');
            }
    
    $dbselect = @mysql_select_db('potholes_v2');
    
            if (!$dbselect){
            die('error connecting database');
            }
    
                        //insert statement 2            
    $sqlinsert2 = "INSERT INTO defect (road, location, carriageway, lane, diameter, depth, speed, description) VALUES ('$_POST[road]' , '$_POST[location]' , '$_POST[carriageway]' , '$_POST[lane]' , '$_POST[diameter]' , '$_POST[depth]' , '$_POST[speed]' , '$_POST[description]')";
    
            if (!mysqli_query($dbcon, $sqlinsert2)) {
            die('Error inserting record2');
            } //end of nested if statement1
    
    
    mysql_close(); //close database connection
    
    
                       //connect to database
    $dbcon2 = mysql_connect('localhost' , 'root' , '' ); //mysqli query would not connect to db so using mysql connection
    
            if (!$dbcon2){
            die('error connecting to host');
            }
    
    $dbselect = @mysql_select_db('potholes_v2');
    
            if (!$dbselect){
            die('error connecting database');
            }
    
                        //select person_id value
    $value = mysql_query("SELECT person_id FROM person ORDER BY person_id DESC" , $dbcon2); //selects last entry of person_id in person table
    
            if (!$value) {
            die ('error, no values'); //error if no value selected
            }
    
    $value2 = mysql_result($value,0); //specifies new value to be inserted into report table as person_id foreign key
    
                        //select defect_id value    
    $defect = mysql_query("SELECT defect_id FROM defect ORDER BY defect_id DESC" , $dbcon2); //selects last defect_id entry from defect table
    
            if (!$defect) {
            die ('Error, no values for defect'); //error if no value selected
            }
    
    $defect2 = mysql_result($defect,0); //specifies new defect value to be placed in report table
    
                        //insert statement 3
    $sqlinsert3 = "INSERT INTO report (date, person_id, defect_id) VALUES ('$_POST[date]' , $value2 , $defect2)"; //inserts date, person_id and defect_id values into report table
    
            if (!mysqli_query($dbcon, $sqlinsert3)) {
            die('Error inserting record 3');
            } //end of nested if statement1
    
    
    mysql_close(); //close database connection  
    
    
    
    
    
    $newrecord = "new record added to database";  //gives feedback for successful submission
    
    }  //end of initial if statement
    
    ?>
    
    点赞 评论 复制链接分享
  • dta38159 dta38159 2013-10-24 20:04

    I think error is in $_POST[date] using ' quotes. your code like following..

    Take person id after when you are inserting a person.

    and then insert in report

    $sqlinsert2 = "INSERT INTO report (report.pid,report.date) VALUES ('$pid',"$_POST['date']");//changed check it
    

    then your both table will link together, with pid (reference key).

    点赞 评论 复制链接分享

相关推荐