Search code examples
phpmysqlcharset

MySQL query result cannot be json encoded in PHP


I have the following PHP API which runs a MySQL query, when;

$sql = "select article_id, title, summary, image_url from articles limit 20"; 

I get the following result;

http://ec2-54-152-162-157.compute-1.amazonaws.com/list_view.php?user_id=1

the above is what I want the format of my output to look.

However when I change the $sql to below I get a blank screen;

$user_id = $_GET["user_id"];

$sql = "
select article_id, title, summary, image_url 
from articles 
where article_id in 
    (
    select max(article_id) as last_article_id 
    from articles as a 
    join media_sources as ms 
    on a.rss_source_id = ms.media_source_id 
    where article_id not in 
        (
        select article_id 
        from responses 
        where (activity_id = 1 and response = -1 and user_id = '1') 
        or (activity_id = 2 and user_id = '1')
    ) 
    group by category
) limit 20;"

$db = new  mysqli("remotehost", "user", "password", "db_name");
$results = $db->query($sql);
$articles["items"] = array();

while($article = $results->fetch_assoc()){
    $item = array ();
    $item["article_id"] = $article['article_id'];
    $item["article_title"] = $article['title'];
    $item["article_summary"] = $article['summary'];
    $item["article_image"] = $article['image_url'];
    array_push($articles["items"], $item);
    //echo json_encode($item);
}
echo json_encode($articles);

I've uncommented echo json_encode($item) in the 3rd from the bottom line of code; and put it in the below API. So there is data but I just can't get into the format I need.

http://ec2-54-152-162-157.compute-1.amazonaws.com/list_view2.php?user_id=1

**********************************EDIT******************************************

json_last_error_msg() returns:

Malformed UTF-8 characters, possibly incorrectly encoded. 

So I guess there are characters that can not be encoded in Json, I will have to investigate and strip them out. but still not sure why this one works as it is the same data getting encoded;

echo json_encode($item);

Solution

  • Added this line fixed it;

    mysqli_set_charset($db, "utf8");