I have the following table schema in MySQL 4.1.18 which I didn't
create, but have to work with.
CREATE TABLE `phplog_userinput` (
`id` int(11) NT NULL auto_increment,
`inputfieldid` int(11) NT NULL default '0',
`userid` int(11) NT NULL default '0',
`value` varchar(150) NT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
A typical set of data looks like this:
id | inputfieldid | userid | value
1 1 2 John
2 2 2 Smith
3 3 2 name (AT) example (DOT) com
I am trying to come up with a query to return all the `values` of a
single userid in a single row. I've checked my books, the manual and
tried every type of join I can think of without success. I'd
appreciate some direction.
Thanks.
Albert Padley

Wednesday 14 June 2006 10:55 am, Albert Padley wrote:
A typical set of data looks like this:

id | inputfieldid | userid | value
1 1 2 John
2 2 2 Smith
3 3 2 name (AT) example (DOT) com

I am trying to come up with a query to return all the `values` of a
single userid in a single row. I've checked my books, the manual and
tried every type of join I can think of without success. I'd
appreciate some direction.

This sounds like somewhat of a strange requirement. Why do they need to be in
a single row? There MIGHT be a way to do it with stored procedures, I'm just
not sure how

Thanks.

Albert Padley

Albert, MySQL's GRUP_CNCAT function might work for you:

In your case something like this:
SELECT userid, GRUP_CNCAT(value)
GRUP BY userid

HTH,
Dan

Albert Padley wrote:
I have the following table schema in MySQL 4.1.18 which I didn't create,
but have to work with.

CREATE TABLE `phplog_userinput` (
`id` int(11) NT NULL auto_increment,
`inputfieldid` int(11) NT NULL default '0',
`userid` int(11) NT NULL default '0',
`value` varchar(150) NT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

A typical set of data looks like this:

id | inputfieldid | userid | value
1 1 2 John
2 2 2 Smith
3 3 2 name (AT) example (DOT) com

I am trying to come up with a query to return all the `values` of a
single userid in a single row. I've checked my books, the manual and
tried every type of join I can think of without success. I'd appreciate
some direction.

Thanks.

Albert Padley

General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
@thelittlemacshop.com

Dan,

Thanks. I'll take a further look at GRUP_CNCAT.

Albert

Jun 14, 2006, at 1:16 PM, Dan Buettner wrote:

Albert, MySQL's GRUP_CNCAT function might work for you:

In your case something like this:
SELECT userid, GRUP_CNCAT(value)
GRUP BY userid

HTH,
Dan
--
Albert Padley wrote:
>I have the following table schema in MySQL 4.1.18 which I didn't
>create, but have to work with.
>CREATE TABLE `phplog_userinput` (
>`id` int(11) NT NULL auto_increment,
>`inputfieldid` int(11) NT NULL default '0',
>`userid` int(11) NT NULL default '0',
>`value` varchar(150) NT NULL default '',
>PRIMARY KEY (`id`)
>) ENGINE=MyISAM DEFAULT CHARSET=latin1
>A typical set of data looks like this:
>id | inputfieldid | userid | value
>1 1 2 John
>2 2 2 Smith
>3 3 2 name (AT) example (DOT) com
>I am trying to come up with a query to return all the `values` of
>a single userid in a single row. I've checked my books, the manual
>and tried every type of join I can think of without success. I'd
>appreciate some direction.
>Thanks.
>Albert Padley
>General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?
>unsub=danb (AT) thelittlemacshop (DOT) com
>

Hi Chris.

I cannot see how it can be done with the current table
schema. Maybe you need to redeclare your table so the values
in the value column are more distinct?

What is value supposed to contain anyway? First name, last
name and email address?

What about a structure like:

id | userid | ipf_1 | ipf_2 | ipf_3
1 2 John Smith email_addy

Which will allow you to retrieve all the values you want
from the table as one row without having to repeat the
userid column?

HTH

Keith Roberts

In theory, theory and practice are the same;
in practice they are not.

Wed, 14 Jun 2006, Chris White wrote:

To: mysql (AT) lists (DOT) mysql.com
From: Chris White <chriswhite (AT) interfuel (DOT) com>
Subject: Re: Problem With Join Syntax

Wednesday 14 June 2006 10:55 am, Albert Padley wrote:
A typical set of data looks like this:

id | inputfieldid | userid | value
1 1 2 John
2 2 2 Smith
3 3 2 name (AT) example (DOT) com

I am trying to come up with a query to return all the `values` of a
single userid in a single row. I've checked my books, the manual and
tried every type of join I can think of without success. I'd
appreciate some direction.

This sounds like somewhat of a strange requirement. Why do they need to be in
a single row? There MIGHT be a way to do it with stored procedures, I'm just
not sure how

Thanks.

Albert Padley

Keith,

I agree that would be a good option to change the table.
Unfortunately, that's not an option at this point. I don't control
the schema. Thanks for the suggestion anyway.

Albert Padley

Jun 14, 2006, at 3:59 PM, Keith Roberts wrote:

Hi Chris.

I cannot see how it can be done with the current table
schema. Maybe you need to redeclare your table so the values
in the value column are more distinct?

What is value supposed to contain anyway? First name, last
name and email address?

What about a structure like:

id | userid | ipf_1 | ipf_2 | ipf_3
1 2 John Smith email_addy

Which will allow you to retrieve all the values you want
from the table as one row without having to repeat the
userid column?

HTH

Keith Roberts

In theory, theory and practice are the same;
in practice they are not.

Wed, 14 Jun 2006, Chris White wrote:
>
>To: mysql (AT) lists (DOT) mysql.com
>From: Chris White <chriswhite (AT) interfuel (DOT) com>
>Subject: Re: Problem With Join Syntax
>>
>Wednesday 14 June 2006 10:55 am, Albert Padley wrote:
A typical set of data looks like this:

id | inputfieldid | userid | value
1 1 2 John
2 2 2 Smith
3 3 2 name (AT) example (DOT) com

I am trying to come up with a query to return all the `values` of a
single userid in a single row. I've checked my books, the manual and
tried every type of join I can think of without success. I'd
appreciate some direction.
>>
>This sounds like somewhat of a strange requirement. Why do they
>need to be in
>a single row? There MIGHT be a way to do it with stored
>procedures, I'm just
>not sure how
>>
Thanks.

Albert Padley
>>
>--
>Chris White
>PHP Programmer/DB Fighter
>Interfuel
>>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?
>unsub=mysql (AT) karsites (DOT) net
>