We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Retrieve a JSON string from database

I have a database with fields that contain JSON arrays. If I PDO select the row contents of one of these tables via straight PHP I can dump the data to the screen and it matches the database. Example:

$query = $database->prepare("SELECT * FROM modsettings
                WHERE proj_id = ? AND mo = ? ORDER BY id DESC LIMIT 1");
$query->execute(array($proj["id"], 2));
$result = $query1->fetch(PDO::FETCH_ASSOC);
var_dump($result);
{"mid":"","module_id":"1" ......} // stripped excessive content

When I attempt this same with PhalconPHP I am greeted with garbage.

$result = Modulesettings::findFirst( array( 'conditions' => 'project_token = "' . $token . '"' , 'columns' => 'settings' ) );
var_dump($result->settings);
object(Phalcon\Mvc\Model\Row)#61 (1) { ["settings"]=> string(167) "{"text":"<\/a>

<\/a>
","class":""}" }

How would I get this raw string out of the table? All other data returns fine from all the other tables.

Thanks.



51.1k

You could try this 3 solutions:

  • Use the built-in method toArray:
$st_result = $result->toArray();
  • Using getters
public function getSettings()
{
    return json_decode($this->settings, true);
}
  • Using afterFetch
public function afterFetch()
{
    if ($this->settings) {
        $this->settings = json_decode($this->settings, true);
    }
}


1.8k

Calin, thank you.

Unfortunately none of these ideas produced any results that I have not already experienced. It seems there is some other escaping of results by the framework and I need to find an alternate method to gather the data.



3.1k
edited Sep '15

try this one... 

            $result = Modulesettings::findFirst( array( 'conditions' => 'project_token = "' . $token . '"' , 'columns' => 'settings' ) );
            echo json_encode($result->settings);


1.8k

Thank you Jan however no change. Output still does not match record in table.



51.1k

The only reason why it won't output what you expect, can be related to the data saved in your "settings" column. I am using the afterFetch method without any problems. Also, when I save the data I am doing it with a setter:

public function setMyColumn(array $data)
{
    $this->data = json_encode($data, JSON_FORCE_OBJECT);
    return $this;
}


125.8k

It's a little unclear to me exactly what is being retrieved from the database. Could you paste what you expect and what you're getting?

Also, please use code formatting - it's easier to read.