CODEWITHSUNDEEP
phpmysqljson
user1904273
user1904273

Reputation: 4764

PHP/MYSQL/JSON: Simplify JSON for API

Hi I am trying to create an API that converts a results array from a database call into json that can be easily parsed.

Using the simple command json_encode, my JSON is a complicated, verbose mess of nested objects and arrays that is proving hard to parse on the other end.

Can anyone suggest a way to pare this down to the key information that should be provided: userid, long comment (lcom) and short comment (shcom) and how to send this as an API?

Thanks in advance for any suggestions

Here is the current JSON output produced from the following query and code:

$sql = "SELECT userid,shcom,lcom FROM comments WHERE userid = 1 LIMIT 4";
$res = mysql_query($sql) or die(mysql_error());  
$comments = array();
while($row = mysql_fetch_array($res)) {
$comments[] = array('row'=>$row);
 } 
echo json_encode(array('comments'=>$comments));

Json output:
{
    "comments": [
        {
            "row": {
                "0": "1",
                "userid": "1",
                "1": "hello",
                "shcom": "hello",
                "2": "hellothere",
                "lcom”: "hellothere"
            }
        },
        {
            "row": {
                "0": "1",
                "userid": "1",
                “1”: ”agreed”,
                "shcom”: ”agreed”,
                “2”: ”agreedforonce”,
                "lcom”: ”agreedforonce”
            }
        },
        {
            "row": {
                "0": "1",
                "userid": "1",
                "1": "gohome",
                "shcom”: ”gohome“,
                “2”: ”gohomenow”,
                "lcom: ”gohomenow”
            }
        },
        {
            "row": {
                "0": "1",
                "userid": "1",
                "1": "getout”,
                "shcom”: ”getout”,
                “2”: ”getoutofhere”,
                "lcom: ”getoutofhere”
            }
        }
    ]
}

Upvotes: 2

Views: 321

Answers (1)

CodeGodie
CodeGodie

Reputation: 12132

You should be using mysqli rather than mysql since mysql is deprecated. Regardless, the problems in your code are happening because of two reasons. One, mysql_fetch_array does not produce the results you are expecting. Two, in your iteration you are not extracting the answers the right way. To resolve, use mysq_fetch_assoc and push only each $row to your final array.

Replace this:

while($row = mysql_fetch_array($res)) {
    $comments[] = array('row'=>$row);
}

to this:

while($row = mysql_fetch_assoc($res)) {
    $comments[] = $row;
}

Upvotes: 2

Related Questions