View Full Version : Making and editing complex arrays

10-05-2012, 11:55 AM
0 down vote favorite

I'm working on adding a feature to survey website, that allows the maker of the survey see the results of the survey according to other results - so that one chart would show what women have answered, one what men, one what children, one what adults etc.

This works like this: There are 0-4 questions, which are special "background info" questions. Each of these has maximum of 5 answers. These values are passed to PHP file in following form: $_POST['tausta(number of the background question, 0-4)']. The values of these variables are numbers 0-4, which are the answer possiblities.

Then there are the regular questions, which are passed like this: $_POST['kys'(number of the question)] The values of these variables are numbers 0-4, which are the answer possiblities, too.

There are 1-40 questions, but the numbers are between 0-39. I have the regular Answers-column in the database, which has all the answers in this form: 0*000*0*|||0*0000 where ** separates the number of answers in one answer choice, and ||| separates the questions.

I also have 20 columns made for the answers according to background, which are named pv0-pv19. The numbers mean the background answers: the answers of background question one, answer three are in pv2, question three answer one in pv10 etc.

So my problem is, how do I get the answers in the correct column correctly, when there are multiple background questions. I have spent like 10 hours working on this without finding a viable solution.

Here is the current code, which handles normal answers (not relative with any background information). The variables are mostly in Finnish, but I commented the code in English for you. Note that this code accomplishes what it was coded for, the problem is adding a code in this code, which would handle the problem described previously in this post.

$vanhat_tulokset = HaeTietokannasta('vastaukset','kyselyt','id',$id); // Gets the old results

$vantuks = explode('|||',$vanhat_tulokset); // creates an array of the question answers

while($numero < $maara){ // while number < amount of questions ($maara is defined earlier)
${'ehdot' . $numero} = explode('***',$vantuks[$numero]); // creates variables of question answers
$kysymys = htmlspecialchars($_POST['kys' . $numero],ENT_QUOTES); // what the user has answered
if($kysymys !== "0"){ // this is because PHP considers string '0' empty
if(empty($kysymys)){ // make sure that the user has answered all questions

echo 'Unohdit vastata johonkin/joihinkin kysymyksiin. Ole hyvä ja paina selaimesi Takaisin-näppäintä varmistaaksesi, että vastaat kaikkiin kysymyksiin.';
exit; // if not, print this (Finnish)

if(is_numeric($kysymys)){ // has to be a number, but is not integer

if($kysymys === '0'){ // if the user has answered 0 (1), then we add one to the results

${'ehdot' . $numero}[0] = ${'ehdot' . $numero}[0] + 1; // to the right place, naturally

if($kysymys === '1'){ // and these next lines work the same, but with different answers

${'ehdot' . $numero}[1] = ${'ehdot' . $numero}[1] + 1;


if($kysymys === '2'){

${'ehdot' . $numero}[2] = ${'ehdot' . $numero}[2] + 1;


if($kysymys === '3'){

${'ehdot' . $numero}[3] = ${'ehdot' . $numero}[3] + 1;


if($kysymys === '4'){

${'ehdot' . $numero}[4] = ${'ehdot' . $numero}[4] + 1;


$numero = $numero + 1;

$nummer = 0;

while($nummer < $maara){ // again while number is less than amount of questions
$muuttuja = ${'ehdot' . $nummer}; //variables we defined and added 1 to right place earlier

${'ehdot' . $nummer} = implode('***',$muuttuja); // Implode them back to string

$nummer = $nummer + 1;

$numer = 0;
$jono = "";
while($numer < $maara){ // do I need to say this again?

$jono = $jono . ${'ehdot' . $numer} . '|||'; // And make the values back to full string
$numer = $numer + 1; //

$palaute = HaeTietokannasta('palaute','kyselyt','id',$id); // this is irrelevant, it just
if (!empty($_POST['palaute'])){ // puts possible feedback about the survey to the database
$palaute = htmlspecialchars($_POST['palaute'],ENT_QUOTES) . '|' . $palaute;

mysql_query("UPDATE kyselyt SET vastaukset='$jono' WHERE id='$id'"); // put the values in
mysql_query("UPDATE kyselyt SET palaute='$palaute' WHERE id='$id'");
echo 'Olet vastannut kyselyyn onnistuneesti.'; // "You have completed the survey successfully"


10-05-2012, 04:00 PM
Step 1: Normalize your database.
Since the database is not normalized, it will create tremendous difficulty in querying. It will also create anomalies and rigid question requirements as no question can ever be removed or inserted, only appended to the existing questions list. Aggregating any statistical data will be near impossible when the number of answers hits a certain threshold since you can no longer index questions and must evaluate every single record for every single answer in an iterative fashion.

Many to many relationships require the use of flattening tables. This simply involves taking the left hand association and the right hand association and merging a composite record in a table referred to as a flattening table. So if you had a User, and a Question which they can answer multiple possibilities to, you'd have a UserAnswer table to correspond the two together.

Until you normalize this schema, then any advice you get will eventually fail. If you had 150,000 recorded entries, you will not be able to easily tell who answered "cow" to a question "which animal goes moo?" for any woman between the ages of 20 and 25 for an example. You would need to scan all 150,000 entries and their corresponding answers to get that result, while a normalized database could do that using simple joins and where clauses.