aebstract Posted February 1, 2010 Share Posted February 1, 2010 $query = mysql_query("SELECT * FROM calendar WHERE MONTH(caldate) = '$calmonth2' && YEAR(caldate) = '$calyear' ORDER BY DAY(caldate) LIMIT 1") or DIE(mysql_error()); I have 4 rows, they have a field called event_tag. I want to grab 1 result from each unique event_tag. My query is obviously wrong, but I'm hoping I'm close and not much needs to be changed. Thanks Quote Link to comment https://forums.phpfreaks.com/topic/190590-grab-unique-rows/ Share on other sites More sharing options...
Hybride Posted February 1, 2010 Share Posted February 1, 2010 $query = mysql_query("SELECT DISTINCT(event_tag) FROM calendar WHERE MONTH(caldate) = '$calmonth2' && YEAR(caldate) = '$calyear' ORDER BY DAY(caldate) LIMIT 1") or DIE(mysql_error()); ought to do it. Quote Link to comment https://forums.phpfreaks.com/topic/190590-grab-unique-rows/#findComment-1005235 Share on other sites More sharing options...
kickstart Posted February 2, 2010 Share Posted February 2, 2010 I have 4 rows, they have a field called event_tag. I want to grab 1 result from each unique event_tag. My query is obviously wrong, but I'm hoping I'm close and not much needs to be changed. Thanks How do you decide which of the 4 rows for an event tag that you want to bring back. If you just want a list of the event tags then the code Hybride gives should do fine. However if you want the (say) latest row of the 4 for an event tag then something different is required (and could do with the table layout to help there). All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/190590-grab-unique-rows/#findComment-1005400 Share on other sites More sharing options...
aebstract Posted February 2, 2010 Author Share Posted February 2, 2010 I want all the information from the row, not just the information from that column. If I have several entries, I don't care which one it grabs, just only grab 1. Quote Link to comment https://forums.phpfreaks.com/topic/190590-grab-unique-rows/#findComment-1005470 Share on other sites More sharing options...
kickstart Posted February 2, 2010 Share Posted February 2, 2010 Hi Cheap and nasty way to do it $query = mysql_query("SELECT * FROM calendar WHERE MONTH(caldate) = '$calmonth2' && YEAR(caldate) = '$calyear' ORDER BY DAY(caldate) GROUP BY event_tag LIMIT 1") or DIE(mysql_error()); Won't work in most flavours of SQL but MySQL seems to manage it. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/190590-grab-unique-rows/#findComment-1005495 Share on other sites More sharing options...
aebstract Posted February 2, 2010 Author Share Posted February 2, 2010 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY event_tag LIMIT 1' at line 1 Quote Link to comment https://forums.phpfreaks.com/topic/190590-grab-unique-rows/#findComment-1005501 Share on other sites More sharing options...
kickstart Posted February 2, 2010 Share Posted February 2, 2010 Hi :'( . Made a mistake. The ORDER BY clause has to go after the GROUP BY clause $query = mysql_query("SELECT * FROM calendar WHERE MONTH(caldate) = '$calmonth2' && YEAR(caldate) = '$calyear' GROUP BY event_tag ORDER BY DAY(caldate) LIMIT 1") or DIE(mysql_error()); Although I suspect you don't want the LIMIT 1 there (which was in your original SQL. $query = mysql_query("SELECT * FROM calendar WHERE MONTH(caldate) = '$calmonth2' && YEAR(caldate) = '$calyear' GROUP BY event_tag ORDER BY DAY(caldate)") or DIE(mysql_error()); All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/190590-grab-unique-rows/#findComment-1005510 Share on other sites More sharing options...
aebstract Posted February 2, 2010 Author Share Posted February 2, 2010 Okay yeah that worked. How come the LIMIT 1 is removed? Quote Link to comment https://forums.phpfreaks.com/topic/190590-grab-unique-rows/#findComment-1005522 Share on other sites More sharing options...
kickstart Posted February 2, 2010 Share Posted February 2, 2010 Hi LIMIT 1 would bring back 1 row total. So if there were 100 unique event_tags you would only get the first one returned. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/190590-grab-unique-rows/#findComment-1005600 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.