convert mysql query into json object via php

Question

i have a problem to modify and convert php array to json object, the array forms via mysql query. desired json format is as follows:

{
"uy":[
        {
    "pid": "23334",
    "t":[
        {
        "k": "Serkan AKYAKA",
        "msj":"message1",
        "tar": "24 Mayis 2011"
        },
        {
        "k": "Ali AKTAS",
        "msj":"message1",
        "tar": "01 Nisan 2011"
        }           
        ]
         },
    {
    "pid": "234534",
    "t":[
        {
        "k": "Gulden DURAY",
        "msj":"message1",
        "tar": "17 Haziran 2011"
        },
        {
        "k": "Ali AKTAS",
        "msj":"message1",
        "tar": "05 Mayis 2011"
        }           
        ]
    }  
] 
}

i have a table including data as follows:

pid(not unique), k(not unique), msg(message text), date(date of the message)

i have php code like below however i could not manage to convert json object as i want

$op='{';
mysql_select_db($database, $rdb);
$query_tav="SELECT pid, k, msj, tar FROM u_t WHERE rid=1 ORDER BY ABS(id)";
$r_tav = mysql_query($query_tav, $rdb) or die(mysql_error());
$tav = mysql_fetch_assoc($r_tav);
row_sayi = mysql_num_rows($r_tav);
if ($row_sayi > 0) {
    do {
        $op=$op.'"t":['.json_encode($tav).'],';
    } while ($tav = mysql_fetch_assoc($r_tav));
}
$op=$op.'}';

the php code above has many missing points however i can not figure out how to do it.

thanks for your help.

Code should be indented for readability (see my edit). The mysql extension is outdated and on its way to deprecation. New code should use mysqli or PDO, both of which have important advantages, such as support for prepared statements. — outis
– outis, Commented Dec 20, 2011 at 19:50
possible duplicate of MySQL data to JSON via PHP, JSON encode MySQL results — outis
– outis, Commented Dec 20, 2011 at 19:51
Can the IDs be negative? By applying a function to the column being ordered by, MySQL won't be able to use indexes. — outis
– outis, Commented Dec 20, 2011 at 19:53

student · Accepted Answer · 2011-11-17 12:01:39Z

1

Have you taken a look at json_encode and json_decode?

http://php.net/manual/en/function.json-encode.php

answered Nov 17, 2011 at 12:01

student

2132 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aliaktas Over a year ago

yes and i tried to use json_encode function, however i could not modify returned array into suitable format for json_encode function in php. do you know the solution?

Collectives™ on Stack Overflow

convert mysql query into json object via php

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related