Solved

Mysql group by two columns

Posted on 2014-04-11
1
292 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 142

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Animated .jpg? 13 62
Mysql sync between 3-4 mysql db 4 25
form button worked now it doesnt anymore 9 42
Update from TABLE-A to TABLE-B 5 39
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now