I have two tables, called items and posts. The primary key item_id in the table one is the foreign key in the table two, using INT UNSIGNED NOT NULL. The table one stores the item_id (UNSIGNED NOT NULL AUTO_INCREMENT), user id and the title of the post. The table two stores the following info: item_id, user_id, message, and posted_on
I do the validation for the new item id before running the query to make sure that there is a new post before any query is run:
// Validate item ID ($itemid), which may not be present:
if (isset($_POST['itemid']) && filter_var($_POST['itemid'], FILTER_VALIDATE_INT, array('min_range' => 1)) ) {
$itemid = $_POST['itemid'];
} else {
$itemid = FALSE;
}
After that, I make the INSERT INTO query using prepared statement for the table 2 like this:
$q = "INSERT INTO tablename2 (item_id, user_id, message, posted_on) VALUES (?,?,?, UTC_TIMESTAMP())";
//Prepare the statement
$stmt = $mysqli->prepare($q);
//Bind the parameters
$stmt->bind_param('iis', $item_id, $user_id, $message);
//Assign values to variables
$item_id = (int)$_POST['itemid'];
$user_id = $_SESSION['user_id'];// The user_id value would normally come from the session.
$message = strip_tags($_POST['message']);
//Execute the statement
$stmt->execute();
if ($mysqli->affected_rows == 1) {//
echo '<p>Your post has been entered.</p>';
} else {
echo '<p>Your post could not be handled due to a system error.</p>';
echo '<p>' . $stmt->error . '</p>';
}
// Close the statement:
$stmt->close();
Everything goes well, except that the item_id, which is inserted into the table2, appears to be 0 all the time, but not the same integer as it should be in the table 1 (Primary - Foreign' keys relationship b/w the two tables).
What did I do wrongly? can you help? Thanks.
