[SOLVED] How do I select a resultset as a comma deliminted string

[emehrkay]

Is it possible to write a query and have it return the results as a single comma delimited one?

 

Like:

 

res1, res2,....

 

So if I had

 

SELECT user_name FROM users;

 

The result would be a single result with all of the user names delimited what whatever character.

 

I tried CONCAT and CONCAT_WS, but both methods return multiple results

 

Thanks

[toplay]

You might want to look into SQL group_concat(), but this would be really silly if there's a lot of data. It's best if you do this with PHP instead.

 

Just loop through the results and concatenate each rows results together.

[emehrkay]

I just found group concat and it does what I expect. Howerver, it does not help me with my overall problem

 

I have a subquery in my where clause, but during design of the database layout, I didnt realize that the subquery would return multiple results. So I figured a way around that would be to us IN. But the return does not seem to work with the IN param

 

this returns no results (i tried to wrap the results from the subquery in parens):

 

WHERE
cii.connection_id IN(
(SELECT CONCAT('(', GROUP_CONCAT(c.connection_id ORDER BY c.connection_id SEPARATOR ','), ')') as conn_id FROM
connections c
INNER JOIN
connection_item ci ON c.connection_id = ci.connection_id
INNER JOIN
vote_item vi ON ci.vote_item_id = vi.vote_item_id
WHERE
c.connection_description_id = 1
AND
vi.vote_item_object_id = 23
GROUP BY
vi.vote_item_object_id))

 

this returns one result, the first one

 

WHERE
cii.connection_id IN(
(SELECT GROUP_CONCAT(c.connection_id ORDER BY c.connection_id SEPARATOR ',') as conn_id FROM
connections c
INNER JOIN
connection_item ci ON c.connection_id = ci.connection_id
INNER JOIN
vote_item vi ON ci.vote_item_id = vi.vote_item_id
WHERE
c.connection_description_id = 1
AND
vi.vote_item_object_id = 23
GROUP BY
vi.vote_item_object_id))
AND
vii.vote_item_object_id <> 24

[Barand]

instead of the convoluted

