Solved

HELP to GROUP results from MySQL search

Posted on 2011-09-08
8
303 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:trrsrr
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 5

Expert Comment

by:KGNickl
ID: 36506904
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
 
LVL 1

Author Comment

by:trrsrr
ID: 36507139
@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
 
LVL 82

Expert Comment

by:hielo
ID: 36507219
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 10

Expert Comment

by:Derokorian
ID: 36507222
to get the strip you can use pathinfo() as such:
$strip = pathinfo($path,PATHINFO_DIRNAME);

Open in new window

0
 
LVL 24

Expert Comment

by:mankowitz
ID: 36507281
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
 
LVL 82

Expert Comment

by:hielo
ID: 36507288
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
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 36507292
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
 
LVL 1

Author Closing Comment

by:trrsrr
ID: 36514237
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question