How to convert grouped row data into columns for lookup?

Question

I'm trying to create a lookup script for my application. The current table structure looks like this:

id      userid      profile_field       profile_value
-----------------------------------------------------
1       1           firstname           John
2       1           lastname            Smith
3       1           address             123 anywhere
4       1           city                city
5       2           firstname           Jane
6       2           lastname            Smith
7       2           address             456 anywhere
8       2           city                town

I'm trying to write a query that will allow me to search for users based on user input.

For example, my admins would want to search for users with last name like "smith". In this example, it would return user ids 1 and 2.

Is this possible to do with mysql? So far, I've looked into mysql's group_concat function, but it seems like it puts everything into a string instead of columns. Thus, I can't generate a proper where clause for searching.

EDIT: I must apologize, I should have been more clear.

I also need to consider having more than one input. For example, search for users with first name like "john" AND last name like "smith". In this example, it would only return user id 1.

That's the part that I'm having trouble with. Sorry again!

Your design is not normalised. You'll never get an optimal solution. — Lightness Races in Orbit, Feb 11 '12 at 14:35
You really need a serious reason to have a table structure like that instead of having a single row for every user with columns like firstname, lastname and so on. Entity-attribute-value models tend to have lots of performance issues. — piotrm, Feb 11 '12 at 14:38
It isn't clear what result you are looking for. Can you add the desired result to your question? — Tom Haws, Feb 11 '12 at 15:02

score 3 · Answer 1 · answered Feb 11 '12 at 14:29

3

try this one:

SELECT group_concat(DISTINCT UserID) as Results
FROM   tableName
WHERE  profile_value like CONCAT('%', 'Smith' ,'%')

this will return

Results
==========
1, 2

OR

SELECT UserID as Results
FROM   tableName
WHERE  profile_value like CONCAT('%', 'Smith' ,'%')

this will return

Results
==========
1
2

answered Feb 11 '12 at 14:29

John Woo

258,903
69
498
492

Unless I misunderstand the question, this answer seems right to me. – Tom Haws Feb 11 '12 at 15:02

score 1 · Answer 2 · edited May 23 '17 at 11:56

Probably not the best solution, but we've decided to go with a cron a job that will create an aggregate table. Then, we can perform the lookup using the aggregate table.

create table profile_values_aggregate 
    select users.uid,    
        (SELECT value FROM profile_values WHERE fid=1 and profile_values.uid=users.uid) AS cust_fname,
        (SELECT value FROM profile_values WHERE fid=2 and profile_values.uid=users.uid) AS cust_lname
        ... ... ...
    FROM profile_values 
    inner join users on users.uid=profile_values.uid
    GROUP BY uid

Query idea was taken from here: https://stackoverflow.com/a/7976379/1203831

I've added an answer, anyway, as I thought this solution would kill your server because there are so many selects — Mosty Mostacho, Feb 12 '12 at 20:58

score 0 · Answer 3 · answered Feb 11 '12 at 14:25

0

SELECT 
  `userid` 
FROM 
  `table` 
WHERE 
   `profile_field` = 'lastname' 
      AND 
   `profile_value LIKE %smith%;

answered Feb 11 '12 at 14:25

Wes Crow

2,971
20
24

score 0 · Answer 4 · answered Feb 11 '12 at 14:26

0

Just try something like this :

SELECT userid FROM table WHERE profile_value='Smith'

answered Feb 11 '12 at 14:26

JuSchz

1,200
2
15
30

Tom Haws · Answer 5 · 2012-02-14T05:43:09.150

0

Thank you for providing your "not the best solution". Based on it, I think I understand what you are trying to do. The standard approach is to join the table to itself like this:

SELECT pv1.`userid` FROM `profile_value` AS pvfirst
JOIN  `profile_value` AS pvlast ON pvfirst.`userid` = pvlast.`userid`
WHERE
pvfirst.`profile_field` = 'firstname' 
AND 
pvfirst.`profile_value` LIKE '%john%'
AND
pvlast.`profile_field` = 'lastname' 
AND 
pvlast.`profile_value` LIKE '%smith%'
;

I hope this helps. If you have any followup questions, don't hesitate to ask.

edited Feb 14 '12 at 05:43

answered Feb 12 '12 at 20:24

Tom Haws

1,322
1
11
23

So if I need to do up to 15 possible searches (first name, last name, city, state, zip, country, etc), I would need to do up to 15 joins? I don't have the to test this now, but how's the performance on such queries? – jellysandwich Feb 13 '12 at 13:50
1. It's a good thing you bring up performance, because this table structure is a perfect candidate to need an index on each of the four columns. Whether you have each column indexed will make a huge performance difference when they are in the WHERE clause. 2. MySQL knows how to optimize queries so that when you add in all the WHERE conditions, the join is not as bad as you might fear. You can verify this intuitively by realizing you would be essentially joining only one row from each of the 15 tables, or in other words, joining 15 tables of one row each. 3. Try it and see. – Tom Haws Feb 14 '12 at 05:48

Mosty Mostacho · Answer 6 · 2012-02-13T17:45:44.687

What about this?

select userid from profile_values
where
    (profile_field = 'firstname' and profile_value = 'John') or
    (profile_field = 'lastname'  and profile_value = 'Smith')
group by userid
having count(*) = 2

The number 2 is the amount of conditions separated by the OR.

Edit:

This seemed to work for "or" properly, but it didn't work for the case if I was looking for some with the first name "john" AND the last name "smith"... – jellysandwich

It is working fine too. As I told you before, if the number 2 is the amount of condition pairs (each pair is a field - value comparison), then with one condition there should only be a 1. Example

select userid from profile_values
where
    (profile_field = 'firstname' and profile_value = 'John')
group by userid
having count(*) = 1

This seemed to work for "or" properly, but it didn't work for the case if I was looking for some with the first name "john" AND the last name "smith"... — jellysandwich, Feb 13 '12 at 13:51

How to convert grouped row data into columns for lookup?

6 Answers6