February 25, 2013

PHP PDO data into Json_Encode + but only first line appears

Question by Adam

                       $sql='SELECT word,language,acceptable FROM profanity;';
        foreach($pds as $row) {
            $profanityText = json_encode(array('word' => $row['word'],
                                       'language' => $row['language'],
                                       'acceptable' => $row['acceptable']));

I have the above code. The catch is it works but only the first line from the database goes into the json array. I’ve run the query directly against the DB and it pulls all the data.

I’m guessing my foreach loop has an issue or many the way I’m doing the PDO call.

any ideas?

Answer by Starx

The problem is that the variable holding the json encoded values $profanityText gets overidden every time in the loop.

Use this

    $rows = array()
    foreach($pds as $row) {

        $rows[] = array('word' => $row['word'],
            'language' => $row['language'],
            'acceptable' => $row['acceptable']);
    $profanityText = json_encode($rows);

Or, if you are not manipulating your data in anyway, you can just directly call (as mentioned by deceze)

$profanityTest = json_encode($pds->fetchAll(PDO::FETCH_ASSOC));

Please fill the form - I will response as fast as I can!