mysql select and multiple column sort and group problem

First I would like to say thank you to everyone for contributing to such a helpful site. I am having a problem that I hope some one can help with. I have looked around the net the few examples I had found did not serve my purpose and I have been unable to modify them to work. So again any help is appreciated.

First lets start with the essential parts of my problem. Here is a dump of the test database.

-- Server version: 5.0.51
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

-- 
-- Database: `weblog`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `storage_auctions`
-- 

CREATE TABLE `storage_auctions` (
  `key` int(5) unsigned zerofill NOT NULL auto_increment,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `auctioneer` text NOT NULL,
  `location` text NOT NULL,
  `contact` text NOT NULL,
  `contents` text NOT NULL,
  `pictures` text NOT NULL,
  `info` text NOT NULL,
  PRIMARY KEY  (`key`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=19 ;

-- 
-- Dumping data for table `storage_auctions`
-- 

INSERT INTO `storage_auctions` (`key`, `timestamp`, `auctioneer`, `location`, `contact`, `contents`, `pictures`, `info`) VALUES (00015, '2009-05-30 16:00:00', 'Rex Frost', 'test 1', '', '', '<br />', '<br />');
INSERT INTO `storage_auctions` (`key`, `timestamp`, `auctioneer`, `location`, `contact`, `contents`, `pictures`, `info`) VALUES (00014, '2009-05-30 10:15:00', 'Tammie Lockwood', 'test', '', '', '<br />', '<br />');
INSERT INTO `storage_auctions` (`key`, `timestamp`, `auctioneer`, `location`, `contact`, `contents`, `pictures`, `info`) VALUES (00013, '2009-05-30 10:15:00', 'Rex Frost', 'test', '', '', '<br />', '<br />');
INSERT INTO `storage_auctions` (`key`, `timestamp`, `auctioneer`, `location`, `contact`, `contents`, `pictures`, `info`) VALUES (00016, '2009-05-30 13:00:00', 'Rex Frost', 'test 3', '', '', '<br />', '<br />');
INSERT INTO `storage_auctions` (`key`, `timestamp`, `auctioneer`, `location`, `contact`, `contents`, `pictures`, `info`) VALUES (00017, '2009-05-27 17:00:00', 'Rex Frost', 'hmmmm', '', '', '<br />', '<br />');
INSERT INTO `storage_auctions` (`key`, `timestamp`, `auctioneer`, `location`, `contact`, `contents`, `pictures`, `info`) VALUES (00018, '2009-05-27 16:00:00', 'Tammie Lockwood', 'hmmmm2', '', '', '<br />', '<br />');

Code (markup):

So what I am trying to accomplish with the above is to create a function that I could select data by year and month then group by day then group by auctioneer and then order by time.

When using the group by statment it only shows one instance of the day if there are multiples etc.

I need all the data to be displayed

Here is what I got so far

CREATE TEMPORARY TABLE `tmp_tbl` (
`key` int( 5 ) NOT NULL ,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP ,
`auctioneer` text NOT NULL ,
`location` text NOT NULL ,
PRIMARY KEY ( `key` )
);
INSERT INTO `tmp_tbl` ( `key` , `timestamp` , `auctioneer` , `location` )
SELECT `key` , `timestamp` , `auctioneer` , `location`
FROM `storage_auctions`
WHERE LEFT( timestamp, 7 ) = "2009-05"
ORDER BY `auctioneer`,`timestamp` ASC ;
SELECT * FROM `tmp_tbl` GROUP BY SUBSTR(timestamp,8,-9) ASC;

Code (markup):

Any help would be greatly appreciated

Big thanks in advance

 

This my not be the way some may have chosen but this is the solution I cam up with.

<table class="articles">
		<tr class="header">
		<td>day</td>
		<td>auctioneer</td>
		<td>time</td>
		<td>location</td>
		<td>edit</td>
		<td>del</td>
		</tr>';
for($day = 1;$day < 31;$day ++){

$query  = 'CREATE TEMPORARY TABLE `tmp_tbl` (
`key` int( 5 ) NOT NULL ,
`timestamp` timestamp NOT NULL ,
`auctioneer` text NOT NULL ,
`location` text NOT NULL ,
PRIMARY KEY ( `key` )
)';
$result = mysql_query($query);

$query = 'INSERT INTO `tmp_tbl` (`key`,`timestamp`,`auctioneer`,`location`) SELECT `key`, `timestamp`, `auctioneer`, `location` FROM `storage_auctions` WHERE LEFT( timestamp, 10 ) = "'.date('Y-m-d',strtotime("$year-$month-$day")).'" ORDER BY `auctioneer` , `timestamp`';
$result = mysql_query($query);

$query = 'SELECT * FROM `tmp_tbl`';
$result = mysql_query($query);

if(!$result){
	die(mysql_error());
}

if(mysql_num_rows($result)!=0){
	while ($myrow = mysql_fetch_assoc($result)) {

    $id = $myrow["key"];
		echo "<tr>";
		echo "<td>";
		echo substr($myrow["timestamp"],8,-9);
		echo "</td>";
		echo "<td>";
		echo $myrow["auctioneer"];
		echo "</td>";
		echo "<td>";
		echo substr($myrow["timestamp"],11,-3);
		echo "</td>";
		echo "<td>";
		echo $myrow["location"];
		echo "</td>";
		echo "<td>";
		echo '<a href="index.php?action=edit_auction&key='.$id.'"><img src="images/edit.gif" width="25px" height="15px" border="0px"/></a>';
		echo "</td>";
		echo "<td>";
		echo '<a href="index.php?action=del_auction&month='.$month.'&key='.$id.'" onClick="return confirm(\'Delete Page?\');"><img src="images/del.gif" width="25px" height="15px" border="0px"/></a>';
		echo "</td>";
		echo "</tr>";

	}
}
     mysql_query('DROP TABLE `tmp_tbl`');
	 mysql_free_result($result);
}
echo '</table>';


mysql_close();


Thanks for the help people .......

Code (markup):

 

Please give a short example with some test data for your table and state what you what as a result, using example output