Getting NULL in JSON

Question

I'm following this website to create mySQL table and php in order to convert data to JSON

SQL:

  CREATE TABLE IF NOT EXISTS `employee` (
  `id_employee` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(10) DEFAULT NULL,
  `designation` varchar(9) DEFAULT NULL,
  `date_joined` date DEFAULT NULL,
  `salary` decimal(7,2) DEFAULT NULL,
  `id_dept` int(2) DEFAULT NULL,
  PRIMARY KEY (`id_employee`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;


INSERT INTO `employee` (`id_employee`, `emp_name`, `designation`, `date_joined`, `salary`, `id_dept`) VALUES
(1, 'SMITH', 'CLERK', '2010-12-17', 2500.00, 20),
(2, 'ALLEN', 'SALESMAN', '2005-02-20', 3500.00, 30),
(3, 'WARD', 'SALESMAN', '2009-02-22', 3550.00, 30),
(4, 'JONES', 'MANAGER', '2010-04-02', 3975.00, 20),
(5, 'MARTIN', 'SALESMAN', '2011-09-28', 3300.00, 30);

PHP:

<?php
//Create Database connection
$db = mysql_connect("localhost","root","root");
if (!$db) {
    die('Could not connect to db: ' . mysql_error());
}

//Select the Database
mysql_select_db("test_json",$db);

//Replace * in the query with the column names.
$result = mysql_query("select * from employee", $db);  

//Create an array
$json_response = array();

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $row_array['id_employee'] = $row['id_employee'];
    $row_array['emp_name'] = $row['emp_name'];
    $row_array['designation'] = $row['designation'];
    $row_array['date_joined'] = $row['date_joined'];
    $row_array['salary'] = $row['salary'];
    $row_array['id_dept'] = $row['id_dept'];

    //push the values in the array
    array_push($json_response,$row_array);
}
echo json_encode($json_response);

//Close the database connection
fclose($db);

?>

However, i get 2 results with null and no other errors:

[{"id_employee":null,"emp_name":null,"designation":null,"date_joined":null,"salary":null,"id_dept":null},{"id_employee":null,"emp_name":null,"designation":null,"date_joined":null,"salary":null,"id_dept":null}]

I just copied the code and try to run it, how come there is no return in the result??

Can someone points out what's wrong with my code??

Or it is my server problem??

PHP version:5.2

MySQL ver. :5.1

Thank you

`mysql_query` is an obsolete interface and should not be used in new applications and will be removed in future versions of PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). If you're new to PHP, a guide like [PHP The Right Way](http://www.phptherightway.com/) can help explain best practices. — tadman, May 13 '14 at 15:30
Did you enable error reporting? If yes and you're still not getting anything, check the output of `json_last_error()`. — NikiC, May 13 '14 at 15:30
I would not trust any site or tutorial written in 2014 that recommends `mysql_connect()`. — Álvaro González, May 13 '14 at 15:31
what does a var dump of $json_response look like before you json_encode it? — dmgig, May 13 '14 at 15:34
var_dump($json_response): array(2) { [0]=> array(6) { ["id_employee"]=> NULL ["emp_name"]=> NULL ["designation"]=> NULL ["date_joined"]=> NULL ["salary"]=> NULL ["id_dept"]=> NULL } [1]=> array(6) { ["id_employee"]=> NULL ["emp_name"]=> NULL ["designation"]=> NULL ["date_joined"]=> NULL ["salary"]=> NULL ["id_dept"]=> NULL } } — user3532328, May 13 '14 at 15:46
It's not MySQL that's deprecated, but PHP's 'mysql_' API. So use the 'mysqli_' or 'PDO' API instead, and take best advantage of prepared statements. — Strawberry, May 13 '14 at 15:53
Results are shown after using MySQLi, [link](http://stackoverflow.com/questions/3351882/convert-mysqli-result-to-json), weird.. — user3532328, May 13 '14 at 16:06

Álvaro González · Answer 1 · 2014-05-13T15:41:05.567

0

Please have a look at mysql_set_charset(). As about JSON itself, last error can be fetched with json_last_error().

edited May 13 '14 at 15:41

answered May 13 '14 at 15:34

Álvaro González

142,137
41
261
360

score 0 · Answer 2 · answered May 13 '14 at 15:48

0

You should replace mysql_fetch_array to mysql_fetch_assoc so you can access the variable by column name.

Also, you should start using mysqli instead of mysql, to connect and interact wth mysql database from php.

answered May 13 '14 at 15:48

cbJava

13
4

Did you use mysql_fetch_assoc($result) ? – cbJava May 13 '14 at 16:15
yep.. still null. but i can get the data by using mysqli now. anyway, thanks – user3532328 May 13 '14 at 16:22

Getting NULL in JSON

2 Answers2