#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0

    Talking Save Response Values in Table Join for Response Text


    This is probably going to take me more time to explain, than it will take someone to solve

    I have a table that I like to generate forms with. I have another table I like to store all available responses with, and yet another table that the data is saved in.

    We'll skip the form gen table and go straight to the point.

    Create the Response table:

    Code:
    CREATE TABLE IF NOT EXISTS `clientformresponses` ( `ResponseID` int(11) NOT NULL auto_increment, `ResponseText` text, `ResponseValue` text, PRIMARY KEY (`ResponseID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;
    
    INSERT INTO `formresponses` (`ResponseID`, `ResponseText`, `ResponseValue`) 
    VALUES 
    (1, 'Yes~No', '1~2'), 
    (2, 'January~February~March~April~May~June~July~August~September~October~November~December', '1~2~3~4~5~6~7~8~9~10~11~12');
    Create the Data Table:


    Code:
    CREATE TABLE IF NOT EXISTS `data` ( `DataID` int(11) NOT NULL auto_increment, `DataTitle` varchar(20) default '', `DataResponse1` int(11) default 0, `DataResponse2` int(11) default 0, `DataAddedBy` int(11) default 0, PRIMARY KEY (`DataID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0; 
    
    INSERT INTO `data` (`DataID`, `DataTitle`, `DataResponse1`, `DataResponse2`, `DataAddedBy`) VALUES (1, 'First One', 1, 2, 177), (2, 'Second One', 2, 2, 177);

    Basically, I have a fully functioning form system that uses responses saved in `formresponses` to choose from, so the user will be confronted with a form:

    Title:
    <open text>

    A Yes No Question:
    <radio button using ResponseID 1 row> Yes/No (value 1 for yes, 0 for no)

    What Month:
    <radio button using ResponseID 2 row> Jan, Feb, Mar, April... etc (Jan=1, Feb=2, Mar=3, Apr=4... etc)


    Now the data gets saved in `data`

    The First Row in `data` is:
    "First One, Yes, February"

    The Second Row in `data` is:
    "Second One, No, February"



    My question is simple, but the answer may be complex....

    How can I select FROM `data`, and then JOIN on the `formresponse` table to give me the correct response corresponding with "DataResponse1" and "DataResponse2"??


    Something to the effect of, but obviously NOT:
    Code:
    SELECT data.DataTitle, f1.ResponseText, f2.ResponseText
    INNER JOIN formresponses f1 ON data.DataResponse1 = f1.ResponseValue
    INNER JOIN formresponses f2 ON data.DataResponse2 = f2.ResponseValue
    I appreciate any help with this. Currently I foresee some amount of pre-query PHP programming Using the replace function... But want to see if there is something I can do outside of that without adding a layer of PHP prior to the query to aid in giving me the correct responses.
    Last edited by MySQL4Life; September 5th, 2012 at 10:33 AM. Reason: Added one [ code ] tag
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Just as an example of what I am thinking of doing with replace (but trying to find another way):


    Code:
    SELECT DataID, DataTitle, REPLACE(REPLACE(CONCAT('~',DataResponse1,'~'), '~1~', 'Yes'), '~2~', 'No') DataResponse1, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT('~',DataResponse2,'~'), '~1~', 'January'), '~2~', 'February'), '~3~', 'March'), '~4~', 'April'), '~5~', 'May'), '~6~', 'June'), '~7~', 'July'), '~8~', 'August'), '~9~', 'September'), '~10~', 'October'), '~11~', 'November'), '~12~', 'December') DataResponse2 
    FROM `data`
    This would require me to grab the responses from the Response table and build the SQL dynamically prior to querying the data (hence the additional layer of programming I am trying to get away from).
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    I don't understand. The lack of response suggests others might not either.

    Suggest you provide a small but representative data set as a set of CREATE and INSERT statements, as well as a corresponding result set - all wrapped up in [ code ][ /code ] tags (minus the spaces), but be prepared for responses of the "not sure why you'd want to do this" variety!
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Well, I thought I was pretty clear in my explanation. But it is what it is, if it isn't understood, then it is not understood.

    People could ask that question, and I would respond with "Because it circumvents hard coding forms, makes it dynamic, and reusability!"

    Create and Insert statements are in the post. The queries, are in the post, problem and one way to resolve also in there... Looking for another way, if there is one!
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    3
    I have a table that I like to generate forms with. I have another table I like to store all available responses with, and yet another table that the data is saved in.
    i think the confusion comes from you mentioning 3 tables and showing us the structure for 2

    im willing to help you get this just need more info

    1) i take it your replace querys are trying to insert the month name not the number correct?

    2) "clientformresponses" im not grasping the full intent for this table maby you can clarify on that. is this a meta table to store the relationship of response to value?

    3) would your missing table be the one to link the response to the data?


    after i keep rereading your post im getting a better understanding of what your trying to do

    when you say
    How can I select FROM `data`, and then JOIN on the `formresponse` table to give me the correct response corresponding with "DataResponse1" and "DataResponse2"?
    you would have the tables related with foreign keys.


    if im on the right track still then this could be done by simply setting clientformresponses.ResponseValue as a foreign key referencing data.DataID then changing the data in clientformresponses to

    Code:
    INSERT INTO clientformresponses (ResponseText, ResponseValue) VALUES ('January', 1)
    
    INSERT INTO clientformresponses (ResponseText, ResponseValue) VALUES ('February', 2)
    
    INSERT INTO clientformresponses (ResponseText, ResponseValue) VALUES ('March', 3)
    and so on...

    as for DataReponse1 change it to a boolean treat true as yes false as no

    then a

    Code:
    SELECT formresponses.column
    , clientformresponses.ResponseValue
    , data.DataTitle
    , data.DataResponse1
    , data.DataAddedBy
    FROM formresponses
    INNER
      JOIN data
        ON data.DataID=formresponses.DataID
    INNER
      JOIN clientformresponses
        ON clientformresponses.ResponseValue=data.DataResponse2
    Last edited by deljr; September 7th, 2012 at 08:56 AM.

IMN logo majestic logo threadwatch logo seochat tools logo