PHP Database Results > Array > JSON (without deprecated mysql_fetch_assoc)

Question

I've been searching everywhere for a definitive answer to what seems to be a really simple task - unfortunately all the solutions I can find (and there are a lot) use mysql_fetch_assoc which is deprecated.

All I'm trying to do is to take a two column set of results from a database and echo in JSON format - I've managed everything fine except for one bit - I can't work how to get the values in to a two dimensional array (two column array) using array_push. I just end up with my two column values merged in to one array. Here's a stripped version of what I've done:

header('Content-Type: application/json');
$mostPopularStm = $sparklyGenericPdoObj->prepare("SELECT views, thing FROM X");
$mostPopularStm->execute();
$mostPopularRS = $mostPopularStm->fetchAll();
echo '{data:';
$mostPopularJson = array();
foreach ($mostPopularRS as $mostPopularItem)
{
array_push($mostPopularJson, $mostPopularItem["views"], $mostPopularItem["thing"]);
}
echo json_encode($mostPopularJson);
echo '}';

This is producing output like this:

{data:["Monkeyface","43","Giblets","25","Svelte","22","Biriani","21","Mandibles","20"]}

But what I need is this:

{data:["Monkeyface":"43","Giblets":"25","Svelte":"22","Biriani":"21","Mandibles":"20"]}

I know I can create something manually to do this, using json_encode to format the string on each loop but it seems inefficient.

Any pointers would be hugely appreciated!

Answer 1

Your current array is like

array(0 => 'Monkeyface', 1 => 43, /* ... */);

but you need like

array('Monkeyface' => 43, /* ... */);

Replace

array_push($mostPopularJson, $mostPopularItem["views"], $mostPopularItem["thing"])

By

$mostPopularJson[$mostPopularItem["thing"]] = $mostPopularItem["views"];

And

echo '{data:';
echo json_encode($mostPopularJson)
echo '}';

Better to use:

echo json_encode(array('data' => $mostPopularJson));

As kingkero said, you will never get your expected result because it is invalid:

{data:["Monkeyface":"43" ...

Correct:

{ "data": { "Monkeyface": "43" ...

Answer 2

Compose your array like so:

$mostPopularJson [$mostPopularItem["thing"]] = $mostPopularItem["views"];