Selecting from a table where field = this and value = that

Question

I have a mysql table that looks something like this:

Row 1:
    'visitor_input_id' => int 1
    'name' => string 'country'
    'value' => string 'Canada'

Row 2:
    'visitor_input_id' => int 1
    'name' => string 'province'
    'value' => string 'Alberta'

Row 3:
    'visitor_input_id' => int 1
    'name' => string 'first_name'
    'value' => string 'Jim'

The problem is that I need to be able to filter it so that a user can generate reports using this:

filter 1:
    'field_name' => string 'country'
    'field_operator' => string '='
    'field_value' => string 'Canada'

filter 2:
    'field_name' => string 'province'
    'field_operator' => string '!='
    'field_value' => string 'Alberta'

filter 3:
    'field_name' => string 'first_name'
    'field_operator' => string '%LIKE%'
    'field_value' => string 'Jim'

I am not really sure what the query would look like to be able to select from this using the filters. Any suggestions? (Unfortunately, creating a new table to store the data more sanely is not really feasible at this time because it is already full of user data)

I think it would look something like this:

if(field_name = 'province' THEN ADD WHERE field_value != 'Alberta')
if(field_name = 'country' THEN ADD WHERE field_value = 'Canada')
if(field_name = 'first_name' THEN ADD WHERE field_value LIKE '%jim%')

but I am not sure how that would work...

Answer 1

Are you able to create an SQL string and then execute it? The string would look like this:

SELECT * FROM yourtable
WHERE (name='country' AND value='Canada') AND
      (name='province' AND value!='Alberta') AND
      (name='first_name' AND value LIKE '%jim%)

EDIT:

I see. Multiple records. So try joining them. This is not correct SQL syntax but should look similar:

SELECT * FROM
 (SELECT * FROM yourtable WHERE (name='country' AND value='Canada'))
JOIN on visitor_input_id
 (SELECT * FROM yourtable WHERE (name='province' AND value!='Alberta'))
JOIN on visitor_input_id
 (SELECT * FROM yourtable WHERE (name='first_name' AND value LIKE '%jim%))

Answer 2

Turns out that this seems to work:

SELECT * FROM visitor_fields

INNER JOIN visitor_inputs ON (visitor_inputs.input_id = visitor_fields.input_id)

INNER JOIN visitor_fields as filter_0 
ON (filter_0.input_id=visitor_inputs.input_id 
AND filter_0.field_name = 'province'
AND filter_0.field_value != 'Alberta')

INNER JOIN visitor_fields as filter_1 
ON (filter_1.input_id=visitor_inputs.input_id 
AND filter_1.field_name = 'country'
AND filter_1.field_value = 'Canada')

INNER JOIN visitor_fields as filter_2 
ON (filter_2.input_id=visitor_inputs.input_id 
AND filter_2.field_name = 'first_name'
AND filter_2.field_value LIKE '%jim%')

Answer 3

I know you say creating a new table with a better schema isn't feasible, but restructuring the data would make it more efficient to query and easier to work with. Just create a new table (called visitor in my example). Then select from the old table to populate the new visitor table.

vistor
----------------
vistor_id
firstname
province
country

You could loop through the statement below with any scripting language (PHP, TSQL, whatever scripting language you're most comfortable with). Just get a list of all vistor_id's and loop through them with the sql below, replacing the x with the visitor_id.

INSERT INTO visitor (visitor_id, name, province, country) VALUES X,
(SELECT value FROM old_table WHERE name='first_name' AND vistor_id = x),
(SELECT value FROM old_table WHERE name='province' AND vistor_id = x),
(SELECT value FROM old_table WHERE name='country' AND vistor_id = x);

This will produce a table where all a visitor's data is on a single row.