[SOLVED] grouping results from a table

[bluebyyou]

I am trying to group my classified results by date...So my first idea was to use mysql GROUP BY.

I have been trying various ways using GROUP BY in my mysql query to achieve this, but I dont think that im going about it the right way.

I only seem to be getting the first result from each group.

 

So I think that its not a mysql solution to get this, but a PHP one. Tell me if im wrong.

 

Any suggestions on how to achieve this?

 

Heres what I have..

http://rockypages.com/classified_type.php?type=1

 

Heres what I want.

http://chicago.craigslist.org/gms/

[mmarif4u]

try order by clause.

[bluebyyou]

Well yeah that works, however I want to be able to create the header for each date dynamically as well....

 

so like this..

 

DATE

  result1

  result2

  result3

Date 2

  result4

  result5

  result6

 

and so on..

 

 

[mmarif4u]

If u have stored date in db then use select date command to retrieve it.

[bluebyyou]

I definately dont see a problem doing that, the problem I have I then getting the group of results under that date header. I dont want to run to many mysql queries, I know there has to be a simple solution, but I cant seem to wrap my head around it.

[Jessica]

before you start:

$date = '';

then in the loop get the date.

$newDate = (date from db);

if($newDate != $date){

  print $newDate;

  $date = $newDate;

}

[bluebyyou]

I don't really get it still, im sorry. Or perhaps im jsut not able to explain my self well.

Im already getting all of the results from my DB...and also paginating them. Im just having a really hard time wrapping my head around this thing.

 

Heres my whole thing..

result here http://rockypages.com/classified_type.php?type=1

 

<?php include("includes.php"); ?>
<?php include("header.php"); ?>
<?php


// If current page number, use it
// if not, set one.
if(!isset($_GET['page']))
{
    $page = 1;
} 
else 
{
    $page = $_GET['page'];
}

// if displaying main category items
if (isset($_GET['type']))
{
$gettype = mysql_escape_string($_GET['type']);
$query = "SELECT * FROM PostingType WHERE `key` = '$gettype'";
dbcon($query);
//checks for mysql injection for category
if (mysql_num_rows($result) > 0)
{
	$row = mysql_fetch_array($result);
	extract($row);
	$breadcrumb = "<a href='classified.php'>Classified</a> > $row[name]";
	$getquery = "type=$gettype"; // sets what will be displayed on the page

}
else
{
	echo "You tried some funny business!";
	exit();
}
}
//if displaying subcategory items
if (isset($_GET['subtype']))
{
   $getsubtype = mysql_escape_string($_GET['subtype']);
   	$query = "SELECT * FROM PostingSubType WHERE psubtype = '$getsubtype'";
dbcon($query);
//checks for mysql injection for subcategory
if (mysql_num_rows($result) > 0)
{
	$row = mysql_fetch_array($result);
	extract($row);
	$ptype = posting_type($row['ptype']);
	$psubtype = $row['postingsubtype'];
	$breadcrumb = "<a href='classified.php'>Classified</a> > <a href='classified_type.php?type=$row[ptype]'>$ptype</a> > $psubtype ";
	$getquery = "subtype=$getsubtype"; // sets what will be displayed on the page

}
	else
{
	echo "You tried some funny business!";
	exit();
}
}

// Number of results per pages
$max_results = 10;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results); 

// Perform MySQL query on only the current page number's results

