CODEWITHSUNDEEP
phpmysqlcharset
Rhys
Rhys

Reputation: 425

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);

Upvotes: -3

Views: 85

Answers (2)

Rhys
Rhys

Reputation: 425

Added this line fixed it;

mysqli_set_charset($db, "utf8");

Upvotes: -1

dbers
dbers

Reputation: 654

If its working from MySQL console then the issue has to most likely be the credentials that the PHP script uses to connect (maybe it can't access something). Is it the same user? does the php user have permissions to create temporary tables and access all the data needed?

Or perhaps it takes to long and is error'ing out. Which command are you using for the php? Try echoing the error that you are getting back or checking the logs. If its failing it would tell you why it failed.

If its php i'd think its either the mysql user permissions, the php max_execution setting, php max memory

Upvotes: -1

Related Questions