Query on subrecords

Hi There, not sure if this can be done with SQL or if there is an SQL
crossover

I have two tables:

Table "record":
id, record_id, data1, data2

Table "subrecord":
id, record_id, name, value

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%')

2. Present the data like this :

foreach distinct `name` echo `name`
then
foreach `value` echo `value`

What I've got so far is the query linking the record to the subrecord,
so I can get all the data into an array with the names:

do {

$thelist['record_id'][] = $row_forms['record_id'];
if ( $row_forms['name'] == "firstname" ) $thelist['firstname'][] =
$row_forms['value'];
if ( $row_forms['name'] == "emailaddress" ) $thelist['emailaddress'][]
= $row_forms['value'];

} while ($row_forms = mysql_fetch_assoc($forms));

Can i do this on a SQL level to avoid getting all the data into an
array record_id, then render results which match those id's. Sounds
overly complicated and confusing.

Any help appreciated. Thanks!
Ryan

Re: Query on subrecords

Look at LEFT JOIN, GROUP BY and ORDER BY in the mysql manual. They should
help you out. Also - why are there two 'record_id's. Sounds like redundancy
to me!
wrote in message
news:1143499964.681507.106830@i40g2000cwc.googlegroups.com.. .
> Hi There, not sure if this can be done with SQL or if there is an SQL
> crossover
>
> I have two tables:
>
> Table "record":
> id, record_id, data1, data2
>
> Table "subrecord":
> id, record_id, name, value
>
> 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%')
>
> 2. Present the data like this :
>
> foreach distinct `name` echo `name`
> then
> foreach `value` echo `value`
>
> What I've got so far is the query linking the record to the subrecord,
> so I can get all the data into an array with the names:
>
> do {
>
> $thelist['record_id'][] = $row_forms['record_id'];
> if ( $row_forms['name'] == "firstname" ) $thelist['firstname'][] =
> $row_forms['value'];
> if ( $row_forms['name'] == "emailaddress" ) $thelist['emailaddress'][]
> = $row_forms['value'];
>
> } while ($row_forms = mysql_fetch_assoc($forms));
>
> Can i do this on a SQL level to avoid getting all the data into an
> array record_id, then render results which match those id's. Sounds
> overly complicated and confusing.
>
> Any help appreciated. Thanks!
> Ryan
>

Re: Query on subrecords

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

Re: Query on subrecords

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