Issue turning pdo column value to string

Essentially I am running the following query using pdo and formatting the results as JSON. The issue is I need column2 to always show as a string in the JSON, even though the value is a number, no other column value needs to be reformatted so I attempted to do a for loop to convert using strvalue.

$stmt = $pdoConnect->prepare('

    SELECT column1, column2, column3 from table1 where column1 = "tree"
');

$stmt->execute([]);


$row = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($row as $rowvalue) {
    strval($rowvalue["column2"]);
}
    
echo json_encode($row);
?>

CURRENT JSON RESPONSE:

[
  {
    "column1": "tree",
    "column2": 29012,
    "column3": "foggy"
  },
  {
    "column1": "tree",
    "column2": 00930239,
    "column3": "sunny"
  },
   {
    "column1": "tree",
    "column2": 203943,
    "column3": "rainy"
   }
  ]

IDEAL JSON RESPONSE:

[
  {
    "column1": "tree",
    "column2": "29012",
    "column3": "foggy"
  },
  {
    "column1": "tree",
    "column2": "00930239",
    "column3": "sunny"
  },
   {
    "column1": "tree",
    "column2": "203943",
    "column3": "rainy"
   }
  ]

CodePudding user response：

Yes, you are close, but what you are doing at the foreach loop is working with a copy of $rowvalue, which doesn't change anything as expected to the original value.

I'm not sure how you are storing the 00930239 with the leading zero's without being a string? ... as that will typically save as 930239 assuming an INT field type at the DB level.

Anyway, as an example of the first and last JSON values, change:

foreach ($row as $rowvalue) {
    strval($rowvalue["column2"]);
}

...to:

foreach ($row as &$rowvalue) { // use & as reference with $rowvalue to save the modification
    $rowvalue['column2'] = strval($rowvalue['column2']);
}

...which will give:

[
    {
        "column1": "tree",
        "column2": "29012",
        "column3": "foggy"
    },
    {
        "column1": "tree",
        "column2": "203943",
        "column3": "rainy"
    }
]

Example php fiddle.

CodePudding user response：

You can try this, ATTR_STRINGIFY_FETCHES

$pdoConnect->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true);

ref: https://www.php.net/manual/en/pdo.setattribute.php

phpfiddle