Solved

Mysql group by two columns

Posted on 2014-04-11
1
302 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:M. Jayme Nagy
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

730 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