Professional Web Applications Themes
Like Tree1Likes
  • 1 Post By Bill Karwin

Query on subrecords - MySQL

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 ...

  1. #1

    Default 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

    sicapitan@gmail.com Guest

  2. #2

    Default Re: Query on subrecords

    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

    [email]sicapitan[/email] wrote:
    > 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
    sicapitan@gmail.com Guest

  3. #3

    Default Re: Query on subrecords

    [email]sicapitan[/email] wrote:
    > WHERE ...
    > (`mos_facileforms_subrecords`.`name` = 'firstname' AND
    > `mos_facileforms_subrecords`.`value` LIKE '%ry%')
    >
    > 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%')
    Aha, okay. I didn't know how to reply to your earlier message, because
    I couldn't tell how you were using the database. With some examples it
    helps a lot.

    You're using a schema design known as Entity-Attribute-Value, or EAV.
    This is tricky to use correctly.

    Each attribute of your entity is stored on a different row of the
    mos_facileforms_subrecords table. When you select from a table in a SQL
    query, and reference that table in expressions, it refers to only _one_
    row of that table.

    You can select from a given table multiple times in a query. Each table
    reference can therefore refer to different rows within that table. You
    need to use "table aliases" so you can refer to these different rows. I
    think "table alias" should really be called "row alias" for that reason.

    I also like to use the aliases throughout the query, because it makes it
    shorter and I think it looks cleaner.

    SELECT
    F.`id` AS `form_id`,
    R.`id` AS `record_id`,
    FN.`value` AS `firstname`,
    LN.`value` AS `lastname`
    FROM
    `mos_facileforms_forms` AS F
    INNER JOIN `mos_facileforms_records` AS R
    ON F.`id` = R.`form`
    LEFT OUTER JOIN `mos_facileforms_subrecords` AS FN
    ON R.`id` = FN.`record` AND FN.`name` = 'firstname'
    LEFT OUTER JOIN `mos_facileforms_subrecords` AS LN
    ON R.`id` = LN.`record` AND LN.`name` = 'lastname'
    WHERE
    (F.`id` = '21' OR F.`id` = '22')
    AND FN.`value` LIKE '%ry%'
    AND LN.`value` LIKE '%sn%';

    Note that if you have many attributes, you'll need many joins. MySQL
    has a limit of 31 joins in a given query, so this data organization may
    break down pretty soon. This is one reason people prefer not to use the
    EAV design.

    Also notice I changed the JOINs to LEFT OUTER JOIN. This is to make
    sure we'll see the row from mos_facileforms_records, even if some of the
    attributes aren't present.

    That's another problem with EAV: it's inconvenient to ensure that a
    given attribute is present. With traditional table structures, you just
    define the column NOT NULL. There's no declarative way to do that with
    EAV, you have to do fairly complex queries to check if a given attribute
    is there.

    Instead, you should create columns in the entity table
    (mos_facileforms_records) for each attribute you need to store.
    For example:

    CREATE TABLE mos_facileforms_records (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    form INTEGER NOT NULL,
    firstname VARCHAR(50) DEFAULT '',
    lastname VARCHAR(50) DEFAULT '',
    address1 VARCHAR(50) DEFAULT '',
    address2 VARCHAR(50) DEFAULT '',
    email1 VARCHAR(50) DEFAULT '',
    -- etc. --
    FOREIGN KEY (form) REFERENCES `mos_facileforms_forms`(`id`)
    )

    But I'm guessing you're using FacileForms with Mambo, so you may be
    stuck with their code and schema.

    Regards,
    Bill K.
    Uldis PoĆŸarnovs likes this.
    Bill Karwin Guest

Similar Threads

  1. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  2. query of query throwing weird exception
    By damion in forum Coldfusion Database Access
    Replies: 0
    Last Post: April 25th, 03:12 AM
  3. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  4. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139