0

I am working on a school assignment where I have to take a Twitter dump as an SQL and save it as a JSON (to be used for D3). I am having some issues with the JSON encoding, however. I was able to get date, user, retweets, favorites to all function properly, but for some reason, the actual text of the tweet is only encoding as "text". I've checked the table and everything on there looks correct, but for some reason, my encoder is not encoding the text correctly. Here is the code I have to encode the Twitter SQL table into JSON:

<?php
$dsn = 'mysql:host=localhost;dbname=twitter';
    define('MYSQL_USER','root');
    define('MYSQL_PASS','dismypassword');

$tweets = "SELECT `id`,`date`,`screen_name`,`favorite_count`,`favorited`,'text' as 'textThing' FROM tweets";

$sql = $tweets;

header("Content-Type: application/json");

try {
    $db = new PDO($dsn, MYSQL_USER, MYSQL_PASS);
    $statement = $db->prepare($sql);
    $statement->execute();
    $data = array();
    while ( ($row = $statement->fetch(PDO::FETCH_ASSOC)) ) {
        $data[] = $row;
    }
    echo json_encode($data);  
} catch (PDOException $e) {
    die('Query failed: ' . $e->getMessage());
}

?>

The output from this is generating a JSON file that looks like this(where the text is coming up as simply "text":

[

{
id: "704419321326510080",
date: "2016-02-29 22:33:48",
screen_name: "cscstars",
favorite_count: "0",
favorited: "0",
textThing: "text"
},

{
id: "704419329287331840",
date: "2016-02-29 22:33:50",
screen_name: "Wary12",
favorite_count: "0",
favorited: "0",
textThing: "text"
},

{
id: "704419365588865024",
date: "2016-02-29 22:33:58",
screen_name: "lgib15",
favorite_count: "0",
favorited: "0",
textThing: "text"
},

{
id: "704419366243151874",
date: "2016-02-29 22:33:59",
screen_name: "jacqui4peace",
favorite_count: "0",
favorited: "0",
textThing: "text"
},
Lars Kotthoff
  • 107,425
  • 16
  • 204
  • 204
stigma_wizard
  • 1
  • How is that inserted on the table? Can you run the select outside php to check? On phpmyadmin maybe. Since you are at it, in case the table has the correct data, what is the result for `var_dump($data);`? (You can limit the select so it doesn't get too long) – FirstOne Mar 08 '16 at 02:15
  • MySQL is generating (as an example) text such as: "RT @Notnow71: Exactly! Compromise is what makes this nation work,extremists on both sides are causing gridlock #ImWithHer https://t.co/YGeâ" Which is coming up as simply "text" when I encode it – stigma_wizard Mar 08 '16 at 02:28
  • 1
    Is that coming in the `var_dump`? Or you checked dirrectly on the database? Please, include (at least some of) the output from `var_dump($data);`in the question... – FirstOne Mar 08 '16 at 02:34
  • 1
    Your query is wrong: you write `'text' as 'textThing'` instead of `\`text\` as 'textThing'` – fusion3k Mar 08 '16 at 02:36
  • Thanks for your response. I tried adding the backticks to text, however when I did that, the HTTP request comes back as empty, giving me no JSON data at all. – stigma_wizard Mar 08 '16 at 02:41
  • No data means there was an error. It should be at least `Array()`. – FirstOne Mar 08 '16 at 02:44
  • Thanks! Any advice where I might be able to track the error down from? It's puzzling that the text comes back as "text" when the backticks aren't there, but as soon as I add them, it blows up the entire process. – stigma_wizard Mar 08 '16 at 02:47
  • Just a question: what happens if you run `SELECT * FROM tweets;`? Or even removing `as 'textThing'` and just keeping `\`text\``? – FirstOne Mar 08 '16 at 02:53
  • When I use $tweets = "SELECT * FROM tweets"; I am getting the same issue where there is no HTTP response. – stigma_wizard Mar 08 '16 at 02:56
  • Same thing if I use just ` `text` ` – stigma_wizard Mar 08 '16 at 02:56
  • Oh well. It's late, the only thing I can help you with then is with some links: [How to squeeze error message out of PDO?](http://stackoverflow.com/q/3726505/4577762), [How do I get PHP Errors to display?](http://stackoverflow.com/q/1053424/4577762), [Syntax error due to using a reserved word as a table or column name in MySQL](http://stackoverflow.com/q/23446377/4577762). And a (probably off) **guess**: what is in the page's source when inspected the blank? – FirstOne Mar 08 '16 at 03:00
  • Thanks for your help again. I thought that maybe the "date" and "text" columns were interfering with how the php file read, so I renamed them to "dtg" and "twt" only to run into no HTTP response again. I did, however, find that getting rid of the "twt" in the select statement allows the program to work. I believe something within the actual tweet texts that we are using is causing an issue. I will see if I can create an escape statement. – stigma_wizard Mar 08 '16 at 03:08

1 Answers1

0

I found the root of my problem. It wasn't the JSON encoder at all, instead it was special characters in the tweet texts that was causing the function to fail. After I scrubbed the tweet sql table the function worked perfectly.

stigma_wizard
  • 1