Solved

HELP to GROUP results from MySQL search

Posted on 2011-09-08
8
305 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

685 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