?
Solved

Mysql group by two columns

Posted on 2014-04-11
1
Medium Priority
?
335 Views
Last Modified: 2014-04-21
Hi,

I have a logs table that contains activities of members using my website. I use the logs table to display recent items for particular items. i do this by storing the a seperate table id and that tables name in the logs table. i then use a while loop on the logs to grab data from the individual tables. Currently i have about seven different tables. To avoid duplication of data when a user updates anything i have grouped by the tables id on the logs sql request. However, i run into a problem with photos. I allow users to upload as many photos as they wish. each photo adds a logs entry with the table name of 'photos' and their associated photos_id which is the same as an 'id' with auto increment on the photos table. However if i group by tables id then each of these individual photos is displayed. i want to eliminate this and just display one item for photos uploaded on the same date. Therefore i was thinking of adding a second group by on a formatted (y-m-D) datetime field in the logs however i quickly realized that would not work as the photos are all going to have a unique id but have the same date. How can i accomplish something along these lines?

The SQL i an using is
SELECT * FROM logs WHERE user='".$_SESSION['username']."' AND ( db_name='blog' OR db_name='bucket_list' OR db_name='destinations' OR db_name='drinks' OR db_name='games' OR db_name='music' OR db_name='news' OR db_name='photos') GROUP BY db_id, DATE_FORMAT(date,'%Y-%m-%d') ORDER BY id DESC LIMIT 20

Open in new window


where
db_name = name of individual tables
db_id = auto increment value assigned to each post in their respected tables
$_SESSION['username'] = name of current user which is also in logs table


thanks in advance!!
0
Comment
Question by:J N
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 39995990
as it's not clear what exactly you have as data, and what exactly you require as output, I will suggest my article for reading:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html

though it's not mainly for MySQL, the concepts might help to clarify what you need to look at
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month15 days, 8 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question