let's say i have a database containing data like this
id userfrom userto body
1 a b hello
2 b a i am b
3 a b everyone
4 b a this is great
now i want to select 1 of each a and b or you can say i want to show only 1 post of both a and b so what should be the mysql query for this in php and my query goes like this tell me what changes should i have to accept to get the data sorry for writing this much long code
<?php
$connection10=mysqli_connect('localhost','root','123456789','register');
$selectquery="select * from messages where userfrom='$msg' or userto='$msg'";
$resultquery=mysqli_query($connection10,$selectquery);
while($rowstand=mysqli_fetch_array($resultquery))
{
$userfrom1=$rowstand['userfrom'];
$userto1=$rowstand['userto'];
$body=$rowstand['msgbody'];
$date=$rowstand['date'];
$searchquery="select * from register where id='$userto1'";
$searchresult=mysqli_query($connection10,$searchquery);
while($bigrow=mysqli_fetch_array($searchresult))
{
$profileimage=$bigrow['profilepic'];
$myfirstname=$bigrow['firstname'];
$mylastname=$bigrow['lastname'];
}
echo '<a href="viewmessages.php?id='.$userto1.'"><img src="'.$profileimage.'" height="50" width="50"></a>'.$myfirstname.' '.$mylastname.'<br>'.$body.'<hr>';}
?>
You can use the GROUP BY command, like this : http://sqlfiddle.com/#!9/a0057/5/0
select DISTINCT name,id,post from users
or
select DISTINCT name,id,post from users GROUP BY name
You could do this with GROUP BY, which will return 2 results in your example. It might be nice to concatenate the posts belonging to the same name using GROUP_CONCAT:
SELECT name, GROUP_CONCAT(post SEPARATOR ' ')
FROM users
GROUP BY name;
Output:
a | hello everyone
b | i am b this is great