-1

How can I run one mysql query to output data both 'ungrouped' and also 'grouped by' user name?

I have a query fetching all the data associated with a user's posts. in the while loop I am retrieving the user's name and inputing it into a select/option dropdown. Below that it is actually outputting the individual posts - User Name and Post.

This all working fine with the exception that, if a user has more than one post his/her name is duplicated in the dropdown. And if I use the clause GROUP BY in the query, then it will not display all the posts - just one per user.

I can't run a separate query (or at least i couldn't find a solution) since i have the query setup for pagination, so running a separate query on the dropdown would just display ALL the users, and I just want to display the ones currently displayed on the page.

Is there a way to output data both 'ungrouped' and also 'grouped by' inside the same query?

Here is an example of my desired output. NOTE: The user Michael Smith has 2 posts, but his name only appears in the dropdown once.

<h3>Posts Filter</h3>
<select>
  <option>Michael Smith</option>
  <option>James Hendrickson</option>
  <option>Lucy Michaels</option>
</select>

<br>
<br>
<hr>

<div style="width:45%;height:200px;float:left;border:1px solid #ddd;padding:10px;margin:2.5%;box-sizing:border-box;">
  <h2>Michael Smith</h2>
  <p>Bacon ipsum dolor amet short ribs kevin ribeye meatball filet mignon swine pork loin spare ribs, pork belly cow tenderloin venison...</p>
</div>

<div style="width:45%;height:200px;float:left;border:1px solid #ddd;padding:10px;margin:2.5%;box-sizing:border-box;">
  <h2>James Hendrickson</h2>
  <p>Bacon ipsum dolor amet short ribs kevin ribeye meatball filet mignon swine pork loin spare ribs, pork belly cow tenderloin venison...</p>
</div>

<div style="width:45%;height:200px;float:left;border:1px solid #ddd;padding:10px;margin:2.5%;box-sizing:border-box;">
  <h2>Michael Smith</h2>
  <p>Bacon ipsum dolor amet short ribs kevin ribeye meatball filet mignon swine pork loin spare ribs, pork belly cow tenderloin venison...</p>
</div>

<div style="width:45%;height:200px;float:left;border:1px solid #ddd;padding:10px;margin:2.5%;box-sizing:border-box;">
  <h2>Lucy Michaels</h2>
  <p>Bacon ipsum dolor amet short ribs kevin ribeye meatball filet mignon swine pork loin spare ribs, pork belly cow tenderloin venison...</p>
</div>

<hr>
<br>

<span style="margin: 0 10px 0 0;width:30px;height;30px;line-height:30px;text-align:center;display:block;float:left;color:#ddd;background:#000;">1</span>

<span style="margin: 0 10px 0 0;width:30px;height;30px;line-height:30px;text-align:center;display:block;float:left;color:#000;background:#ddd;">2</span>

<span style="margin: 0 10px 0 0;width:30px;height;30px;line-height:30px;text-align:center;display:block;float:left;color:#000;background:#ddd;">3</span>

<span style="margin: 0 10px 0 0;width:30px;height;30px;line-height:30px;text-align:center;display:block;float:left;color:#000;background:#ddd;">4</span>

<span style="margin: 0 10px 0 0;width:30px;height;30px;line-height:30px;text-align:center;display:block;float:left;color:#000;background:#ddd;">5</span>

<br>
<br>

Thank you in advance.

Serge

Sergio
  • 792
  • 3
  • 10
  • 35
  • 1
    there is no db code in here. – Funk Forty Niner Feb 24 '17 at 14:57
  • 1
    No, you cannot have agrouped and ungrouped result in the same query - it just doesn't work that way. You should be able to run another query based on data from the drop-donw though. – Jay Blanchard Feb 24 '17 at 14:57
  • 1
    Could just get it ungrouped and [group it in PHP](http://stackoverflow.com/questions/12706359/php-array-group) – apokryfos Feb 24 '17 at 14:58
  • this seems a repost of your previous question http://stackoverflow.com/q/42381424/1415724 – Funk Forty Niner Feb 24 '17 at 15:04
  • Fred -ii- similar but different since I have the pagination aspect now that basically chops down the printed results. And I'm also trying to get more used to doing these sort of filterings with mysql queries since I'm not so familiar with them. – Sergio Feb 24 '17 at 15:05
  • you have an answer below then, see that. and again; there's no mysql here so it's anybody's ballgame really – Funk Forty Niner Feb 24 '17 at 15:07

2 Answers2

1

Here's what I'd do.

Run the ungrouped query. Then 'pre-assemble' the values for the dropdown. Something like this (for the purposes of this example, your query results are called $results):

$dropdown = array();
foreach($results as $r)
{
    $dropdown[] = $r['user_name'];
}
$dropdown = array_unique($dropdown);

And then have in your select:

<select>
<?php foreach($dropdown as $value): ?>
    <option><?php echo $value; ?></option>    
<?php endforeach; ?>
</select>

It's not the most elegant solution, but it should work for you.

P. Gearman
  • 1,138
  • 11
  • 14
0

It will be better to use distinct in you query it will give distinct Author name.

Dilraj Singh
  • 951
  • 10
  • 12