Update query not taking integer variable

Question

Update query not taking integer variable ($user):-

$user = $_POST["user"];
$user = (int)$user;
$sql = "UPDATE users_meta
    SET 
        `meta_value` = '$plan_end'
    WHERE 
        `user_id` = $user AND 
        `meta_name` = 'plan_end'";
$conn->query($sql);

user_id column in mysql database is set to int datatype.

When I simply put a number instead of the variable, it works:-

WHERE 
    `user_id` = 37 AND ...

I also tried without converting the number from string to int, and its not working. I have a feeling that it has something to do with quotes, so I played around with it based on suggestions online, but none worked.

Do you get any error messages? Have you look at the Error logs?? — RiggsFolly, Jun 04 '21 at 11:47
See about sql injection and the importance of prepared and bound/executed queries — Strawberry, Jun 04 '21 at 11:47
A prepared statement would probably solve your problem [php.net](https://www.php.net/manual/de/mysqli.quickstart.prepared-statements.php) [w3schools](https://www.w3schools.com/php/php_mysql_prepared_statements.asp) — noah1400, Jun 04 '21 at 11:47
To get errors out of PHP even in a LIVE environment add these 4 lines to the top of any `MYSQLI_` based script you want to debug `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);`. This will force any `MYSQLI_` errors to generate an Exception that you can see on the browser as well as normal PHP errors. — RiggsFolly, Jun 04 '21 at 11:48
You really ought to be using a prepared statement, as others have suggested. BUT creating your query this way can be good for troubleshooting. Echo out your query and make sure your `$user_id` is populating as you think it should. — aynber, Jun 04 '21 at 11:57

score 0 · Answer 1 · answered Jun 04 '21 at 12:24

As per based on my knowledge if database field is of Int type then it cannot accept character/string values even you are converting them into type of integer. You have to modify the database field to varchar or you have to pass only numeric values to the database field. Hope this suggestion may solve your problem.

score 0 · Answer 2 · answered Jun 04 '21 at 12:35

Hi I have tested your query on my local it works fine so I don't think there is any thing wrong with you query.

<?php

$mysqli = new mysqli("localhost","root","","test_table");

// Check connection
if ($mysqli -> connect_errno) {
  echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
  exit();
}


if(@$_POST["user"])
    {
$user = $_POST["user"];     
$tasone = $_POST["tasone"];
$user = (int)$user;
$sql = "UPDATE test_table SET `TAS_ONE` = '$tasone' WHERE  `user_id` = $user and `TAS_TWO`='dsadsa'";
$mysqli->query($sql);
    }
?>

<form action="" method="post">
  <input type="text" id="user" name="user" value=""><br>
  <input type="text" id="tasone" name="tasone" value=""><br>
  <input type="submit" value="Submit">
</form> 

CREATE TABLE `test_table` (
  `USER_ID` int(9) NOT NULL,
  `TAS_ONE` varchar(200) NOT NULL,
  `TAS_TWO` varchar(200) NOT NULL,
  `TAS_THREE` varchar(200) NOT NULL,
  `TAS_FOUR` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Update query not taking integer variable

2 Answers2