Solved

HELP to GROUP results from MySQL search

Posted on 2011-09-08
8
307 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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.
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…

623 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