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?
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:
select user.user_name, post.content
from user inner join post on
user.u_id = post.u_id
get the results, and show them at your convenience. If the user_name comes up more than once, just print it once, and go through all its content.
Try this:
SELECT u . * , (
SELECT GROUP_CONCAT( p.content )
FROM post p
WHERE p.u_id = u.u_id
) AS combine
FROM user u
LIMIT 0 , 30
If you want to display it in a table-like form, you could keep a variable which holds the current username or id.
$currentUser = false;
foreach ($rows as $row) {
echo '<tr><td>';
if ($row['user_id'] != $currentUser) {
//print user
$currentUser = $row['user_id'];
}
echo '</td><td>';
//print content
echo '</td></tr>';
}