Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Mysql group by two columns

Posted on 2014-04-11
1
Medium Priority
?
331 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Suggested Courses

609 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