Having problems with a MySQL query. Only returning the results for the first item in the resulting GROUP_CONCAT array. i.e resulting array is [1,3,4], the quesitionnaires for only user id 1 is returned.
$query_search = "SELECT questionnaires_index.id, questionnaires_index.ea_num, questionnaires_index.address, questionnaires_index.status, questionnaires_index.json_stored, users.username FROM questionnaires_index INNER JOIN users ON users.id = questionnaires_index.interviewer_id WHERE questionnaires_index.interviewer_id IN (SELECT GROUP_CONCAT(id) FROM users WHERE supervisor = (SELECT id FROM users WHERE username = '".$username."'))";
Am I using GROUP_CONCAT incorrectly? Is there a better way of doing this?
EDIT 1 Here are the SQL tables used in this query
CREATE TABLE IF NOT EXISTS `questionnaires_index` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ea_num` int(10) unsigned NOT NULL,
`address` varchar(100) NOT NULL,
`interviewer_id` int(10) unsigned NOT NULL,
`status` varchar(30) NOT NULL DEFAULT 'Available',
`json_stored` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
--
-- Dumping data for table `questionnaires_index`
--
INSERT INTO `questionnaires_index` (`id`, `ea_num`, `address`, `interviewer_id`, `status`, `json_stored`) VALUES
(1, 101, '29 De Havilland Crescent Pro Park, Building 1 Persequor Technopark Pretoria 0020', 1, 'Non Contact', 1),
(2, 102, '5th Floor, Imperial Bank Terraces Carl Cronje Drive Tyger Waterfront Bellville 7530', 1, 'Available', 0),
(3, 101, '29 De Havilland Crescent Pro Park, Building 1 Persequor Technopark Pretoria 0020', 3, 'Partially Completed', 0),
(4, 102, '5th Floor, Imperial Bank Terraces Carl Cronje Drive Tyger Waterfront Bellville 7530', 3, 'Available', 0),
(5, 201, '101 test address', 4, 'Available', 0);
And for users:
CREATE TABLE IF NOT EXISTS `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
`supervisor` int(10) unsigned NOT NULL,
`version_code` varchar(10) NOT NULL,
`is_interviewer` tinyint(1) NOT NULL DEFAULT '0',
`is_supervisor` tinyint(1) NOT NULL DEFAULT '0',
`surname` varchar(50) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`id`, `username`, `password`, `supervisor`, `version_code`, `is_interviewer`, `is_supervisor`, `surname`) VALUES
(1, 'Rynardt', 'q', 2, '2.2.0', 1, 0, ''),
(2, 'Herholdt', 'q', 0, '2.2.0', 0, 1, ''),
(3, 'test', 'test', 2, '2.2.0', 1, 0, ''),
(4, 'Botha', 'q', 2, '', 1, 0, '');
EDIT 2 More info on what I am trying to do:
If you look at the EDIT I made you will see the SQL tables are now included. u.supervisor is op type integer to indicate id of supervisor entry in users table. $username is of type string and indicates the name of the supervisor. I therefore first have to get the id of the supervisor, then get all the id's of users that has a supervisor with the id found previously. Then using this array of users.id get all the questionnaires associated with these users and display it to the supervisor when he logs in.
SELECT GROUP_CONCAT(id) FROM users WHERE supervisor =
(SELECT id FROM users WHERE username = '".$username."')
GROUP BY supervisor
to work with aggregate functions like group_concat, you need to have an aggregated result set ;)
edit...hmm... in this special case
WHERE ... IN (SELECT id FROM ...)
should be sufficent, no need to concatenate the result sets