August 30th, 2012, 10:11 PM
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:
Create the Data Table:
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`)
(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 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:
A Yes No Question:
<radio button using ResponseID 1 row> Yes/No (value 1 for yes, 0 for no)
<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:
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.
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
Last edited by MySQL4Life; September 5th, 2012 at 10:33 AM.
Reason: Added one [ code ] tag
August 30th, 2012, 10:20 PM
Just as an example of what I am thinking of doing with replace (but trying to find another way):
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).
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
September 1st, 2012, 03:36 AM
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!
September 5th, 2012, 10:31 AM
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!
September 5th, 2012, 01:06 PM
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
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
and so on...
INSERT INTO clientformresponses (ResponseText, ResponseValue) VALUES ('January', 1)
INSERT INTO clientformresponses (ResponseText, ResponseValue) VALUES ('February', 2)
INSERT INTO clientformresponses (ResponseText, ResponseValue) VALUES ('March', 3)
as for DataReponse1 change it to a boolean treat true as yes false as no
Last edited by deljr; September 7th, 2012 at 08:56 AM.