MySQL alternate to group_concat?

Question

Using group_concat apparently has a size limit on it.... so I'm looking for another way to achieve this.

Database structure:

John Doe CA  Col1   Col2   Col3
John Doe CA  Col1   Col2   Col3
John Doe CA  Col1   Col2   Col3
Jane Doe CA  Col1   Col2   Col3
Jane Doe CA  Col1   Col2   Col3
John Doe NY  Col1   Col2   Col3
John Doe NY  Col1   Col2   Col3
Jenny Doe WI  Col1   Col2   Col3
Billy Bob PA  Col1   Col2   Col3
Billy Bob PA  Col1   Col2   Col3
John Doe WA  Col1   Col2   Col3
Peter Paul FL  Col1   Col2   Col3
Peter Paul FL  Col1   Col2   Col3
Peter Paul FL  Col1   Col2   Col3
Peter Paul FL  Col1   Col2   Col3

When doing a search, I need to return anyone matching the search criteria, but need to have the data grouped/organized a certain way.

Using the above table scheme/layout..

If I searched for Name John Doe..

I would need to return BOTH John Doe/CA & John Doe/NY ONLY ONCE! (each)...

but somehow also have all fields/cols (col1, col2, col3...etc) for ALL ROWS associated with that name/state match..

(sorry I'm having a hard time trying to explain it properly I guess)

Using the same search as above, John Doe (for example)

I am expecting something like:

• John Doe / CA
• Col1 Col2 Col3
• Col1 Col2 Col3
• Col1 Col2 Col3
• Col1 Col2 Col3
• Col1 Col2 Col3

*John Doe / NY * Col1 Col2 Col3 * Col1 Col2 Col3

I would need the same type of return for any type of search query

So if I just searched by where state = 'CA';

I would need all CA matches returned like the above:

1.) Single name / State

2.) All columns from all records that meet that criteria

John Doe / CA
 * Col1   Col2   Col3
 * Col1   Col2   Col3
 * Col1   Col2   Col3
 * Col1   Col2   Col3
 * Col1   Col2   Col3

Jane Doe / CA
 * Col1   Col2   Col3
 * Col1   Col2   Col3

Billy Bob / CA
 * Col1   Col2   Col3
 * Col1   Col2   Col3

This attempt:

SELECT *, COUNT(*) as casecount FROM mytable WHERE first LIKE '%John%' AND wstate LIKE '%CA%'group by first,last,state;

Gets me close... I get only 1 'name/state' returned for each 'match' (instead of say all 5 records for John Doe/CA)

Slightly different search terms:

SELECT *, COUNT(*) as casecount FROM mytable WHERE first LIKE '%John%' group by first,last,state;

Again gets me only 1 'name/state' record returned for EACH match (instead of 5 records returned for John Doe/CA and 3 records returned for John Doe/NY)..

This has served me well up until this point.. and I am stuck on how to proceed to get what I need after this.

When I get my response from the database.. I loop through the records/rows.. and display the details in some table (to help with the visual display of the info)...

But I now need to dump -all- the Col1, Col2, Col3..etc data into a cell/area/placeholder below this main name/state data...

Using PHP as an example,, its like I need to loop through my array and then do another sub/nested loop on the other 'records/rows'

Moving on I have tried this: (Group_Concat)

SELECT CONCAT(first, ' ', middle, ' ',last) as fullName, GROUP_CONCAT(COl1, '^', Col2, '^', Col3 SEPARATOR '<br>') FROM myTable GROUP BY CONCAT(first, ' ', middle, ' ',last);

Which does exactly what I want.. (it seems).. well at least gives me the data in one chunk, and I guess I could use some PHP to explode by < br > and then another explode by ^ character...

The problem with this is, that I read there is a size limit on using GROUP_CONCAT? And being there is no telling how many results there are.. I dont want to invest in something that wont cut it.. or error out..etc..

and after some tweaking/merging of the queries.. I have this working query:

SELECT *, COUNT(*) as casecount , GROUP_CONCAT(Col1, '^', Col2, '^', Col3 SEPARATOR '<br>') FROM mytable GROUP BY first, middle, last, state;

What easy to read/understand alternatives do I have to get the same results, but not have to worry about some size limit of GROUP_CONCAT()? (I'm still new to MySQL)