Update error when adding 2 digit integer mysql

Question

I'm getting an error on a mySql insert when i go to insert a double digit integer. When inserting a single digit integer value everything works fine, but for the double digit e.g 18, I get a foreign key constraint error (below) and the column will default to 1.

Cannot add or update a child row: a foreign key constraint fails (deposit_guard.tenants, CONSTRAINT tenants_to_properties FOREIGN KEY (listing_id) REFERENCES properties (property_id))

Insert Code:

session_start();
require_once('../dbconnect.php');
if(isset($_POST["t_name"]))
/* multiline add to database and changed for my purposes http://www.webslesson.info/2017/03/multiple-inline-insert-data-using-ajax-jquery-php.html */
{
$t_name = $_POST["t_name"];
$t_email = $_POST["t_email"];
$t_deposit = $_POST["t_deposit"];
$t_start = $_POST["t_start"];
$t_duration = $_POST["t_duration"];
$prop_id = $_POST["prop_id"];
$query = '';
for($count = 0; $count<count($t_name); $count++)
{
$t_name_clean = mysqli_real_escape_string($conn, $t_name[$count]);
$t_email_clean = mysqli_real_escape_string($conn, $t_email[$count]);
$t_deposit_clean = mysqli_real_escape_string($conn, $t_deposit[$count]);
/* converting string to date: https://stackoverflow.com/questions/6238992/converting-string-to-date-and-datetime */
$t_duration_clean = mysqli_real_escape_string($conn, $t_duration[$count]);
$t_start_clean = date("y-m-d", strtotime($t_start[$count]));
$t_end = date('Y-m-d', strtotime("+" .$t_duration_clean[$count] ."months", strtotime($t_start_clean)));
$prop_id_clean = $prop_id[0];
if($prop_id_clean != '' && $t_name_clean != '' && $t_email_clean != '' && $t_deposit_clean != '' && $t_start_clean != ''  && $prop_id_clean != ''  && $t_duration_clean != '' && $t_end != '')
{
$query .= '
INSERT INTO tenants(listing_id, name, email, deposit_amount, s_date, duration, e_date) 
VALUES("'.$prop_id_clean.'","'.$t_name_clean.'", "'.$t_email_clean.'", "'.$t_deposit_clean.'", "'.$t_start_clean.'" , "'.$t_duration_clean.'" , "'.$t_end.'"); 
';
}
}
if($query != '')
{
if(mysqli_multi_query($conn, $query))
{
echo 'Tenant added';
}
else
{
    echo "Error updating record: " . mysqli_error($conn), $prop_id_clean;
}
}
else
{
echo 'All Fields are Required';
}
}
?>

Any ideas why this could be happening? I can add code if necessary, i'm using PHP if that is of any benefit. Thanks!

PASTEBIN: https://pastebin.com/k6FZVBy6

I would imagine that it's because the single digit integer you're inserting happens to be an acceptable value in the other table to which the foreign key relates. Check the "properties" table and see what the available values of "property_id" are. You'll probably find there is no 18 in there. — Loki, Feb 21 '18 at 14:58
@Loki there is an 18 in there, i checked the dB and I wouldn't be able to add it through the application otherwise — D.Neligan, Feb 21 '18 at 15:00
Are you sure this is right: `$prop_id_clean = $prop_id[0];` ? Should it not be `$prop_id_clean = $prop_id[$count];`? — Dalton Cézane, Feb 21 '18 at 15:03
Also, you have to check if the `$prop_id` is valid in the `properties` table (`property_id`)... — Dalton Cézane, Feb 21 '18 at 15:09
@Dalton i created a single number array for my ajax call in the other page ad the prop_id stays the same but the other array values change. I should change the name from $prop_id_clean to something more relevant by right. The prop_id has to be valid, it wouldn't show otherwise — D.Neligan, Feb 21 '18 at 15:09
If I go into phpMyAdmin, tenants table and insert i can select 18 as a value also — D.Neligan, Feb 21 '18 at 15:16

score 0 · Answer 1 · answered Feb 21 '18 at 15:23

For both the foreign key and the primary key data type and size must be same for both the table. If the size or data type is different then it will throw an error.
For your case the size of the integer is different , In primary table the size is 11 byte and in secondary table the size is 4 byte.

Update error when adding 2 digit integer mysql

1 Answers1