-1

I would like to combine two fields in a MYSQL database into one for output in JSON using PHP. The query draws from two tables:

The first table looks like

comments
id|userid|comment
1|1|Agree
1|2|Disagree

The second table of users looks like:

users
id|username
1|Bob
2|Joe

Here is my current code (stripped down):

$sql = "SELECT u.id,u.username,c.comment FROM comments `c` ";
$sql.= "LEFT JOIN users `u` ON c.userid = u.id 
$res = mysql_query($sql) or die(mysql_error());
//Note there is protection against sql injection and the actual parameterized query is way more complicated: I am simplifying here  

while($row = mysql_fetch_assoc($res)) {
    $comments[] = $row;
}
echo json_encode(array('comments'=>$comments));

It returns JSON that looks like:

"comments":[{"id":"1","username":"Joe","comment":"Agree"},{"id":"2","username":"Bob","comment":"Disagree"}]

I would like to create combine the id and name of the user into one field to get something like:

"comments: [{"user": "1,Joe","comment":"Agree"},{"user":"2,Bob","comment":"Disagree"}]

Can I use concat? Would appreciate any suggestions on how to do this.

zztop
  • 701
  • 1
  • 7
  • 20
  • Well, instead of directly assigning the entire row `$comments[] = $row`, build a new array with the combined values from the row as you desire and then encode that one? Seems like a very straightforward task. – El_Vanja Apr 04 '20 at 01:26
  • 1
    `concat(u.id, ',', u.username) as combo`. Additionally `actual parameterized query` is false, `mysql_` doesn't support parameterized queries. – user3783243 Apr 04 '20 at 01:28
  • 1
    Does this answer your question? [Why shouldn't I use mysql\_\* functions in PHP?](https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) – Dharman Apr 04 '20 at 11:12
  • Dharman, did you actually read the question and the part about parameterized queries? That link has nothing to do with the question. Nick and user3783243 provided good answers. – zztop Apr 04 '20 at 13:42
  • While @Dharman's link doesn't answer your direct question, following his advice will save you potential big trouble down the road, and you might want to read the link in detail to see why. – Hovercraft Full Of Eels Apr 04 '20 at 14:05
  • You don't really understand the issue yet, but if you advance further into PHP programming likely you will. Hopefully this will be in the not too distant future – Hovercraft Full Of Eels Apr 05 '20 at 02:32

1 Answers1

3

Just return the data from the query in the form you want:

$sql = "SELECT CONCAT_WS(',', u.id, u.username) AS user,  c.comment FROM comments `c` ";
$sql.= "LEFT JOIN users `u` ON c.userid = u.id";

You can also use CONCAT(u.id, ',', u.username) instead of CONCAT_WS; the only difference is that CONCAT_WS ignores NULL values, where CONCAT will return NULL if any value is NULL. The difference can be seen in this demo.

Nick
  • 138,499
  • 22
  • 57
  • 95
  • is there a difference between CONCAT_WS and CONCAT? – zztop Apr 04 '20 at 01:29
  • @zztop `CONCAT_WS` will ignore `NULL` values. In this case it probably won't a difference compared to `CONCAT(u.id, ',', u.username)` but in others it may. – Nick Apr 04 '20 at 01:30