Group by and not getting all results

[ebolt007]

Ok, so I am trying to do a query where I group by "Whosendscard" and all the rest of the information then gets pulled in below each group.

I have the following, which is working, but I only get one result per group I need to get all of the results per group. For instance,

if I have Joe, and Frank in the Whosendscard then I have 3 queries for Joe and 1 for frank I should see 4 results, but I am only seeing 1 result for each. How do I get the 3 results to show under Joe and the 1 to show under Frank where the ClientsAnniversary is say in $searchmonth = August like I have below? And I have the Limit set because I am doing 15 results ber page, which also works because if I have 19 queries between Joe, Frank, and Paul and 15 of those queries are Joe and Franks, Joe and Frank show up on the front page and Paul shows up on the 2nd as expected but only 1 query per user shows up. Grrr.

$query_pag_data = "SELECT * FROM  
                          (
                         SELECT * FROM Contacts WHERE ClientsAnniversary LIKE '%".$searchmonth."%' ORDER BY Whosendscard ASC LIMIT $start, $per_page
                          ) as Contacts
                          group by Whosendscard
                        ";

[ebolt007]

Anyone? Did I write it that confusing?

All I need is to get all of the information in each group, and return those, but return the group once.

I want it to display like the following basically.

 

Joe:(with 3 record)

1st record

2nd record

3rd record

 

Frank:(with 2 records)

1st record

2nd record

 

Paul:(with 3record)

1st record

2nd record

3rd record

 

But right now all I am getting is

 

Joe:(with 3 record)

1st record

 

Frank:(with 2 records)

1st record

 

Paul:(with 3record)

1st record

 

So they are grouping and I am writing the Names as titles in the groups, but I can't get the rest of the information from the groups.

[PFMaBiSmAd]

GROUP BY consolidates all the rows having the same GROUP BY value into one single row. That's not what you want.

 

You need your query to retrieve the rows you want in the order that you want them. To output a new name/title/heading when it changes value, you simply detect when the column value changes. See the following post - http://www.phpfreaks.com/forums/index.php?topic=348604.msg1644964#msg1644964

[ebolt007]

Why does it count all the rows even inside the Group tho? My problem is, I have to have the entire thing, even the title inside one while loop, because I can echo the title and put the other info in another inside the main loop, but with the pagination I have, then it'll either count the title, or count the info inside the while loop so my pages aren't correct. For instance, if I have my pages set to display 15 queries, using the LIMIT I have below, if I use

 

$query_pag_data2 = "SELECT DISTINCT Whosendscard FROM Contacts WHERE ClientsAnniversary LIKE '%".$searchmonth."%' ORDER BY Whosendscard ASC LIMIT $start, $per_page";
$result_pag_data2 = mysql_query($query_pag_data2) or die('MySql Error' . mysql_error());
while ($contact_row2 = mysql_fetch_array($result_pag_data2)) {
$Whosendscard = $contact_row2['Whosendscard'];
$query_pag_data = "SELECT * FROM Contacts WHERE Whosendscard ='$Whosendscard' AND ClientsAnniversary LIKE '%".$searchmonth."%' ORDER BY Whosendscard ASC";
$result_pag_data = mysql_query($query_pag_data) or die('MySql Error' . mysql_error());
while ($contact_row = mysql_fetch_array($result_pag_data)) {
//Blah code
}
}

Then it'll need to display 15 Whosendscard before it shows another page, and at the same time, if one WhosendsCard has 10 queries and the next has 20, then all of those will show on the same page because it's only counting the "title" and if I put the LIMIT in the second SQL query, if the first query has 20 queries, and the 2nd has 10 queries, it counts these separately, so it will break up the first one and not do 15 of the next one on another page correctly.

 

 

[ebolt007]

Nevermind, I got it working using the link you posted. THANKS! Still thought a group could be broken up to simplify it further.