How to reformat JSON response using values from Array

Question

Essentially I have the following script with the following response:

<?php
  header('Content-Type: application/json');

  $stmt = $pdo->prepare('

    SELECT
        `tablelist`.`id`,
        `tablelist`.`content`
        FROM `tablelist `
    ');
    
    $stmt->execute([
      
      ]);
      
  $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
  $rowcount = $stmt->rowCount();
    
     
    if ($rowcount < 1) {
        $response["error"] = TRUE;
        echo json_encode($response);
    }else{

        echo json_encode($row);
        
        
    }

?>

Current Response:

[
  {
    "id": 1,
    "content": "Reason 1"
  },
  {
    "id": 2,
    "content": "Reason 2"
  },
  {
    "id": 3,
    "content": "Reason 3"
  },
  {
    "id": 4,
    "content": "Reason 4"
  }
]

I would instead like to present this array as the following (no square brackets):

{
  "error": false,
  "content": {
    "1": "Reason 1",
    "2": "Reason 2",
    "3": "Reason 3",
    "4": "Reason 4"
}

I know I need to do the following:

$response["error"] = FALSE;
$response["content"][$row[id]] = $row[content];

But using this method I am not getting any values from the array.

How can present the values from the array the way?

What does that mean, “I am not getting any values from the array”? What do you get? — deceze
– deceze ♦, Commented Oct 4, 2020 at 15:17
I get null because the array values are not being formatted into $response[“content”][$row[id]] = $row[content] — John
– John, Commented Oct 4, 2020 at 15:26
The question is not about JSON but about simple array processing. — axiac
– axiac, Commented Oct 4, 2020 at 16:52

yTko · Accepted Answer · 2020-10-04 15:26:49Z

2

Look at array_column function and Example #2 at https://www.php.net/manual/en/function.array-column.php

You can transform your rows like this but you must be aware of the uniqueness

$content = '[
  {
    "id": 1,
    "content": "Reason 1"
  },
  {
    "id": 2,
    "content": "Reason 2"
  },
  {
    "id": 3,
    "content": "Reason 3"
  },
  {
    "id": 4,
    "content": "Reason 4"
  }
]';

$from = json_decode($content, true);
$to = json_encode(array_column($from, 'content', 'id'), JSON_PRETTY_PRINT);

echo $to;

will result to

{
    "1": "Reason 1",
    "2": "Reason 2",
    "3": "Reason 3",
    "4": "Reason 4"
}

via https://3v4l.org/WL29a

edited Oct 4, 2020 at 15:26

answered Oct 4, 2020 at 15:22

yTko

1757 bronze badges

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

2 Comments

John Over a year ago

Instead of writing out the content I would just use json_encode($row) right? Otherwise I am hardcoding and I can’t do that.

yTko Over a year ago

no, you should do $response['error'] = false; $response['content'] = array_column($row, 'content', 'id'); echo json_encode($response); inside your else block

Dharman · Accepted Answer · 2020-10-04 19:19:06Z

You can do it very easily with PDO using the PDO::FETCH_KEY_PAIR fetch mode. It will use the first column from SQL as a key and the second as the value.

<?php

header('Content-Type: application/json');

$stmt = $pdo->prepare('
    SELECT
        `tablelist`.`id`,
        `tablelist`.`content`
        FROM `tablelist `
');

$stmt->execute(); 

$dataFromDB = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

if (!$dataFromDB) {
    echo json_encode(['error' => true]);
} else {
    echo json_encode(['error' => false, 'content' => $dataFromDB]);
}

This will produce an output similar to this if there were any rows returned from the SELECT:

{
    "error": false,
    "content": {
        "1": "Reason 1",
        "2": "Reason 2",
        "3": "Reason 3",
        "4": "Reason 4"
    }
}

Collectives™ on Stack Overflow

How to reformat JSON response using values from Array

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related