Posted by sicapitan on 10/05/14 11:44

Yep everything is cross indexed, numerically, with int id's.

In simple terms, I'm trying to build an sql query. My table has a
column called 'name' and 'value'. Inside 'name' are numerous values
but I am only interested in 9 of them.

So when 'name' = "firstname", I want to see if the 'value' is LIKE
'%$var%'

If I am just searching for firstname it is very easy, but if I want to
search for lastname, something like:

when
'name' = 'firstname' and 'value' LIKE '%$var%'
AND
'name' = 'lastname' and 'value' LIKE '%$var2%'

In a less simple form, I have joins with 2 parent tables int he
following query:

SELECT
`mos_facileforms_forms`.`id` AS `form_id`,
`mos_facileforms_records`.`id` AS `record_id`,
`mos_facileforms_subrecords`.`value`,
`mos_facileforms_subrecords`.`name`,
`mos_facileforms_subrecords`.`id` AS `subrecord_id`
FROM
`mos_facileforms_forms`
Inner Join `mos_facileforms_records` ON
`mos_facileforms_forms`.`id` = `mos_facileforms_records`.`form`
Inner Join `mos_facileforms_subrecords` ON
`mos_facileforms_records`.`id` = `mos_facileforms_subrecords`.`record`
WHERE
(`mos_facileforms_forms`.`id` = '21' OR
`mos_facileforms_forms`.`id` = '22') AND
(`mos_facileforms_subrecords`.`name` = 'firstname' AND
`mos_facileforms_subrecords`.`value` LIKE '%ry%')

returns results as expected

No results are returned when I add on the following, even though the
data definatly exists:

AND
(`mos_facileforms_subrecords`.`name` = 'lastname' AND
`mos_facileforms_subrecords`.`value` LIKE '%sn%')

hope this helps

NC wrote:
> sicapitan@gmail.com wrote:
> >
> > I have two tables:
> >
> > Table "record":
> > id, record_id, data1, data2
> >
> > Table "subrecord":
> > id, record_id, name, value
>
> How, if at all, are they cross-indexed?
>
> > In the subrecord the name contains the header title, and the value the
> > value. I need to:
> >
> > 1. Search the database
> >
> > (`name` = 'firstname' AND `value` LIKE %'somevalue%') AND
> > (`name` = 'emailaddress') AND `value` LIKE '%somevalue%')
>
> This is syntactically incorrect; the number of opening parentheses does
> not match the number of closing parentheses. So I really have no idea
> what you are trying to do.
>
> Also, am I correct thinking that 'firstname', 'emailaddress', and
> '%somevalue%' are all user inputs?
>
> Right now, there are too many blanks to give you a precise answer; the
> only thing I can tell you is that you should look into SQL joins. You
> should join the two tables on the field they have in common.
>
> Cheers,
> NC

[Back to original message]