json does not work with pdo but it works with mysql_ function

Question

This code works 100% but when I try to use JSON it does not display any data.

The problem is that I am trying to change all my codes to PDO since I was told that mysql_* function's are depreciated.

<?php
$con = new PDO('mysql:host=localhost;dbname=adnan;charset=UTF-8','root','');
$sql= $con->query('select *  from adnan_user');
while($row =$sql->fetch()){
    echo $row['user_id'],"\n";
}
?>

Here is the code which I call in json : its also works when I use mysql_ function But with pdo it does not work .

<?php 
$con = new PDO('mysql:host=localhost;dbname=adnan;charset=UTF-8','root','');
$sql= $con->query('select *  from adnan_user');
while($row =$sql->fetch()){
    $name = $row['name'];
    $user= $row['user']; 
}
// The JSON standard MIME header.
header('Content-type: application/json');
$array = array('name'=>$name, 'user'=>$user);
echo  json_encode($array);
?>

The code for json

<html>
    <head>
        <script src="http://code.jquery.com/jquery-latest.js"></script>
        <script> 
        $(document).ready(function(){
            $.getJSON('json.php', function(data) {
                $('#myJson').html('<table style="color:red"><tr><td>' + data.name + '</td><td>' + data.user + '</td></tr></table>');
            });
        });
        </script>
    </head>
    <body>
        <div id="myJson"></div>
    </body>
</html>

What do you mean with "It doesn't work". Is there an error message, is it invalid json or is there nothing happening at all? — Louis Huppenbauer, Jan 25 '13 at 09:56
Nothing happening at all yet when I use mysql_ it does display the targeted results — Musa, Jan 25 '13 at 09:59
@fab - Of course, you're right. I sometimes get mildly confused. — Louis Huppenbauer, Jan 25 '13 at 10:08
`$name` and `$user` are being overwritten with fresh data on each cycle of the `while` loop, so when you come to load them into the JSON, you'll only have the last record. Is that the intention? It doesn't look right. — SDC, Jan 25 '13 at 10:09
Have you considered using PDO's `fetchAll()` method instead of looping with `fetch()`? If you want to output the entire data set as an array, that would seem to be an easier option. It would solve the issue I highlighted above too. — SDC, Jan 25 '13 at 10:19
@john Here it is but not lite $name, 'user'=>$user); echo json_encode($array); ?> — Musa, Jan 25 '13 at 10:21

Fabian Schmengler · Accepted Answer · 2013-01-25T10:38:37.740

Your DSN in new PDO(...) is wrong, because in MySQL the UTF-8 charset is called utf8:

$con = new PDO('mysql:host=localhost;dbname=adnan;charset=utf8','root','');

See here for more information: http://php.net/manual/en/mysqlinfo.concepts.charset.php

Also you should catch exceptions, so PDO can tell you what's wrong next time:

try {
    $con = new PDO(...);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

score 1 · Answer 2 · answered Jan 25 '13 at 10:28

This will work, but in this case you only get the last record from your database. Why you have * in your sql? If you need all records you need to change the logic in your code.

 <?php
  $db = new PDO('mysql:host=localhost;dbname=core', 'root', ''); 
  $db->exec("SET NAMES 'utf8' COLLATE 'utf8_general_ci'");
  $sql = "select * from adnan_user";
  $result = $db->prepare($sql);
  $result->execute();
  $array = array();
  while($row = $result->fetch()){    
    $name = $row['name'];
    $user = $row['user'];       
  }
 $array['name'] = $user;
 $array['user'] = $name;
 header('Content-type: application/json');
 echo  json_encode($array);
?>

json does not work with pdo but it works with mysql_ function

2 Answers2