HELP to GROUP results from MySQL search

Hi .. I need help to group search results from MySQL search.
Simplified the case as below:

MySQL Database Sample
###########################################################
('id',  'type',   'keywords',       'path')
###########################################################
('101', 'normal', 'man, work',      'client/Johnny/normal/2011-08-10/photo101.jpg')
('102', 'normal', 'woman, farm',    'client/Johnny/normal/2011-08-10/photo102.jpg')
('103', 'normal', 'man, office',    'client/Johnny/normal/2011-08-10/photo103.jpg')
('104', 'normal', 'woman, work',    'client/Johnny/normal/2011-08-10/photo104.jpg')
('105', 'normal', 'man, police',    'client/Johnny/normal/2011-08-10/photo105.jpg')
('106', 'normal', 'woman, office',  'client/Johnny/normal/2011-08-15/photo106.jpg')
('107', 'normal', 'man, work',      'client/Johnny/normal/2011-08-15/photo107.jpg')
('108', 'normal', 'woman, police',  'client/Johnny/normal/2011-08-15/photo108.jpg')
('109', 'normal', 'man, police',    'client/Johnny/normal/2011-08-15/photo109.jpg')
('110', 'normal', 'woman, teacher', 'client/Johnny/normal/2011-08-15/photo110.jpg')
('111', 'HQ',     'man, office',    'client/Johnny/HD/2011-08-22/photo111.jpg')
('112', 'HQ',     'woman, office',  'client/Johnny/HD/2011-08-22/photo112.jpg')
('113', 'HQ',     'man, work',      'client/Johnny/HD/2011-08-22/photo113.jpg')
('114', 'HQ',     'woman, farm',    'client/Johnny/HD/2011-08-22/photo114.jpg')
('115', 'HQ',     'man, office',    'client/Johnny/HD/2011-08-22/photo115.jpg')
('116', 'HQ',     'woman, work',    'client/Johnny/HD/2011-08-28/photo116.jpg')
('117', 'HQ',     'man, police',    'client/Johnny/HD/2011-08-28/photo117.jpg')
('118', 'HQ',     'woman, teacher', 'client/Johnny/HD/2011-08-28/photo118.jpg')
('119', 'HQ',     'man, farm',      'client/Johnny/HD/2011-08-28/photo119.jpg')
('120', 'HQ',     'woman, police',  'client/Johnny/HD/2011-08-28/photo120.jpg')

And the PHP Codes:
//$search = $_GET['q'];
$search = 'woman';
$client = 'client/Johnny';

$sql   = "SELECT * FROM ".TABLE." WHERE keywords RLIKE '".$search."' AND path RLIKE '".$client."' ORDER BY id ASC";
$query = mysql_query($sql) or die("Invalid query: " . mysql_error());
$found = mysql_num_rows($query);

if ( !empty($found) ) {

    echo 'RESULTS FOUND:';
    while ( $data = mysql_fetch_assoc($query) )
	{ 
	    $img_id = $data['id'];
	    echo $img_id.'<br />';
	}

} else {
    echo 'NO RESULTS FOUND';
}

Open in new window


And if search for woman (as above) the result goes like:
RESULTS FOUND:
102
104
106
108
110
112
114
116
118
120

But what I'm looking for (and not able to find a proper solution) is for the search results to also groups by 'path' (or excerpt from the 'path') .. so it's like :
RESULTS FOUND:
client/Johnny/normal/2011-08-10/
102
104
client/Johnny/normal/2011-08-15/
106
108
110
client/Johnny/HD/2011-08-22/
112
114
client/Johnny/HD/2011-08-28/
116
118
120

Can anybody help me enhance my PHP codes to be able to group the results as explained above .... Thanks all.
LVL 1
trrsrrAsked:
Who is Participating?
 
mankowitzConnect With a Mentor Commented:
I suppose you could use group concat, but your php would have to parse it.

SELECT left(path, length(path)-locate('/',reverse(path))) as pathname, group_concat(id)
FROM tmp WHERE keywords RLIKE 'woman' AND path RLIKE 'client/Johnny'
GROuP BY left(path, length(path)-locate('/',reverse(path)))
ORDER BY id ASC

which produces


