如何在MySQL和PHP中用户出现一次的用户显示多个结果？

Question

Now I am designing a query to get the user name and what they have posted

here is the fields in table "user" and "post"

user: user_name, u_id

post: p_id, u_id, content

And I want to write a query to display like that

user_name        content

Cherry         hi,i am cherry
               hello
Tom            good day today=)

But not

user_name        content

Cherry         hi,i am cherry
Cherry         hello
Tom            good day today=)

How can I achieve that on PHP in table form?

Answer 1

GROUP_CONCAT should be helpful to you. You can read documentation on it here.

Your query could look like this:

select user_name, group_concat(content,"
") from post group by user_name;

This will group all of the posts together by each user, and then concatenate the content field of all of a user's rows into a single string separated by newline.

You can see the SQLFiddle with query and results here

(Keep in mind that SQLFiddle prints its results, by default, in tabular HTML. Choose Run SQL -> Plaintext Output to see the actual newlines printed out)

EDITED (added content below)

If you want to use PHP to handle this, you can do the following:

$query = 'select user_name, group_concat(content,"
") as contents from post group by user_name;'
$result = mysqli_query($query)

print '<table><tr><td>USER NAME</td><td>CONTENTS</td></tr>';
while ($row = mysqli_fetch_assoc($result)) {
  print '<tr>';
  print '<td>' . $result['user_name'] . '</td>';
  print '<td>' . preg_replace("/
/", '<br>' . $result['contents']) . '</td>';
  print '</tr>';
}
print '</table>';

Here are links to documentation on some of the functions I used in this example:

• mysqli_query
• mysqli_fetch_assoc
• preg_replace