PHP Saving information to MySQL but not using hard coded code

Question

Hi guys,

Thanks everyone for assisting me but know I'm well and truely stuck...hopefully someone can advise?

My current code that saves to a MySQL database:

$q1 = $_POST["q1"];
$q2 = $_POST["q2"];
$q3 = $_POST["q3"];
$q4 = $_POST["q4"];
$q5 = $_POST["q5"];
$q6 = $_POST["q6"];
$q7 = $_POST["q7"];
$q8 = $_POST["q8"];


    $proc = mysqli_prepare($link, "INSERT INTO tresults_bh_main (respondent_id, ip, browser, q1, q2, q3, q4, q5, q6, q7, q8) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
    mysqli_stmt_bind_param($proc, "issiiiiiiii", $respondent_id, $ip, $browser, $q1, $q2, $q3, $q4, $q5, $q6, $q7, $q8);

At the moment this is all hard coded and I need it to use variables:

I have an array which stores the following information:

$qs['questions'] - stores, for example q1, q2, q3, q4 etc - obviously these change both in terms of of numbers, ie. q10, q11, q12 and also the amount in the array - so there could be 4 q's or 10 q's stored in the array.

What I struggling to get my head around is how I would set-up this hard coded page to work with my array and variables, so I don't have to hand code the page.

If anyone can help, much appreciated.

Homer.

Answer 1

Hello. You need to normalise your database design.

You can keep your first table to record the response, but store a response_id as a unique, auto-incrementing primary key:

tbl_responses

response_id, respondent_id, ip, browser

Then add a second table to store the submitted data. A relationship is formed between the two tables on the response_id field. Create a composite key from both response_id and question_id to uniquely identify each row.

tbl_questions

response_id, question_id, value