表示不以正确的方式写入数据库

Question

Heey,

I am busy with a form that has to insert its value into a database (mysql). The form has to write to two tables called address and person. The persons information needs to write to person and its address has to write to the address table. However when I press submit, it will say everything went successfully BUT it doesn't store information in person only in address.

In the database person_address is linked to address_id in a later state I will create a detail form where the "admin" can select city OR state and it will show all the persons in the city and state.

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "INSERT INTO person (person_firstname, person_lastname, person_email,      person_phonenumber, person_cv)
VALUES     ('$_POST[firstname]','$_POST[lastname]','$_POST[telephone]','$_POST[email]','$_POST[cv]')";

$sql = "INSERT INTO address (address_street, address_housenumber,     address_zipcode, address_city, address_state)
VALUES
('$_POST[straat]','$_POST[huisnummer]','$_POST[postcode]','$_POST[stad]','$_POST[provincie]')";

if ($conn->query($sql) === TRUE) {
    $URL="http://localhost:8080/Website/bedankt.php";  

header ("Location: $URL");  
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Answer 1

As mentioned, you overwrite your $sql variable without executing it. Also, consider sanitizing and binding your $_POST variables with prepared statements. Additionally you can use mysqli->insertid to capture the auto_increment address_id in address table and use it in subsequent person append query to maintain relationships between both tables. Of course you need to reverse the order of the sql statements.

// DATABASE CONNECTION
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

// ADDRESS APPEND - PREPARE SQL STATEMENT AND BIND PARAMS
$stmt = $conn->prepare("INSERT INTO address (address_street, address_housenumber, 
                                             address_zipcode, address_city, address_state)
                        VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param("sssss", $straat, $huisnummer, $postcode, $staad, $provincie);

$straat = htmlspecialchars($_POST[straat]);
$huisnummer = htmlspecialchars($_POST[huisnummer]);
$postcode = htmlspecialchars($_POST[postcode]);
$stad = htmlspecialchars($_POST[stad]);
$provincie = htmlspecialchars($_POST[provincie]);

// EXECUTE STATEMENT
$result = $stmt->execute();    
if ($result === FALSE) {
    die("Error: " . $stmt->error);
}

// CAPTURE LAST INSERTED address_id
$last_id = $conn->insert_id;

// PERSON APPEND - PREPARE SQL STATEMENT AND BIND PARAMS
$stmt = $conn->prepare("INSERT INTO person (person_firstname, person_lastname, 
                                            person_email, person_phonenumber,
                                            person_cv, person_address)
                         VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param("sssssi", $firstname, $lastname, $telephone, $email, $cv, $last_id);

$firstname = htmlspecialchars($_POST[firstname]);
$lastname = htmlspecialchars($_POST[lastname]);
$telephone = htmlspecialchars($_POST[telephone]);
$email = htmlspecialchars($_POST[email]);
$cv = htmlspecialchars($_POST[cv]);

// EXECUTE STATEMENT
$result = $stmt->execute();    
if ($result === TRUE) {
    $URL="http://localhost:8080/Website/bedankt.php";  
    header ("Location: $URL");  
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
$conn->close();