Can I update one table with values from another table?

[karla007]

I want to make a poll for my website(flash) where I can change the question from time to time. When I put up a new question, question_id in table1 will change (auto increment)

I am trying to make an updatefunction in php to work with amfphp and flash.

I have two tables:
1: Question: question_id, question.
2: Answer: answer_id, answer, question_id

question_id is foreign key in table 2. Can I somehow update question_id in table 2 with the 'most recent or higest' value from question_id in table 1.

I hope you understand my question, I find it very difficult to explain what I am looking for.

Any help is much appreciated, Thank you.

Karla

[dlite922]

I don't understand your question.

If it's a poll, then you provide the question (table1) and the user taking the poll provides records in answer table (table2).

You're program should know which question the user is answering to tie that back to table 1, so all you have to do is make sure you have the most recent question.

Which is easy to do if the latest question has the highest question_id

SQL:
SELECT * FROM question_table ORDER BY question_id DESC LIMIT 1;


Feel free to elaborate more,




Dan

[karla007]

I see I have a long way to go when I can't even explain my problem. I am not even sure I am looking for an UPDATE.
At first my poll worked just fine (I only have one question and you can only answer yes or no). Everytime the user answered yes, the function just inserted yes into the answer table: INSERT INTO answer(answer) VALUES('yes');
The problems started when I wanted to add a new question. To make sure that the new answers only belonged to the new question, I added a foreing key to the answer table: question_id. So now my INSERT must look like this: INSERT INTO answer(answer, question_id) VALUES('yes', some variable question_id that comes from the question table). I don't know how to get that variable into VALUES, that's when I thought I needed an UPDATE instead, but that won't work either.

Does this make any sense at all?
I am totally new to this, but I really want to learn.

Thank you for your time
Karla

[karla007]

@karla007
After a good nights sleep and some more research I found the solution:
INSERT INTO answer (answer, question_id) VALUES ('yes', (SELECT MAX(question_id) FROM question));

It has taken me two days to find out!

[dlite922]

that variable should be pushed to the query just like the answer.

So basically the question_id is in a hidden form field along with the answer.

When it gets submitted with answer, you just provided in the VALUES. There is no need for the MAX(question_id)

Ask yourself this. What if you have multiple questions displayed at the same time, how do you know which answer goes to which question?

The solution: submit the question_id with every question's answer.

Hope that makes sense,






Dan

[dlite922]

@karla007
You have hard coded 'yes' into that query? isn't that actually coming from the user? How do you know the user answered 'Yes'? what about "no"?

What if the answer is not yes/no, what if it's A,B,C or a number?





Dan