Concatenate All Rows From One Column With MYSQL

Question

I am trying to do something that I thought would be very simple but it's driving me crazy.

I have the following data:

ID     ---    Name
1      ---    Joe
2      ---    Bob 
3      ---    Jim
4      ---    Mike

I want to be able to show the results of this from MYSQL as:

"Joe", "Bob", "Jim", "Mike"

I tried CONCATENATE tutorials, but they all seem to be for merging like ID's.

$sql = "SELECT names, CONCAT_WS('', 'names') as namelist FROM peoplenames";

$result = $conn->query($sql);

echo $row["namelist"];

if ($result->num_rows > 0) {

    // output data of each row
    while($row = $result->fetch_assoc()) {

        $names = $row["nameslist"];
        echo $names;
    }
}

If I echo outside the loop I only get the most recent result.

Any ideas?

Rob · Answer 1 · 2015-07-21T20:56:25.960

0

Change your query to SELECT names FROM peoplenames and use PHP concat for a string:

$names = "";

while($row = $result->fetch_assoc()) {
  $names .= '"' . $row["names"] . '",';
}

echo $names;

edited Jul 21 '15 at 20:56

answered Jul 21 '15 at 20:26

Rob

1,840
2
12
19

I am trying to take this result and put it into an array. When I use PHP to add the separator I only get one name to show. IE it shows "Joe", and not "Joe", "Bob", "Jim", "Mike" – Ryan Webb Jul 21 '15 at 20:39
Did you try the solution I provided? Note that the loop concats the names and then the names are echoed OUTSIDE of the loop. – Rob Jul 21 '15 at 20:41
Sorry Rob, your solution looks like it should work. This was my fault, it looked like something I had been working on earlier, but your code is more solid. Only problem I have now is getting the quotes to show correctly. IE it shows Joe, Bob instead of "Joe", "Bob" – Ryan Webb Jul 21 '15 at 20:48
Updated my answer. You need to manually concat the string with the quotes. – Rob Jul 21 '15 at 20:56

RiggsFolly · Accepted Answer · 2015-07-21T20:39:17.897

0

The problem is that you are overwriting the contents of $names each time round the loop.

Change your code like this

$sql = "SELECT names FROM peoplenames";

$result = $conn->query($sql);

$names = NULL;

if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {
        $names .= sprintf('"%s",',$row['names']);
    }
    // output amalgamated date
    rtrim($names, ',');
    echo $names;
}

edited Jul 21 '15 at 20:39

answered Jul 21 '15 at 20:28

RiggsFolly

93,638
21
103
149

Yes.. .yet another spellchecker at your service ;-) – Funk Forty Niner Jul 21 '15 at 20:30
Ahhh, my spell checker is in the house – RiggsFolly Jul 21 '15 at 20:30
It's almost a *live-in* version too. I don't do windows though. – Funk Forty Niner Jul 21 '15 at 20:31
^ same here. Glad to be back after 2 weeks vacation... but kinda wish I was still over there lol - hahaha I knew you'd come up with a *nix joke ;-) – Funk Forty Niner Jul 21 '15 at 20:34
This is working to concatenate all the names. However the separator isn't working. So I am seeing JoeBobJimMike instead of "Joe", "Bob", "Jim", "Mike" – Ryan Webb Jul 21 '15 at 20:36
meaning... that I said "I don't do/wash windows" you know what I mean ;-) I was going to edit it after and saying "but I do do Linux"... just not "that" way hehe – Funk Forty Niner Jul 21 '15 at 20:37
@RiggsFolly Nova Scotia, by the sea. – Funk Forty Niner Jul 21 '15 at 20:45
@RiggsFolly You more than likely may have flown right over me lol you need to visit the Maritimes :-) including PEI and Cape Breton. – Funk Forty Niner Jul 21 '15 at 20:48
Is the $names = null; necessary? I removed it and this code still worked. – Ryan Webb Jul 21 '15 at 20:53
Its not strictly necessary, but it is always good practice to initialise variables – RiggsFolly Jul 21 '15 at 20:55
@RiggsFolly I'd call that green ticky, an assist ;-) good show. – Funk Forty Niner Jul 21 '15 at 21:21
1

@Fred-ii- I would have to agree. Now its been closed as a duplicate, se la vie – RiggsFolly Jul 21 '15 at 22:34
@RiggsFolly et oui, c'est la vie mon cher ami. – Funk Forty Niner Jul 21 '15 at 22:35
@Fred-ii- Thought so, you spelt it correctly. Never been colder than the week in winter I spend in Montreal. But I loved the year I spent in Toronto. – RiggsFolly Jul 21 '15 at 22:39
@RiggsFolly Montreal's nice, but the traffic/drivers are murder. I think I felt safer driving in Toronto lol – Funk Forty Niner Jul 21 '15 at 22:41

Concatenate All Rows From One Column With MYSQL

2 Answers2