CODEWITHSUNDEEP
phpmysql
GLRotterdam
GLRotterdam

Reputation: 143

Form not writing to database the right way

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.

picture 1 picture 2 picture 3 

// 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();
?>

Upvotes: 0

Views: 72

Answers (2)

Parfait
Parfait

Reputation: 107587

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();

Upvotes: 1

gunterB
gunterB

Reputation: 195

As Fred -ii- already said, you are overwriting the first query by writing the second query to the same variable. To solve the problem you should use two queries. 

$sql1 = "...";

$sql2 = "...";

if($conn->query($sql1) === TRUE && $conn->query($sql2) === TRUE) {
   ...
}

Also, there is SQL injection possible because you don't escape the $_POST parameters. This allows other people to execute malicious SQL on your server. You should have a look at mysqli::prepare or mysqli::real_escape_string.

Upvotes: 2

Related Questions