Posted by laptop on 09/29/41 11:43

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!
<sicapitan@gmail.com> 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
>

[Back to original message]