bindParam doesn't work when foreign key constraint set to cascade

Question

I'm creating a forum site for an assignment and the code worked fine until I changed two things: I changed my database constraints to CASCADE (needs to be like that for the rest of the site) and I changed the query function to prepare, bind and execute for security.

This code works

$insert_post_query =
"INSERT INTO posts (
    thread_id, user_id, username, post_content, post_date
)
VALUES (
    '$topic_id', '$user_id', '$username', '$post_content', '$post_date'
)";
$post_result = $db->query($insert_post_query);

This code doesn't work

$insert_post_query =
"INSERT INTO posts (
    thread_id, user_id, username, post_content, post_date
)
VALUES (
    '?', '?', '?', '?', '?'
)";
try{
$post_result = $db->prepare($insert_post_query);
$post_result->bindParam(1,$topic_id,PDO::PARAM_INT);
$post_result->bindParam(2,$user_id,PDO::PARAM_INT);
$post_result->bindParam(3,$username,PDO::PARAM_STR);
$post_result->bindParam(4,$post_content,PDO::PARAM_STR);
$post_result->bindParam(5,$post_date,PDO::PARAM_STR);
$post_result->execute();

}catch(Exception $e){
    echo "Unable to add the post<br>$e";
    exit;
}

This is the error

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (forum.posts, CONSTRAINT posts_ibfk_1 FOREIGN KEY (thread_id) REFERENCES threads (thread_id) ON DELETE CASCADE ON UPDATE CASCADE)

I'm fairly new to PHP so it could be something simple, and any help would be appreciated.

Do you have `threads` entity with `id` equal to `thread_id` you provide in bindParam? — lamik, Oct 29 '16 at 08:04

score 0 · Answer 1 · answered Oct 29 '16 at 04:46

you can do in this way, before insert data set foreign_key_checks = 0 than active it again with same query line with value 1.

SET foreign_key_checks = 0;
SET foreign_key_checks = 1;

$insert_post_query =
"INSERT INTO posts (
    thread_id, user_id, username, post_content, post_date
)
VALUES (
    '?', '?', '?', '?', '?'
)";
try{
$post_result = $db->prepare("SET foreign_key_checks = 0");
$post_result = $db->prepare($insert_post_query);
$post_result->bindParam(1,$topic_id,PDO::PARAM_INT);
$post_result->bindParam(2,$user_id,PDO::PARAM_INT);
$post_result->bindParam(3,$username,PDO::PARAM_STR);
$post_result->bindParam(4,$post_content,PDO::PARAM_STR);
$post_result->bindParam(5,$post_date,PDO::PARAM_STR);
$post_result->execute();
}catch(Exception $e){
    echo "Unable to add the post<br>$e";
    exit;
}
$post_result = $db->prepare("SET foreign_key_checks = 1");

bindParam doesn't work when foreign key constraint set to cascade

1 Answers1