How to archive session data in MySQL

I am writing a website which is a list of questions to be filled out by the user.
Each question is asked one at a time, and based on the answer, the next question is asked. When the user submits the answer, the question ID and answer are stored in a PHP session. What I want to do is store that PHP session in a MySQL database so that it can be viewed and searched later on.

Initially, I made a table with a field for each question ID and stored the answer as each question was submitted. I realized that this would probably not scale very well, since I was currently at 50 questions, meaning 50 columns in the table. As more questions were added, more columns would need to be added to the storage table, so the number of fields could get quite large.

I then tried using the PHP serialize function, to save the session data into a single TEXT field in the database. This solves the problem of the number of fields, but it makes it a chore to query for information.

What would be the best way to store the session array data to a database, so that it could be searched later on? For example, find the records where the answer to question_1 is "X value"?

re: How to archive session data in MySQL

just a quick note:

1. restructure your data schema to be:
1.1 - create your questions in a sepeate table as a list table.
so your list table defenition should look like:
id,
question

1.2 - then create your answer table with the rows needed for answers.
id,
question_Id_fk,
answer

then assign a 1 to many relationship to them.

so the parent table will be the list because you can have one of them to many of the questions.

that will be a start why take up so much space when the records can do that for you.

hope this helps.

as far as storing the sessions into the database.

store the session in a variable i.e. $_session['Id'] = $Id then insert into table values ($Id, fk_id, $answer)

but if you do it the way i explained earlier with restructure your schema designs then you would not need to use sessions because that data is already inserted into the database.

sessions are good for your control, and managing user functions through out the site.

hope this help