Compare db value and get the value + duplicates

Question

I'm working on a project where the user is submitting multiple dates. The dates are then saved in the db along with the current username. What I'm trying to do next, is to get an overview of all submitted dates and the users who have submitted the current date. BUT my problem is, that I only wan't to show ONE date, and then all the users who have selected this unique date. Like this:

05.03.2012 = Paul
06.03.2012 = Shane, John
07.03.2012 = John, Irene

And NOT like this, as it is at the moment:

05.03.2012 = Paul
06.03.2012 = Shane
06.03.2012 = John
07.03.2012 = John
07.03.2012 = Irene

Are you with me? Because thats the main problem. As for the coding part, I've tried to do like this:

$table_name = $wpdb->prefix . 'my_db';
$my_date_overview = $wpdb->get_results("
SELECT * FROM $table_name 
WHERE thedate > NOW()
ORDER by thedate ASC");

foreach ( $my_date_overview as $the_dates ) {
echo $the_dates->thedate;
echo ',';
echo $the_dates->username;
}

Db-table values: ID, thedate, username.

I've tried to use "SELECT DISTINCT thedate", but then I don't get the usernames. Can I even do this filtering process here, or do I need to make a function?

I've tried to google it but without any luck. I'm out of ideas - and knowledge. Can anyone help me out? Thanks

Your Common Sense · Accepted Answer · 2012-03-05 11:48:31Z

2

SELECT date, group_concat(username) FROM $table_name 
WHERE thedate > NOW()
GROUP BY thedate
ORDER by thedate ASC"

note that group_concat's length is limited by default. for the considerable number of names yopu have to increase the corresponding variable's value

edited Mar 5, 2012 at 11:48

answered Mar 5, 2012 at 11:30

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nicola Peluchetti · Accepted Answer · 2012-03-05 11:36:52Z

-1

You should usa a query like

SELECT
GROUP_CONCAT(DISTINCT username
           ORDER BY username DESC SEPARATOR ',') as users,
thedate FROM $table_name 
WHERE thedate > NOW()
GROUP BY thedate
ORDER by thedate ASC

edited Mar 5, 2012 at 11:36

answered Mar 5, 2012 at 11:29

Nicola Peluchetti

77k32 gold badges151 silver badges197 bronze badges

1 Comment

Skrol29 Over a year ago

CONCAT_WS() does not perform gouping opration

Collectives™ on Stack Overflow

Compare db value and get the value + duplicates

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related