Group By

[SkyRanger]

I am trying to get a group by to work but for some reason it will only display one output.  Not sure what the problem is.

 

mpid  mpyear mpmonth mpday  mptitle
  4       2012      3             2        Text stuff here
  3       2012      3             1        Day 1 stuff here
  2       2012      2             28      Feb stuff here
  1       2011      12           27      First post test here


So what I am trying to do is sort out per year month

ie:

2012
3
Text stuff here
Day 1 stuff here
2
Feb stuff here 
2011
12 
First post test here

 

That is what I eventually want it to look like, but first thing I need to do is get atleast the mptitle to group first.

$query = "SELECT * FROM monsterpost group BY 'mpmonth'"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo $row['mptitle'];
echo "<br />";
}
?>

[Anon-e-mouse]

Morning!

 

The group by statement refers to a column, so surrounding it with apostrophes will make the column act as a value, if that makes sense. This should work:

 

<?php

$sql = "SELECT * FROM monsterpost GROUP BY mpmonth";

?>

 

Notice the group by clause is capitalized and the column name you are grouping is not surrounded by apostrophes. If you wanted you could surround it in backticks (`) but it isn't essential in this situation.

[SkyRanger]

Ok, not sure what is going on.  That isn't working Anon-e-mouse. Still not sure what problem is.  Only shows 2 entries out of 4 and not even the right 2.

[darkfreaks]

$sql = "SELECT * FROM monsterpost GROUP BY ALL mpmonth ";

[PFMaBiSmAd]

GROUP BY consolidates rows having the same value into a single row in the result set. That's not what you want. GROUP BY is used when you want to use aggregate functions (COUNT, SUM, AVE, MIN, MAX,...) on the rows within each group.

 

What you want to do is form a query that gets the rows you want, in the order that you want them. Then you output the information the way you want when you iterate over the rows in your php code. You would 'remember' the year and month values (using variables) and every time either one of them change, you would output new year or month headings/sub-headings, followed by the data. Start by reading the reply at the following link - http://www.phpfreaks.com/forums/index.php?topic=352119.msg1662984#msg1662984  You would add a second if(){} statement and $last_subheading variable for the month (see reply #9 in that same linked to thread.)

[PFMaBiSmAd]

I would also recommend that you use one DATE data type column to hold your mpyear mpmonth mpday value as a date (YYYY-MM-DD.) It will use less storage, queries will execute faster, there will be less overall php code and sql statements, you can directly order/sort by dates, and you can use the few dozen mysql date functions directly in your query statements.

[SkyRanger]

Thank you.  I am looking through the other posts that you sent me the links for.  That looks exactly what I need.  Thanks

[SkyRanger]

Thanks PFMaBiSmAd.  But after staring at the examples you gave.  I am more lost than I was before....lol.... I am a real noob when it comes to complex php code.  Doing the basics like showing everything in the table is about all I can do. Thanks for your help anyhow.  Hopefully over time and allot of practice I will figure it out.

[PFMaBiSmAd]

Assuming you have your dates in DATE data type column named date (and the other column is named title) -

 

<?php
// connect and select database here....

$query = "SELECT YEAR(date) as year, MONTHNAME(date) as month, title FROM monsterposts ORDER BY date DESC"; // query to get the rows you want in the order that you want them, with the year and monthname specifically selected as well
$result = mysql_query($query);
$last_heading = null; // remember the last heading (initialize to null)
while($row = mysql_fetch_assoc($result)){
$new_heading = $row['year']; // get the column in the data that represents the heading
$new_subheading = $row['month']; // get the column in the data that represents the subheading
if($last_heading != $new_heading){
	// heading changed or is the first one
	$last_heading = $new_heading; // remember the new heading
	$last_subheading = null; // (re)initialize the subheading
	// start a new section, output the heading here...
	echo "{$row['year']}<br />";
}
// subheading under each heading
if($last_subheading != $new_subheading){
	// subheading changed or is the first one
	$last_subheading = $new_subheading; // remember the new subheading
	// start a new section, output the subheading here...
	echo "{$row['month']}<br />";
}
// output each piece of data under a heading here...
echo "{$row['title']}<br />";
}

[SkyRanger]

OMG PFMaBiSmAd  That is exactly what I needed.  That works perfectly.  I owe you BIG time.  This would have taken me a month to even get a start on figuring out how to do that.  I have been picking away at it and reading over the other posts since you posted last.  Again thank you.

[SkyRanger]

I know I am probably pushing my luck.  Been playing with the code trying to do different things with it.  Is there a way to separate the month.  If I am being to much of a bother just say figure it out yourself......lol

 

2012

March

entry

entry

 

February

entry

entry

 

Trying to put it in a drop down menu now....but thank you again for you help.

[SkyRanger]

Nevermind.  Figured out the space problem.