how to read json from database

Question

I have json string in database

{"api":[{"caseid":2,"fullname":"df","businessname":"asdf","phonenumber":"12345678","activity":"sdf","province":7,"wilayat":"adfasd","description":"sfasdf","casedate":"2018-02-08 11:39:19"},{"caseid":3,"fullname":"\u0646\u0648\u0631\u062e\u0627\u0646 \u062a\u0627\u062a\u0627\u0631\u064a","businessname":"\u0627\u0644\u0631\u0641\u062f","phonenumber":"98818663","activity":"\u0646\u0649\u0633 \u064a\u0634\u064a \u0647\u062e\u0634 \u0645\u062a \u0634\u064a\u0628\u0634\u0633","province":1,"wilayat":"\u0627\u0644\u0633\u064a\u0628","description":"\u0648\u0635\u0641 \u0627\u0644\u0634\u0643\u0648\u064a","casedate":"2018-02-08 12:14:24"},{"caseid":4,"fullname":"test","businessname":"asdf","phonenumber":"12345678","activity":"asdfas","province":3,"wilayat":"dfasdf","description":"fsdfasdfasdf","casedate":"2018-02-08 12:24:53"},{"caseid":5,"fullname":"\u0645\u062d\u0645\u062f \u0627\u0633\u0644\u0627\u0645","businessname":"\u0635\u0646\u062f\u0648\u0642 \u0627\u0644\u0631\u0641\u062f","phonenumber":"98818663","activity":"\u0649\u0646 \u0633\u0634 \u0646\u062e \u0645\u0643 \u0648\u0646\u062e \u0635\u062e\u0636 \u062d\u062e\u0634","province":1,"wilayat":"\u0627\u0644\u0633\u064a\u0628","description":"\u0634\u0628 \u0634\u0633\u064a\u0628 \u0634\u0633\u064a\u0628 \u0634\u0633\u0628 \u062b\u0636\u0635  \u0631\u062b\u0631\u062b \u0631\u062b\u0631\u0631 \u0629\u0645 \u0629\u0648\u0648 \u0646\u0646\u062e\u062d","casedate":"2018-02-12 11:23:38"},{"caseid":6,"fullname":"\u0641\u0647\u062f \u0627\u0644\u062d\u0627\u0631\u062b\u064a","businessname":"\u062a\u062c\u0631\u0628\u0629 \u0627\u0644\u0646\u0638\u0627\u0645 ","phonenumber":"95871817","activity":"\u0644\u0627 \u064a\u0648\u062c\u062f ","province":1,"wilayat":"\u0627\u0644\u0633\u064a\u0628 ","description":"\u062a\u062c\u0631\u0628\u0629 1 ","casedate":"2018-02-13 13:40:51"}]}

$apiContent have above json string

$apiContent = $this->db->from("users_api")->get()->row()->apicontent;

when i write echo $apiContent it's shows the whole string but when i try to decode json string it's shows nothing

print_r(json_decode($apiContent, TRUE));

here is my whole function

function saveImportComplaint($xp)
{       
        $apiContent = $this->db->from("users_api")->get()->row()->apicontent;               
        return json_decode($apiContent, TRUE);      
}

After adding makejsonsafe function i can read and convert json, error coming because of unicode value

function makejsonsafe($myjsonstring)
{
    for ($i = 0; $i <= 31; ++$i) 
    {
        $myjsonstring = str_replace(chr($i), "", $myjsonstring);
    }
    $myjsonstring = str_replace(chr(127), "", $myjsonstring);
    if (0 === strpos(bin2hex($myjsonstring), 'efbbbf')) 
    {
        $myjsonstring = substr($myjsonstring, 3);
    }
    $myjsonstring = json_decode( $myjsonstring );
    return $myjsonstring;
}

[AGAIN?](https://stackoverflow.com/questions/48771113/parsing-json-for-decode-not-working/48771216#48771216) ? — Oleg Butuzov, Feb 14 '18 at 06:15
Possible duplicate of [parsing json for decode not working](https://stackoverflow.com/questions/48771113/parsing-json-for-decode-not-working) — Muhammed Imran Hussain, Feb 14 '18 at 06:16
It is Unclear why your code isn't working. Show us: `$apiContent`. There is something that you aren't telling us. — mickmackusa, Feb 14 '18 at 06:21
@mickmackusa i already write in my question $apiContent have above json string comes from the database — Evil Inc., Feb 14 '18 at 06:23
I just copied your json string to https://jsonlint.com/ and it does, in fact, validate. So you are doing something wrong and we can't help you without knowing more. You say that the json string is what is in the database ... but is that what is _actually_ being delivered to `$apiContent`? — mickmackusa, Feb 14 '18 at 06:25
@mickmackusa yes dud i also checked my string in http://jsonviewer.stack.hu/ it's showing perfectly but i don't know why it's not decoding in my function — Evil Inc., Feb 14 '18 at 06:27
I am not familiar with ci, so can you try `echo gettype( $apiContent );` and check what is the result? — Eddie, Feb 14 '18 at 06:28
I'd like to see: `if((@json_decode($apiContent,true))===null && json_last_error()!==JSON_ERROR_NONE){ echo json_last_error(); }` — mickmackusa, Feb 14 '18 at 06:29
My first suspicion is the unicode values. Are you following "UTF-8 All The Way Through"? (Have you read that one?) https://stackoverflow.com/questions/279170/utf-8-all-the-way-through This might explain why other resources are validating properly, yet your application chokes. Also, what php version are you on? It might not matter, just thought I'd ask. — mickmackusa, Feb 14 '18 at 06:34
@mickmackusa yes you are right and i found the solution by adding this function function makejsonsafe($myjsonstring) { for ($i = 0; $i <= 31; ++$i) { $myjsonstring = str_replace(chr($i), "", $myjsonstring); } $myjsonstring = str_replace(chr(127), "", $myjsonstring); if (0 === strpos(bin2hex($myjsonstring), 'efbbbf')) { $myjsonstring = substr($myjsonstring, 3); } $myjsonstring = json_decode( $myjsonstring ); return $myjsonstring; } — Evil Inc., Feb 14 '18 at 06:38
Please do your best to post an educational answer which will serve to help future readers. Posting the solution as a comment is really hard to read. Then you can accept your own answer. — mickmackusa, Feb 14 '18 at 06:44
@EvilInc. Do not add the solution to your question as an Edit. Please post an answer to your own question -- this is totally legitimate and helpful. Also include some explanation as to why your method works and what each step is going. — mickmackusa, Feb 14 '18 at 08:27

score 0 · Accepted Answer · answered Feb 25 '18 at 07:53

It looks like you are dealing with:

a Byte Order Mark
and potentially some unicode / zero-width characters

Without seeing your actual input string, I can't confidently write a perfect pattern or test my code, but my method should accomplish what your posted workaround does...

function makejsonsafe($myjsonstring){
    return json_decode(preg_replace('/[\x00-\x1F\x7F]|^\xEF\xBB\xBF/u','',$myjsonstring));
    // return the data as an object
}

Pattern Breakdown:

[                #start character class
    \x00-\x1F    #match range between "null" and "unit separator"
    \x7F         #match "delete"
]                #end character class
|                #or
^\xEF\xBB\xBF    #match your BOM at start of string

All that said, a shorter pattern may do the same trick:

\p{Cc} is the same as [\x00-\x1F\x7F], so you might use: /\p{Cc}|^\xEF\xBB\xBF/u

\P{C} will only target printable characters, so you might use the inverse: /\p{C}+/u

References:

how to read json from database

1 Answers1