[Resolved] [Resolved] Select query problem

ph3n0m

Hi all

I have the following database structure

userid, contactid, fieldname, value

Data is stored like this

'123', '231', 'firstname', 'blah';
'123', '231', 'home_number', 'blah2';
'123', '232', 'firstname', 'new blah';
'123', '232', 'home_number', 'new blah2';

what I am wondering is what kind of select do I have to write so that the output would resemble

Firstname : blah
Home Number: blah2
Firstname : new blah
Home Number: new blah2

mrhappiness

best thing you can do is changing your table structure to

userid
contactid
first_name
home_number

ph3n0m

I managed to get around it without changing the database structure by doing the following

$sql_display = "SELECT b.value as simone, j.value as wonder from contacts as b, 
contacts as j WHERE b.userid='$id' AND
b.fieldname='firstname' AND
j.fieldname='home_number' group by
b.contactid order by b.contactid DESC";

It works 🙂 The only problem is I have to use either b.userid or j.userid - which are always the same

ph3n0m

sorry but the issue is not resolved at all, I completely messed up

ph3n0m

yay to myself and a collegue - we eventually got it sorted with an INNER JOIN

$sql_display = "select b.value as simone, j.value as wonder from contacts as b
								inner join contacts as j on b.contactid = j.contactid
								where b.userid='$id' 
								AND b.fieldname='firstname' AND j.fieldname='home_number' 
								order by b.contactid DESC";