2

When calling the getColumnMeta function with PDO, it returns no native_type field for fields with a JSON data type. Is there any way to make it return a JSON native type? It does return the type for any other data type such as string, int and float

I am running PHP7 with the standard PDO driver and MySQL 5.7

Jesse Schokker
  • 896
  • 7
  • 19
  • 2
    [getColumnMeta](http://php.net/manual/en/pdostatement.getcolumnmeta.php) returns _The PHP native type used to represent the column value._ PHP doesn't have json [native type](http://php.net/manual/en/language.types.intro.php), since json is pretty much just a string. – FirstOne Nov 20 '17 at 13:58
  • @jeroen JSON type is very recent addition to MySQL. [link](https://dev.mysql.com/doc/refman/5.7/en/json.html). But when sent to php it's indeed nothing more than a string. What's your use case? Why do you want to know if it's a string? – Niels Nov 20 '17 at 13:59
  • I am building a database controller which does stuff like converting to the right data type for you. Otherwise I have to repeat using that function every time I want to fetch that specific JSON row. – Jesse Schokker Nov 20 '17 at 14:02
  • Have a look at the information_schema that MySQL has. E.G. `SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'TABLE_NAME'` this returns the datatypes each column has internally. You could make your distinctions based on those values. – Niels Nov 20 '17 at 14:06
  • You could use [json_last_error](http://php.net/manual/en/function.json-last-error.php) after running `json_decode` and if the result is `JSON_ERROR_NONE`, there is a chance it's a (valid) json. I don't how reliable this method is, though. – FirstOne Nov 20 '17 at 14:08
  • @FirstOne That will waste a lot of processing power I'd imagine – Jesse Schokker Nov 20 '17 at 14:09
  • @Niels Thanks I will look in to it. – Jesse Schokker Nov 20 '17 at 14:10
  • 1
    you don't need such a controller, [PDO can already convert the types](https://phpdelusions.net/pdo#returntypes) for you – Your Common Sense Nov 20 '17 at 14:11
  • I wouldn't call it _waste_, but if you're concerned, there is this question with some suggestions: [Fastest way to check if a string is JSON in PHP?](https://stackoverflow.com/q/6041741/4577762) – FirstOne Nov 20 '17 at 14:12
  • @YourCommonSense Yes it does, however I am trying to implement it for data types like booleans and JSON. – Jesse Schokker Nov 20 '17 at 14:12
  • 1
    @FirstOne so if I'll answer on a forum with a single word "true", my reply will be considered JSON? that's a slippery approach, DON't ever try to go that road – Your Common Sense Nov 20 '17 at 14:13
  • I've used the TINYINT type and must have a length of 1. 0 = false and 1 = true – Jesse Schokker Nov 20 '17 at 14:14
  • @YourCommonSense The TINYINT with max length of 1 is what MySQL uses themselves as well to distinguish a boolean. – Jesse Schokker Nov 20 '17 at 14:15
  • well, tinyint could be just a tinyint, with values from 0 to 255. If I hate anything in the web development, it's such smart-alec ill-do-it-all solutions. Because the day you'll need something different it will be easier to rewrite the whole code than add one exception. – Your Common Sense Nov 20 '17 at 14:38

1 Answers1

-2

No, there isn't.

getColumnMeta() is still an experimental feature. The actual spec was never designed and the only implementation available is a result of an experiment. The values you get do not mean much since there is no consensus on what they should mean.

JSON is a new type that was added to MySQL, and it was added after the experiment. The type is not present in the code and if needed someone would have to add it, but the question is what should it actually represent. The data you receive in PHP from JSON column type is coming as a string. PHP doesn't have a native JSON type and represents JSON data as a string. You can decode it to an array or an object, but that is the role of the application.

Dharman
  • 30,962
  • 25
  • 85
  • 135