Search query, return results by fields

[The_Terminator]

Got a search query wich is working

$q_searchdvd = "SELECT a.d_id, a.d_title, a.d_director, a.d_year FROM dvd a, genres b WHERE a.d_genreid=b.g_id AND (a.d_title LIKE '%" . $_POST['fld_search'] . "%' OR a.d_director LIKE '%" . $_POST['fld_search'] . "%' OR a.d_year LIKE '%" . $_POST['fld_search'] . "%' OR b.g_name LIKE '%" . $_POST['fld_search'] . "%') ORDER by d_title ASC";

Results are getting back nicely order on Title
Eg :

- Commando (movie)
- Rambo (movie)
- Rob Bowman  (director)
- The bomb (movie)

However is it possible to give the results in groups ?
Eg :

Titles
- Commando (movie)
- Rambo (movie)
- The bomb (movie)

Director
- Rob Bowman  (director)

Year
- no results found...

Or even better

Titles
- Commando (movie)
- Rambo (movie)
- The bomb (movie)

Director
- Rob Bowman  (director)

Please advise 🙂

[bogu]

Something like this, is not tested but u should get the point:

SELECT 
	CONCAT("Titles:\r\n", GROUP_CONCAT(DISTINCT CONCAT(' - ' ,a.d_title) SEPARATOR "\r\n")) as `titles`,
	CONCAT("Directors:\r\n", GROUP_CONCAT(DISTINCT CONCAT(' - ' ,a.d_director) SEPARATOR "\r\n")) as `directors`,
	CONCAT("Years:\r\n", GROUP_CONCAT(DISTINCT CONCAT(' - ' ,a.d_year) SEPARATOR "\r\n")) as `years`
FROM 
	dvd a, genres b 
WHERE 
	a.d_genreid=b.g_id AND 
	(
		a.d_title LIKE '%" . $_POST['fld_search'] . "%' OR 
		a.d_director LIKE '%" . $_POST['fld_search'] . "%' OR 
		a.d_year LIKE '%" . $_POST['fld_search'] . "%' OR 
		b.g_name LIKE '%" . $_POST['fld_search'] . "%'
	) 
ORDER by d_title ASC"; 

GROUP BY (Aggregate) Functions
String Functions >> CONCAT