如何使用外键将PHP表单中的数据插入到MySQL数据库中？

问题描述：

I am dealing with a PHP form containing checkboxes and a MySQL db. I finally achieved to insert multiple rows, one for each selected item, by looping over the array.
But now, I have to face another issue: in my DB, I have one principal table to store the one-choice questions and another table to store the answers from the checkboxes.
I would like to first execute the query inserting the one-choice answers into the principal table (one row per form), so that it generates a serial ID.
And secondly, to get back this ID and associate it to every row inserted into the checkbox table in order to link the two tables though this ID.
Is that possible please and how should I do?

Here the HTML code:

<input type="checkbox" name="nature_contact[]" value="1"><label >Phone</label><br/>
<input type="checkbox" name="nature_contact[]" value="2"><label >Mail</label><br/>
<input type="checkbox" name="nature_contact[]" value="3"><label >Visit</label><br/>
<input type="checkbox" name="nature_contact[]" value="4"><label >Unk</label>    <br/><br/> 
<input type="text" name="coord"/>
            <br/>
<input type="text" name="tel"/>
            <br/><br/>              
<input type="submit" name="add" value="SEND"/>

And here the PHP part:

try {
    if(isset($_POST['add'])){   
        if(isset($_POST['coord'])) {
            $coord=$_POST['coord'];
        }
        else { $coord = '';
        }
        if(isset($_POST['tel'])) {
            $tel=$_POST['tel'];
        }
        else { $tel = '';
        }           
    $query="INSERT INTO nmp_mfs.general (coord, tel) VALUES ('".$coord."', '".$tel."')";
    $statement_gnl = $pdo->prepare($query); 
    $statement_gnl->execute();  
    }
}   
catch(PDOException $e) {
    $msg = 'ERREUR PDO dans ' . $e->getFile() . ' L.' . $e->getLine() . ' : ' . $e->getMessage();
    die($msg);
}

try {
    if(isset($_POST['add'])){   
        if(isset($_POST['nature_contact'])) {
            $sql = "INSERT INTO nmp_mfs.t_temporaire (nature_contact) VALUES ".rtrim(str_repeat('(?),', count($_POST["nature_contact"])), ',');
            $statement = $pdo->prepare($sql);
            $count = 1;
            foreach($_POST["nature_contact"] as $nature_contact) {
                $statement->bindValue($count++, $nature_contact);
            }
            $statement->execute();
        }
    }   
}   
catch(PDOException $e) {
    $msg = 'ERREUR PDO dans ' . $e->getFile() . ' L.' . $e->getLine() . ' : ' . $e->getMessage();
    die($msg);
}

答

Yes this is possible.

You need the last inserted id of the principal table row like:

$lastInsertedID = $db->lastInsertId();

1.) Insert the question in the database table (principal)

2.) Get the last inserted id ($lastInsertedID)

3.) Insert answers related to the question in the answer table and provide the last inserted id.

$query = "INSERT INTO nmp_mfs.t_temporaire (questionID, nature_contact) 
VALUES ($lastInsertedID, $nature_contact)"; // Example

4.) Select the ID's of your questions.

5.) Get the corresponding answers:

$query = "SELECT awnsers WHERE question_id = questionID"; // Simple example

答

To make sure your data synchronized,you can use transaction in mysql.Sorry for my poor english, I just want to do something useful.

如何使用外键将PHP表单中的数据插入到MySQL数据库中？

相关推荐