SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2000
    Location
    Atlanta
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mySQL query based on dynamic forms

    The engine that I'm building requires dynamic fields, that is, a form's fields are built dynamically from fieldname/fielddefaultvalues from the database.

    So the way data is stored into the database is:

    ID|fieldname|fieldvalue

    The ID is form's ID that when selected as a group, pulls out the fieldname/fieldvalues associated with the form's ID.

    The problem I'm having though is being able to select data based on various information. IE,

    1|address|PO BOX 727
    1|state|GA
    1|fname|John
    1|lname|Doe2
    2|address|PO BOX 1983
    2|state|TN
    2|fname|John2
    2|lname|Doe2

    They query I need to structure is a way so that I can select WHERE fname equals John2 and lname equals Doe2. However, since the column name in the table are labeled, fieldname|fieldvalue respectively, the query is incredibly hard for this situation to create.

    I want to be able to select for any fieldname for a particular fieldvalue and return the ID of the forms that matches based on the criteria.

    Here's my query so far:

    SELECT * FROM table1 WHERE (fieldname='fname' AND fieldvalue='John2') AND (fieldname='lname' AND fieldvalue='Doe2');

    You probably seee the problem as you can't match two rows with one 'AND' clause. I can't use 'OR' as the more clauses formed will narrow search results. Can this be achieved in SQL or should I bet better off querying for one particular data then get the result set, store that into a temp table, query the temp data with the new search criteria and recursively handle it till it completes all search criteria fields? Any suggestions?
    http://www.primomailer.com
    Mass Campaign/e-Mailing Service for
    Web Designers and eMarketing Agents


  2. #2
    SitePoint Addict Viral's Avatar
    Join Date
    Nov 2001
    Location
    Washington DC
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: mySQL query based on dynamic forms

    Originally posted by Jonathan
    1|address|PO BOX 727
    1|state|GA
    1|fname|John
    1|lname|Doe2
    2|address|PO BOX 1983
    2|state|TN
    2|fname|John2
    2|lname|Doe2

    Here's my query so far:

    SELECT * FROM table1 WHERE (fieldname='fname' AND fieldvalue='John2') AND (fieldname='lname' AND fieldvalue='Doe2');
    I'll start by saying that this is a very poorly designed table, which is the root of your problems. Why doesn't this table have the following fields: ID, fname, lname, address, state? That would solve your problems.
    If, for some reason, that is out of the question, you CAN search for multiple records, but the query will get ugly REALLY fast. Here's an example of matching two rows:

    SELECT * FROM table1 WHERE ((fieldname='fname' AND fieldvalue='John') AND (fieldname='lname' AND fieldvalue='Doe')) OR ((fieldname='fname' AND fieldvalue='John2') AND (fieldname='lname' AND fieldvalue='Doe2'))

    --Viral
    A computer without Windows is like a chocolate cake without mustard.

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2000
    Location
    Atlanta
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yea, I realize it was a poorly designed table after the fact and really. However, I sat down to rethink how I would in the future, redo this, but was unable to come with a solution.

    The reason why this table did not have a state column, fname, lname, etc. columns was that it was a dynamic engine where by the user decideds what the forms (ID) will have in terms of input. This means, they pick the text field name and default values on another table.

    So you might get a case where ID 1 would might have only a First Name and Last Name, but ID 2 would have the First Name, Last Name, and Title.

    We couldn't strict the database to a set certain naming convention on the columns since the user is allowed to create as many fields he wants for a particular form. A form the enduser creates could have 100 text fields if he wanted to, and on our end, we could not identify all the various fields that he might provide in the future forms.
    http://www.primomailer.com
    Mass Campaign/e-Mailing Service for
    Web Designers and eMarketing Agents



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
  •