Create array from row objects in the same table :: PHP + SQL

Question

I have an array like so

id | userID | item
1  ! 1      | {"property": 0, "property": "string"}
2  ! 1      | {"property": 4, "property": "string2"}

and I wish to return one array with like so:

[{
    "property": 0, 
    "property": "string"
}, 
{
    "property": 4,
    "property": "string2"
}]

No matter what I try I cannot seem to get it to work properly. Either I get a whole string so number values get converted, or I get an object with \" everywhere. Currently I have this code

if ($query = $mysqli->query("SELECT item FROM userItems WHERE userID = 'id' "))
{
    while ($row = $query->fetch_object())
    {
        $result->items[count($result)] = $row;
    }
}

$test = stripslashes(json_encode($result->items));

which returns this ...

{"1":{"item":"{"property":"0","property":"string"}"}}

Been trying to solve it for hours and can't get it right

David Jirman · Accepted Answer · 2015-09-19 01:03:05Z

1

I tried your code and there're two main things:

you're building an array of associative arrays but associative array tends to overwrite identical associative keys with the latter in your code - in your case both keys are property,
the data you're retrieving from DB (userItems.item) already seems to be JSON encoded so you need to call json_decode() somewhere instead of calling json_encode() again; decoding your $row->item ($result->items[count($result)] = json_decode($row->item);) seems to do the trick.

Just as a sidenote you should consider wrapping your id parameter in SQL query into mysqli_real_escape_string() or so.

Let me know if that helped and you see the desired result now.

answered Sep 19, 2015 at 1:03

David Jirman

1,21612 silver badges25 bronze badges

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

1 Comment

lmenus Over a year ago

Thanks! I will try it tomorrow, too tired for today haha. I do use $mysqli->real_escape_string() but I omitted it to take up less space

Collectives™ on Stack Overflow

Create array from row objects in the same table :: PHP + SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related