CODEWITHSUNDEEP
phpmysqljsonpdo
user2621382
user2621382

Reputation:

Sql statement returns null when there is actually a value

I am using Flight framework at backend for building a RESTful service and PDO for retrieving data from MySql. 

require 'flight/Flight.php';    

Flight::route('GET /satellites', function () {
    $sql = "SELECT * FROM satellites";
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $response = json_encode($stmt->fetchAll());
        $db = null;
        echo "{sucess: true, records:" . "$response" . "}";
    } catch (PDOException $e) {
        echo '{"error":{"text":' . $e->getMessage() . '}}';
    }        
});

Flight::start();

I get this JSON in response:

{
    sucess: true, 
    records:[
        {
            "id":"1",
            "dates":"01.01.2007 - 01.01.2011",
            "satellite":null,
            null:"98.2",
            "resolution":"15",
            "band":"30",    
            "price":"$15"
        }
    ]
}

The above is correct, but satellite shouldn't be null, it definitely has a value (I checked it through PHPMyAdmin). Also there is a column called null with value 98.2, which should be called nadir. Why is it called null?

BTW, data type of satellite and nadir columns is varchar(255). I then changed satellite column datatype to text, but all the same.

Upvotes: 2

Views: 101

Answers (2)

Vin.AI
Vin.AI

Reputation: 2437

In your record there is something may be hidden characters, as your var_dump data shown:

array(1) {
 [0]=> array(10) {
  ["id"]=> string(1) "1"
  ["dates"]=> string(23) "01.01.2007 - 01.01.2011"
  ["satellite"]=> string(9) "SAT­7" // This 4 character is showing count 9
  ["nadir­"]=> string(4) "98.2"
  ["resolution"]=> string(2) "15"
  ["band"]=> string(2) "30"
  ["price"]=> string(3) "$15"
 }
}

Above "SAT7" is actually looking like 4 character word but it has some hidden characters, var_dump showing count. Similarly "nadir" has also hidden characters.

Fix above issue and continue

Here json output is giving this:

[{
    "id":"1",
    "dates":"01.01.2007 - 01.01.2011",
    "satellite":"SAT\u00ad7", // Hidden characters in value
    "nadir\u00ad":"98.2", // Hidden characters in field
    "resolution":"15",
    "band":"30",
    "price":"$15"
}]

Upvotes: 1

Rahi
Rahi

Reputation: 324

  in  getConnection(); you use 
   // You might missing this line set utf8
   $pdoParams = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8;');

   $params = array(
    'host' => 'localhost',        'username' => 'username',
    'password' => 'password',
    'dbname' => 'dbname',
    'driver_options' => $pdoParams
   );

Upvotes: 0

Related Questions