Solved

HELP to GROUP results from MySQL search

Posted on 2011-09-08
8
306 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

752 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