// checks if there is actually a query to do
if (isset($getquery))
{
$query2 = "SELECT * FROM Posting WHERE $getquery LIMIT $from, $max_results";
dbcon2($query2);

if (mysql_num_rows($result2) > 0)
{ ?>
	<div> <?php
	echo $breadcrumb;
	echo "<br /><br />";
	while($row2 = mysql_fetch_array($result2))
	{
	    // Query results 
		extract($row2);
    		echo $postdate; ?> <a href="post.php?id=<?php echo $number; ?>"><?php echo $title; ?>
		<?php if ($price != ""){ ?> - $ <?php echo $price; } ?></a> 
		<?php if (isset($_GET['type'])){ ?><i> <?php posting_subtype($subtype); echo $posting_subtype ?></i> <?php } ?><br>
		<?php 
	}
	echo "<br />";

	// Figure out the total number of results in DB:
	$query = "SELECT COUNT(*) as Num FROM Posting WHERE $getquery";
	dbcon($query);

	$total_results = mysql_result($result,0); // <!!! is this really how to get the total_results?
	echo "<br />";


	// Figure out the total number of pages. Always round up using ceil()

	$total_pages = ceil($total_results / $max_results);

	// Build Page Number Hyperlinks

	echo "Result Page: ";
	// Build Previous Link
	if($page > 1)
	{

	    $prev = ($page - 1);
	    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev&$getquery\">< Previous</a> ";

	}
		for($i = 1; $i <= $total_pages; $i++)
		{
		    if($page == $i)
			{
    			    echo "<strong>$i</strong> ";
    			} 
			else 
			{
    			     echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i&$getquery\">$i</a> ";
    			}
		}

		// Build Next Link
		if($page < $total_pages)
		{
		    $next = ($page + 1);
		    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next&$getquery\">Next ></a>";
		} 
	?>
	</div><?php
}
else
{
echo "There's nothing here yet. Be the first to post here!";
}
}
else
{
echo "Nothing Selected";
}
?>
</body>
</html>

[Jessica]

There is a lot of code there which doesn't seem relevant. Where did you incorporate what I posted?

[bluebyyou]

I didn't because I dont get how it would work for what I want. It seemed relevant to me because it all works together and I thought it would be helpful to see what im doing.

[emehrkay]

in your query add

 

ORDER BY your_Date_field DESC

 

in your display loop do

 

if($row[$i]->date == $row[$i-1]->date && $i != 0){

//disply row

}else{

//display next header then the next iteration in the loop will take care of the display of the info

}

[bluebyyou]

Am I putting this in right?

 

<?php
while($row2 = mysql_fetch_array($result2))
	{

		extract($row2);
		if($row2[$i]->$postdate == $row2[$i-1]->$postdate && $i != 0)
		{
		//disply row
		echo $postdate;
		}
		else
		{
		//display next header then the next iteration in the loop will take care of the display of the info

                             // I dont understand this..
		}

	    // Query results    		
                         echo $postdate; ?> <a href="post.php?id=<?php echo $number; ?>"><?php echo $title; ?>
		<?php if ($price != ""){ ?> - $ <?php echo $price; } ?></a> 
		<?php if (isset($_GET['type'])){ ?><i> <?php posting_subtype($subtype); echo $posting_subtype ?></i> <?php } ?><br>
		<?php 
	}
?>

[bluebyyou]

So ive been looking at it... I understand what you are saying now.... I want to display the date...then on the next iteration i want to check if that post date is the same...if it is the same, dont display it again...if it is not the same, display the new one...

 

I jsut dont get this part of what you posted.. $row[$i]->date == $row[$i-1]->date && $i != 0

[emehrkay]

no. in order to use my method you need a complete array to reference not just one row.

 

let me look at how i gropu the data before i use it at some of my older code

[bluebyyou]

I got it to work, maybe not necesarily how you were thinking, but you inspired my solution. Thank you for your help.

 

Heres what I did.

<?php
while($row2 = mysql_fetch_array($result2))
	{

		extract($row2);
		if($date != $postdate)
		{
		//disply date
		echo "<br /><strong>$postdate</strong><br />";
		$date = $postdate;
		}


	    // Query results 

		/*echo $postdate; */ ?> <a href="post.php?id=<?php echo $number; ?>"><?php echo $title; ?>
		<?php if ($price != ""){ ?> - $ <?php echo $price; } ?></a> 
		<?php if (isset($_GET['type'])){ ?><i> <?php posting_subtype($subtype); echo $posting_subtype ?></i> <?php } ?><br>
		<?php 
	}
?>

 

Heres the result..

 

http://rockypages.com/classified_type.php?page=1&type=1

[emehrkay]

look at this example

<?php

$x = array(array('datee' => 'today', 'x' => '2'), array('datee' => 'today', 'x' => '3'), array('datee' => 'tomorrow', 'x' => '4'));

for($i = 0;  $i < count($x); $i++){
if($i == 0){
	echo $x[$i]['datee']."<br />";
	echo $x[$i]['x']."<br />";
}elseif($x[$i]['datee'] == $x[$i-1]['datee']){
	echo $x[$i]['x']."<br />";
}else{
	echo $x[$i]['datee']."<br />";
	echo $x[$i]['x']."<br />";
}
}
?>