client/Johnny/normal/2011-08-10, 104,102
client/Johnny/normal/2011-08-15, 110,108,106
client/Johnny/HD/2011-08-22, 112,114
client/Johnny/HD/2011-08-28, 118,116,120
0
 
KGNicklCommented:
I would put the results in a data structure and loop through the data structure and do as you wish with it rather than just outputting the query results and/or trying to add more/different SQL.

Maybe someone will provide you the code, but I'm too tired after coding all day.
0
 
trrsrrAuthor Commented:
@KGNickl:
Thanks for your comments... actually I was thinking something like that but cant guess it right.
As you see before grouping the results it has to strip the 'path' first .. e.g :
'path' > 'client/Johnny/normal/2011-08-10/photo101.jpg
'strip' > 'client/Johnny/normal/2011-08-10/
any more guides ?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
hieloCommented:
try:
//$search = $_GET['q'];
$search = 'woman';
$client = 'client/Johnny';

$sql   = "SELECT * FROM ".TABLE." WHERE keywords RLIKE '".$search."' AND path RLIKE '".$client."' ORDER BY id ASC";
$query = mysql_query($sql) or die("Invalid query: " . mysql_error());
$found = mysql_num_rows($query);

if ( !empty($found) ) {

    echo 'RESULTS FOUND:';
	$buffer=array();
    while ( $data = mysql_fetch_assoc($query) )
	{ 
		$temp=explode('/',$data['path']);
		array_pop($temp);
		$temp=implode('/',$temp);
		if( !isset($buffer[$temp]) )
		{
			$buffer[$temp]=array();
		}
		$buffer[$temp][]=$data;
	}
	
	foreach($buffer as $k=>$item)
	{
		echo '<h1>',$k,'</h1>';
		foreach( $item as $i=>$data)
		{
			echo $data['id'].'<br />';
		}
	}
} else {
    echo 'NO RESULTS FOUND';
}

Open in new window

0
 
DerokorianCommented:
to get the strip you can use pathinfo() as such:
$strip = pathinfo($path,PATHINFO_DIRNAME);

Open in new window

0
 
mankowitzCommented:
You could do something like this:

SELECT *, left(path, length(path)-locate('/',reverse(path))) as pathname
FROM tmp WHERE keywords RLIKE 'woman' AND path RLIKE 'client/Johnny' ORDER BY id ASC

however, it is not easy to do the formatting that you want

client/Johnny/normal/2011-08-10/
102
104
client/Johnny/normal/2011-08-15/
106
108
110
client/Johnny/HD/2011-08-22/
112
114
client/Johnny/HD/2011-08-28/
116
118
120
0
 
hieloCommented:
you can also try:
$search = 'woman';
$client = 'client/Johnny';

$sql   = "SELECT SUBSTRING_INDEX(`path`, '/', 4) as `g`, GROUP_CONCAT( CAST(`id` AS CHAR) SEPARATOR  '*') as `id`,  GROUP_CONCAT(CAST(`type` AS CHAR)  SEPARATOR  '*') as `type`,   GROUP_CONCAT(CAST(`keywords` AS CHAR)  SEPARATOR  '*') as `keywords`,  GROUP_CONCAT(CAST(`path` AS CHAR)  SEPARATOR  '*') as `path`  FROM ".TABLE." WHERE `keywords` RLIKE '".mysql_real_escape_string($search)."' AND `path` RLIKE '". mysql_real_escape_string($client)."' ORDER BY `id` ASC";

$query = mysql_query($sql) or die("Invalid query: " . mysql_error());
$found = mysql_num_rows($query);

if ( !empty($found) ) {

    echo 'RESULTS FOUND:';
    while ( $data = mysql_fetch_assoc($query) )
	{ 
		echo $data['g'], str_replace('*','<br />',$data['id']),'<br />';
	}

} else {
    echo 'NO RESULTS FOUND';
}

Open in new window

0
 
trrsrrAuthor Commented:
Just by copy-paste and not making major tweaks (that I cant, lol) I found out that @mankowitz codes is the most suitable solution in my case.
Parsing the results is done separately in PHP by my trial & error :-)

Thanks to @mankowitz and also the rest of the programmers who have contribute to this matter...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.