prepared statement in php is returning 0 rows

Question

I have a table with only one row called users in my database with email and mobile_number as it's columns.My row has that mobile_number column value as 9866163987.When I am trying to select this mobile number value my prepared statement is returning 0 rows.Here is my code.

if($this->checkExists("email",$_POST["registerEmail"]))
{
    --some code--
}
if($this->checkExists("mobile_number",$_POST["registerMobileNumber"]))
{
     --some code--
}
private function checkExists($field,$value)
{
    include("includes/connection.php");
    $sql = "SELECT $field FROM users WHERE $field=:value";      
    $prepare = $connection->prepare($sql);
    $prepare->bindParam(":value",$value,PDO::PARAM_STR);
    try
    {
        $prepare->execute();
    }       
    catch(PDOException $e)
    {           
        die($e->getMessage());
    }
    $count = count($prepare->fetchAll());
    echo $count."<br />";
    if($count == 1)
    {
        return true;
    }
    else
    {
        return false;
    }
}

For email it is working fine and returning one row,but for mobile_number it is returning 0 rows?what to do?

Check the actual value of `$value` to see if the input makes sense. Maybe the value was posted incorrectly. Or it's posted fine, but it just happens to be a number that doesn't match anything in the database. — GolezTrol, May 30 '15 at 01:55
thanks for your suggestion.i checked my database.it is exactly what you said.mismatching of values.but the problem is i am inserting 9866163987,but in the database some other value 2147483647 is being inserted.what to do??? — Siva Charan, May 30 '15 at 02:06
Are you representing the mobile number as an int? Sounds like you're running into an integer overflow problem. Try changing the column to a CHAR(10). The correct value you be persisted at that point. — keelerm, May 30 '15 at 02:33
What type is the field in the table? You might be overflowing the field. \2147483647 is the largest value that can be stored in a signed 32-bit integer. — Mike Stotts, May 30 '15 at 02:33

score 0 · Answer 1 · edited May 23 '17 at 12:14

Your interger is being truncated because it exceed the 32 bit limit of an int. (as @Mike Stotts said in the comments)

see What is the size of column of int(11) in mysql in bytes?

You could use a string instead or use something bigger like bigint.

Note: if you plan on using bigint, you should consider adding store_result(); before your bind_result to prevent memory exhaustion: See: PHP mysql_stmt::fetch() gives PHP Fatal error memory exhausted

prepared statement in php is returning 0 rows

1 Answers1