cii.connection_id IN(
(SELECT CONCAT('(', GROUP_CONCAT(c.connection_id ORDER BY c.connection_id SEPARATOR ','), ')') as conn_id FROM
connections c

 

isn't it simpler to just join to get the matching values

 

cii INNER JOIN connections c ON cii.connection_id = c.connection_id

[emehrkay]

Barand that doesnt work because of what I am actually trying to do is a little more complicated. I tried to take a small piece of the big problem and focus on that, but I will post the big problem

 

In my system I have objects with object_ids (everything with an object_id can have a comment) these object_ids are held in the object table. So users have an user_id (unique to the user table) and object_id (unique to the object table/system).

 

Alright so what I am doing now is trying to create a connection between two of these objects and allow users to be able to vote on the connection, but the twist is that they can vote on each end of the connection.

 

A<---CONNECTION--->B

 

As a user I am able to vote on this connection once from A and once from B.

 

To set this up, I created five tables.

 

connection_description - this defines what type of connection

 

connections - creates a unique id for the connection and uses connection_description_id to define connection type

 

vote_item - creates a unique id for an item to be able to be voted on (i did this table so that an item can be voted on multiplie times in different connections)

 

connection_item - connections the connection_id and the vote_item_id (for now there are only two way connection (AtoB) but this table will allow for n way connections)

 

votes - this table connects the connection_item_id with the user_id  and their vote

 

here is a dump of those tables if you want to play around with it (mysql 4.0 compatability)

-- 
-- Table structure for table `connections`
-- 

CREATE TABLE `connections` (
  `connection_id` int(15) unsigned NOT NULL auto_increment,
  `object_id` int(15) unsigned NOT NULL default '0',
  `connection_description_id` int(15) unsigned NOT NULL default '0',
  `creator_id` int(15) unsigned NOT NULL default '0',
  `date_created` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`connection_id`)
) TYPE=MyISAM  AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `connections`
-- 

INSERT INTO `connections` (`connection_id`, `object_id`, `connection_description_id`, `creator_id`, `date_created`, `date_modified`) VALUES 
(1, 150, 1, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(2, 158, 1, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00');

-- --------------------------------------------------------

-- 
-- Table structure for table `connection_description`
-- 

CREATE TABLE `connection_description` (
  `connection_description_id` int(15) unsigned NOT NULL auto_increment,
  `object_id` int(15) unsigned NOT NULL default '0',
  `connection_description_title` varchar(50) NOT NULL,
  `connection_description_text` text NOT NULL,
  `creator_id` int(15) unsigned NOT NULL default '0',
  `date_created` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`connection_description_id`)
) TYPE=MyISAM  AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `connection_description`
-- 

INSERT INTO `connection_description` (`connection_description_id`, `object_id`, `connection_description_title`, `connection_description_text`, `creator_id`, `date_created`, `date_modified`) VALUES 
(1, 136, 'sounds like', 'When making this connection, you are saying that A sounds like B.', 1, '2007-11-11 15:41:49', '2007-11-11 15:41:49');

-- --------------------------------------------------------

-- 
-- Table structure for table `connection_item`
-- 

CREATE TABLE `connection_item` (
  `connection_item_id` int(15) unsigned NOT NULL auto_increment,
  `vote_item_id` int(15) unsigned NOT NULL default '0',
  `connection_id` int(15) unsigned NOT NULL default '0',
  PRIMARY KEY  (`connection_item_id`)
) TYPE=MyISAM  AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `connection_item`
-- 

INSERT INTO `connection_item` (`connection_item_id`, `vote_item_id`, `connection_id`) VALUES 
(1, 1, 1),
(2, 2, 1),
(3, 3, 2),
(4, 4, 2);

-- --------------------------------------------------------

-- 
-- Table structure for table `votes`
-- 

CREATE TABLE `votes` (
  `vote_id` int(15) unsigned NOT NULL auto_increment,
  `object_id` int(15) unsigned NOT NULL default '0',
  `connection_item_id` int(15) unsigned NOT NULL default '0',
  `user_id` int(15) unsigned NOT NULL default '0',
  `vote` float NOT NULL default '0',
  `date_created` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`vote_id`)
) TYPE=MyISAM  AUTO_INCREMENT=10 ;

-- 
-- Dumping data for table `votes`
-- 

INSERT INTO `votes` (`vote_id`, `object_id`, `connection_item_id`, `user_id`, `vote`, `date_created`, `date_modified`) VALUES 
(1, 200, 1, 1, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(2, 201, 1, 2, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(3, 202, 2, 1, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(4, 203, 2, 2, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(5, 205, 1, 3, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(6, 207, 3, 1, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(7, 208, 3, 2, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(8, 209, 3, 3, -1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(9, 210, 4, 2, -1, '0000-00-00 00:00:00', '0000-00-00 00:00:00');

-- --------------------------------------------------------

-- 
-- Table structure for table `vote_item`
-- 

CREATE TABLE `vote_item` (
  `vote_item_id` int(15) unsigned NOT NULL auto_increment,
  `vote_item_object_id` int(15) unsigned NOT NULL default '0',
  PRIMARY KEY  (`vote_item_id`)
) TYPE=MyISAM  AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `vote_item`
-- 

INSERT INTO `vote_item` (`vote_item_id`, `vote_item_object_id`) VALUES 
(1, 23),
(2, 24),
(3, 23),
(4, 26);

 

Alright, if you are still reading, my aim was to make a single query that would return a list of connections that an item has only requiring the object_id, connection_type_id, and user_id

 

So if I wanted to see the connections for object_id 23, it would return this

 

connection_id, object_id, votes, voted

1, 24, 1, 1

2, 26, 3, 0

 

I threw together, and I mean threw, this query that almost works, the problem is that my subquery cii.connection_id = returs more than one result

 

		SELECT cii.connection_id, vii.vote_item_object_id,
			(SELECT SUM(vote) as total FROM
				votes v
			WHERE 
				v.connection_item_id = cii.connection_item_id	
			)
		AS votes,
			(SELECT COUNT(user_id) AS user_count FROM
				votes vv
			WHERE
				vv.connection_item_id = cii.connection_item_id
			AND
				vv.user_id = ". $user_id ."
			)
		AS voted
		FROM
		connection_item cii
		INNER JOIN
		vote_item vii ON cii.vote_item_id = vii.vote_item_id
		WHERE
		cii.connection_id =
			(SELECT c.connection_id FROM
			connections c
			INNER JOIN
			connection_item ci ON c.connection_id = ci.connection_id
			INNER JOIN
			vote_item vi ON ci.vote_item_id = vi.vote_item_id
			WHERE
			c.connection_description_id = ". $connection_description_id ."
			AND
			vi.vote_item_object_id = ". $object_id ."
			GROUP BY
			vi.vote_item_object_id)
		AND
		vii.vote_item_object_id <> ". $object_id ."

 

I know this is pretty much sh*t, but I figured it would work for now and I would go back and refine it. Any idea on how to make a single query do everything that i am asking with these five tables?

 

Longest post I've ever made and will probably ever make.

 

Thanks

[emehrkay]

Solved for now. I just used two separate queries

 

public static function getConnectionsByObjectByType($object_id, $conn_arr, $user_id){
	$db = DB::getInstance();
	$q = "
		SELECT cii.connection_id, cii.connection_item_id, vii.vote_item_object_id, p.person_name, p.person_display_name,
			(SELECT SUM(vote) as total FROM
			votes v
			WHERE
			v.connection_item_id = cii.connection_item_id	
			)
		AS votes,
			(SELECT COUNT(user_id) AS user_count FROM
			votes vv
			WHERE
			vv.connection_item_id = cii.connection_item_id
			AND
			vv.user_id = ". $user_id ."
			)
		AS voted
		FROM
			connection_item cii
		INNER JOIN
			vote_item vii ON cii.vote_item_id = vii.vote_item_id
		INNER JOIN
			person p ON vii.vote_item_object_id = p.object_id
		WHERE
			cii.connection_id IN (". $conn_arr .")
		AND
			vii.vote_item_object_id <> ". $object_id ."
		ORDER BY
			voted DESC
	";
	$r = $db->runQuery($q);
	return $db->createObj($r);
}

public static function getConnectionIdsByObjectByType($object_id, $type_id){
	$db = DB::getInstance();
	$q = "
		SELECT c.connection_id FROM
			connections c
		INNER JOIN
			connection_item ci ON c.connection_id = ci.connection_id
		INNER JOIN
			vote_item vi ON ci.vote_item_id = vi.vote_item_id
		WHERE
			c.connection_description_id = ". $type_id ."
		AND
			vi.vote_item_object_id = ". $object_id ."
	";
	$r = $db->runQuery($q);
	return $db->createObj($r);
}