Creating a json array using concat with MySql

Question

I'm creating a json array from MySql data using concat like this:

$id = '5705';
$sql = 'select concat("{""type:""colName"",""id"":""$id""}") as myJson from table where etc.;

$stmt = $conn->prepare($sql);

What's happening is, instead of getting data from colName from the table and the value of $id, I'm getting the result as it is in $sql. How do I break out of it and get colName and $id's value?

Current Result

{""type:""colName"",""id"":""$id""}

Desired Result

{""type:""novice"",""id"":""5705""}
//Here novice is data from colName, and 5705 is the value of $id

Shouldn't the desired result be: {"type":"novice","id":"5705"}? — Vincent Ramdhanie
– Vincent Ramdhanie, Commented Jun 8, 2013 at 14:05

Danack · Accepted Answer · 2013-06-08 14:11:00Z

3

Please DON'T DO THAT. Trying to format data into JSON in your SQL will be fragile as encoding things into JSON is subtly more tricky that you would expect and you will inevitably get it wrong.

You should use the json_encode function in PHP. It will work reliably whereas your code will almost certainly break.

$dataArray = array();

while($statement->fetch()){
    $data = array();
    $data['type'] = $typeColumn;
    $data['id'] = $id;

    $dataArray[] = $data;
}

json_encode($dataArray, JSON_HEX_QUOT);

Also, formatting data to send to a client really shouldn't be part of an SQL query.

answered Jun 8, 2013 at 14:11

Danack

25.8k16 gold badges93 silver badges123 bronze badges

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

3 Comments

Norman Over a year ago

This is the way we currently do it.

Danack Over a year ago

Then why are you moving it into the SQL ?

nl-x Over a year ago

Ha! We group_concat complete xml structures directly from mysql. Don't let him scare you...

Fabio · Accepted Answer · 2013-06-08 14:04:12Z

1

You need a better concatenation either in query and php

'select concat("{""type:"",colName,"",""id"":""'.$id.'""}")

Despite it is not really needed you could surround column name with backticks `

answered Jun 8, 2013 at 14:04

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

3 Comments

Norman Over a year ago

Ok. So the $id part works, but it's still the same with colName. I still get {"type:",colName,","id":"5705"}

Fabio Over a year ago

@Norman try this "select concat('{\"\"type:\"\",colName,\"\",\"\"id\"\":\"\"".$id."\"\"}') form where "

Norman Over a year ago

Ok. I got it to work. You need to do it like this: 'select concat("{""type:",colName,",""id"":""'.$id.'""}") -> remove the " before and after colName.

Filippos Karapetis · Accepted Answer · 2013-06-08 14:03:18Z

0

Your variables inside your string are not substituted with their values, as you got single quotes. Double quoted strings will expand variables with their values

Thus, you could invert your quotes, like this, in order to get the actual values of your variables:

$sql = "select concat('...')"

answered Jun 8, 2013 at 14:03

Filippos Karapetis

5,20423 silver badges39 bronze badges

Collectives™ on Stack Overflow

Creating a json array using concat with MySql

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related