Storing column value in array and passing into another table

[karan_5]

I want to store one column value in array because it is auto incremented and also it is a primary key
and this values i want to insert into another table where that primary key refer as a foreign key
so anyone can help me to come out from this problem..

[minitauros]

Pfew this maybe is a pretty vague description of your problem ^^. Could you be a bit more specific please?

You have two database columns which both have an INT field set as primary key, of which the one references to the other? And you want to store which value in what table?

[karan_5]

thank you sir ok i try to explain you the problem:
i have one table question in which one primary key which is integer and auto incremented and this table have some other field,this table is working fine i have problem in another table which names answer and it is having question_id as a foreign key(int) this table is connected to question table so i want the foreign key value same as whatever in question_id field in question table so sir now it is clear?

[minitauros]

So you have something like:

questions
id INT PRIMARY KEY AUTO_INCREMENT

and

answers
id INT PRIMARY KEY AUTO_INCREMENT
question_id INT
FOREIGN KEY (question_id) REFERENCES (questions.id)

right? And you need to know the ID of a question when you insert an answer? It seems to me that the ID of that question should be known at that point, and that it should be set in an <input> element of the form that is used to submit an answer. Am I understanding this correctly? :)

[karan_5]

yes sir.. thanks

[karan_5]

sir i am waiting for ur response..

[minitauros]

Oh sorry I thought that my answer had already solved your problem ^^. What I meant was (if I understand your case correctly) something like this:

<?php
// Retrieve questions. Something like:
$q = 'SELECT id,
        ...
    FROM questions
    ...';

// Handle the query... (I suppose you have code for this already)
?>

<form action="submit.php" method="post">
    <div class="entry">
        <p>Question 1: ...</p>
        <input type="text" name="answers[1]"><!-- 1 is the ID of the question here -->
    </div>
    <div class="entry">
        <p>Question 2: ...</p>
        <input type="text" name="answers[4]"><!-- 4 is the ID of the question here -->
    </div>
    <input type="submit" value="Submit">
</form>

On submit.php you will now receive an array called $_POST['answers'], of which the keys are the question ID's and the values are the answers to those questions.

[karan_5]

thnks sir but i am using jsp not php nd my problem is half of the way , m trying to solve it

[minitauros]

Oh yes, of course :). Well I thought my HTML alone would be able to solve your problem. The course of action is:

1. Get questions data (id, question, etc).
2. List the questions (see the HTML example I provided).
3. Submit the form.
4. On the target page, parse the form data and insert the data into the database.

Is anything in any of these steps not working or unclear, or do you need help with any of them? :)

[karan_5]

ya sir i solved it m having another problem some answer getting null i meant its not storing the answer value and foriegn key that is working i got one simple query for that.

[karan_5]

sir i understand ur point i even got d query but problem is no of. question will be unknwon so i cant give manually i have to go with some loop there i need idea how to fix..
l = st.executeUpdate("insert into answer(answer,user_id,survey_id,question_id) values('" + ans + "','" + userid + "','" + sid + "',(select question_id from question where survey_id='"+sid+"'))")
i ahve given like this d problem is i need to go wid loop

[minitauros]

So are you then asking how you can loop through the submitted answers, or how you can write a loop in JSP? :) I thought this would be a theory-only question so with my knowledge of PHP I thought I'd be able to answer it. I'm not so good at JSP so let me give you an example of how you'd do this in HTML/PHP, to give you an idea of the workflow you could use.

Your HTML would look like:

<form>
    <div class="section">
        <div class="question">
            <p>Question: ...</p>
            <input type="text" name="questions[your_question_id]">
        </div>
        <div class="answer">
            <p>Answer:</p>
            <input type="text" name="answers[your_question_id]">
        </div>
    </div>

    <!-- A question with multiple answers: -->
    <div class="section">
        <div class="question">
            <p>Question: ...</p>
            <input type="text" name="questions[your_question_id]">
        </div>
        <div class="answer">
            <p>Answer 1:</p>
            <input type="text" name="answers[your_question_id][]">
        </div>
        <div class="answer">
            <p>Answer 2:</p>
            <input type="text" name="answers[your_question_id][]">
        </div>
    </div>
</form>

If submitted, in PHP (so I think also in JSP) you would get an array like this:

answers => array(
    1348 => answer,
    143 => array(
        answer1,
        answer2
    )
)

The keys being the question ID's, with the values being the answers. You'd then loop through them using a loop of choice and execute the appropriate queries to insert the answers into your database. With this knowledge that would mean that in this query:

"insert into answer(answer,user_id,survey_id,question_id) values('" + ans + "','" + userid + "','" + sid + "',(select question_id from question where survey_id='"+sid+"'))"

you wouldn't need that subquery, because you already have knowledge of the question ID's your answers belong to. Your query could be as simple as:

INSERT INTO answer (
    answer,
    user_id,
    survey_id,
    question_id
) VALUES (
    '" + ans + "',
    '" + userid + "',
    '" + sid + "',
    '" + questionid + "'
)

Was this of any help? :)

[karan_5]

ok sir thank you..