How to query this table

[Warren Gamley]

Hi folks,

I've looked through these forums, but haven't seen quite what I'm trying to do. I have a CMS that uses PHP and a MySQL backend, and has created a table that I am wanting to query. Basically what I am wanting to do is return a list of users who have indicated that they would like to subscribed to my mailing list. The table and fields are outlined below (this is from database dump):

DROP TABLE IF EXISTS userProfileData;
CREATE TABLE userProfileData (
userId int(11) NOT NULL default '0',
fieldName varchar(128) NOT NULL default '',
fieldData text,
PRIMARY KEY (userId,fieldName)
) TYPE=MyISAM;

--
-- Dumping data for table `userProfileData
--

INSERT INTO userProfileData VALUES (3,'firstname','Mike');
INSERT INTO userProfileData VALUES (3,'lastname','Smith');
INSERT INTO userProfileData VALUES (3,'email','[e-mail]');
INSERT INTO userProfileData VALUES (3,'emailSubscribe','yes');

INSERT INTO userProfileData VALUES (4,'firstname','Doug');
INSERT INTO userProfileData VALUES (4,'lastname','Brown');
INSERT INTO userProfileData VALUES (4,'email','[e-mail]');
INSERT INTO userProfileData VALUES (4,'emailSubscribe','no');

INSERT INTO userProfileData VALUES (5,'firstname','Jane');
INSERT INTO userProfileData VALUES (5,'lastname','Thomas');
INSERT INTO userProfileData VALUES (5,'email','[e-mail]');
INSERT INTO userProfileData VALUES (5,'emailSubscribe','yes');

INSERT INTO userProfileData VALUES (6,'firstname','John');
INSERT INTO userProfileData VALUES (6,'lastname','Black');
INSERT INTO userProfileData VALUES (6,'email','[e-mail]');
INSERT INTO userProfileData VALUES (6,'emailSubscribe','no');


I've added a line between the different INSERT sections for readability. So what I'm needing to do is be able to query this table so that I can at least have a list of email addresses that have indicated they would like to subscribe to my mailing list. This would be done by indicating yes in the emailSubscribe field. The problem for me is that emailSubscribe is not an actual field in the table, but is rather a value assigned to the fieldname field.

If anyone can help me with this, I would be very grateful. I'm not sure if this is a newbie question, or if what I'm trying to do would be a bit more involved.

Thanks in advance for any assistance you could give!

WarrenG