Why this PDO parametrized query behave "strangely"?

Question

Here i have edited my original question. I have alswo answered it, in my next message.

I'm trying to get results from MySQL with parametrized php PDO query, but thing behaves strangley. I dont know if it is a bug, or I am doing something wrong or not seeing something obvious.

Lets suppose there are these two tables in database

CREATE TABLE `users` (
`user_id` int(11) NOT NULL PRIMARY KEY 
) 

CREATE TABLE `users_contacts` (
`contact_id` int(11) NOT NULL PRIMARY KEY ,
`user_id` int(11) DEFAULT NULL,
`type` varchar(45) DEFAULT NULL,
`value` varchar(255) DEFAULT NULL
) 

Fill them with minimal data :

INSERT INTO `users` (`user_id`) VALUES (125);

INSERT INTO `users_contacts` (`contact_id`, `user_id`, `type`, `value`) 
    VALUES(11432, 125, 'email', 'losleyTyped@offten.stinks'),
          (11433, 125, 'phone', '1234567'),
          (564, 125, 'unit', '910');

And then you try to fetch data like this

$db_name = "";
$db_user = "";
$db_pass = "";
$db_pdo  = new pdo("mysql:host=localhost;dbname=$db_name","$db_user","$db_pass");


$user          = 125;
$user_unit_btm = 900;
$user_unit_top = $user_unit_btm + 100;

$upload_user = $db_pdo -> prepare("SELECT K.value AS unit
                                    FROM users AS O, 
                                         users_contacts AS K 
                                    WHERE   O.user_id = :user_id AND 
                                            K.user_id = O.user_id AND 
                                            K.type = 'unit' AND 
                                            K.value >= :unit_btm AND  
                                            K.value < :unit_top
                                 ");


$upload_user -> execute( [":user_id"   => $user,
                          ":unit_btm"  => $user_unit_btm,
                          ":unit_top"  => $user_unit_top
                         ]
                       );


$upload_user = $upload_user -> fetch(PDO::FETCH_ASSOC);

var_dump($upload_user);

var_dump will return false, but there is no error(err is 0000)

I have reduced the problem, and find that only one parameter ":organization" is problematic and cause for bizare behevior.

But if you replace " K.value < :unit_top " with variable $user_unit_top
" K.value < $user_unit_top "
Then, query returns result!

Same thing if i replace " K.value < :unit_top " with literal 1000,
" K.value < 100"
Then query returns result!

Why is this happening?

Answer 1

As mentioned in my comment to your answer.

The PHP documentation on PDOStatement::execute states.

An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.
Source: https://www.php.net/manual/en/pdostatement.execute.php

Additionally PDOStatement::fetch() returns false when there are no more results or upon failure.

The return value of this function on success depends on the fetch type. In all cases, FALSE is returned on failure.

Example https://3v4l.org/NVECJ

$pdo = new PDO('sqlite::memory:', null, null, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);
$pdo->query('CREATE TABLE foo(id INTEGER)');

$stmt = $pdo->prepare('SELECT * FROM foo');
$stmt->execute();
var_dump($stmt->fetch());
//bool(false)

If you need to explicitly define a data type, other than PDO::PARAM_STR for the parameter being sent to MySQL, you would use PDOStatement::bindParam or PDOStatement::bindValue

Example:

$upload_user = $db_pdo->prepare('SELECT 
        K.value AS unit
    FROM users AS O, 
        users_contacts AS K 
    WHERE O.user_id = :user_id 
    AND K.user_id = O.user_id
    AND K.type = 'unit'
    AND K.value >= :unit_btm
    AND K.value < :unit_top');
$upload_user->bindValue(':user_id', $user, PDO::PARAM_INT);
$upload_user->bindValue(':unit_btm', $user_unit_btm, PDO::PARAM_INT);
$upload_user->bindValue(':unit_top', $user_unit_top, PDO::PARAM_INT);
$upload_user->execute();

An alternative would be to force data type casting on the parameter in the query.

$upload_user = $db_pdo->prepare('SELECT 
        K.value AS unit
    FROM users AS O, 
        users_contacts AS K 
    WHERE O.user_id = :user_id
    AND K.user_id = O.user_id
    AND K.type = 'unit' 
    AND K.value >= (:unit_btm - 0)
    AND K.value < (:unit_top - 0)'); //CAST(:unit_top AS SIGNED)
$upload_user->execute([
    ':user_id' => $user,
    ':unit_btm' => $user_unit_btm,
    ':unit_top' => $user_unit_top
]);

---

Another contributing factor to your issue, is that MySQL will perform an automatic conversion to the column's data type for the comparison. Where other RDMBS, like PostgreSQL and SQLite3 do not perform the same conversions.

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts strings to numbers as necessary, and vice versa.
Source: https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

Since your initial column data type was VARCHAR, this resulted in the following from your testing.
DB Fiddle

Initial query as PDOStatement::execute([1000]).

SELECT IF('910' > '1000', 'fail', 'pass') AS if_str_to_str;

| if_str_to_str |
| ------------- |
| fail          |

Manually supplying integer to the Query

SELECT IF('910' > 1000, 'fail', 'pass') AS if_str_to_int;

| if_str_to_int |
| ------------- |
| pass          |

After changing the database column data type and using PDOStatement::execute([1000])

SELECT IF(910 > '1000', 'fail', 'pass') AS if_int_to_str;

| if_int_to_str |
| ------------- |
| pass          |

Using PDOStatement::bindValue(':param', '1000', PDO::PARAM_INT) or ('1000' - 0)

SELECT IF('910' > CAST('1000' AS SIGNED), 'fail', 'pass') AS if_str_to_typecast_int;

| if_str_to_typecast_int |
| ---------------------- |
| pass                   |