[jimmyo88]

Could someone be of assistance. I've got site where the user can take up to 6 different quizzes.
After the user has completed the quiz, their taken to a page which displays their score. The problem is i need to insert this score in to the database with the column name as a variable.

I have 6 columns in my database named quizresult1, quizresult2, all the way to 6 and some other fields including username etc.

If you look at my code below, the code works fine if i remove the variable in the sql statement and put in a column name such as quizresult1.
The problem is is that i need this column to be set depending on the quizname variable?
Thanks to anyone who can help

<?php
session_start();	
$username = $_SESSION['username']; //get username
$quizname = $_SESSION['quizname']; //get quizname from quiz page
$score = $_SESSION['score'];	  //get score

echo $username;  //testing username 
echo $quizname; //testing quizname "quizresult1"  <----Name of database field

echo "Congratulations on completing the quiz. Your score was ";
echo $score; //echo score
echo "<br />";

$connection = mysql_connect("localhost","root","root") or die("couldn't connect");  //connect to db      
$select = mysql_select_db("login") or die ("cannot select database!");   // select db login
$sql = ("UPDATE users SET '.$quizname.' = '$score' WHERE username = '$username'"); //update the table users with the column name equal to $quizname where usernames match
mysql_query($sql); 

echo "<a href='index.php'>Click</a> here to return to the the home page";

?>

[jimmyo88]

p.s. I'm tempted just to go for the quick fix and make 6 result pages?? What are peoples ideas on this?

[Atli]

Hey.

There is a flaw in your database layout. Basically: you shouldn't repeat columns in a table. It violates the first rule of relational database design. (I wrote about this in detail in this article.)

Your "quizresult1" to "quizresult6" fields are a repeating group of data, and as such should exist in it's own table. - The best layout I can think of for what your are doing is a standard N:M relationship: two tables "user" and "quiz", with a third table linking them together. The third table would log how each user scored on a quiz.

Consider this:

+---------+     +--------------+     +---------+
| users   |     | quiz_results |     | quiz    |
+---------+     +--------------+     +---------+
| id (PK) |>--->| user_id (PK) |  |-<| id (PK) |
| name    |     | quiz_id (PK) |<-|  | name    |
| email   |     | score        |     | etc...  |
| etc...  |     | date_time    |     +---------+
+---------+     +--------------+

Then, instead of updating the score, you could do this:

INSERT INTO `quiz_result` (`user_id`, `quiz_id`, `score`, `date_time`)
VALUES ({$userID}, {$quizID}, {$score}, NOW());

That said...

There are two problems with your SQL query:

• You surround the $quizname with dots, like you are trying to concat it to a string, but this is all happening inside the string, so the dots are just... dots, corrupting the SQL string.
  
  
• You encapsulate the column name in single-quotes. This doesn't work. You should use back-ticks when dealing with identifies, like column names: (`). -- Single-quotes are only for string values.

This post has been edited by Atli: 28 April 2011 - 07:18 PM

[jimmyo88]

I completely understand you 100%. I'm doing a database course aswell and we've learnt all about normalisation. Unfortunately our webdevelopment tutor insists we stick to a flat file?? Why...I Don't know, its simply illogical.

anyway... I tried using backticks with my identifiers and removing the dots and still no luck. Is this a bit of a lost cause?

P.S that database article you wrote is excellent. I'll most definetly be using it as revision reading for my exam in a couple of weeks.

This post has been edited by jimmyo88: 28 April 2011 - 09:09 PM

[GhandiJones]

This statement needs to be looked at...

$sql = ("UPDATE users SET '.$quizname.' = '$score' WHERE username = '$username'");

You may want to think about how you're separating your information. Look at what Atli is saying in the later part of his post. You're not segmenting your strings and variables correctly. And to point out what is incorrect with how it currently sits... Run this.

<?php
$quizname = "Hello";
$score = "world";
$username = "!";

$sql = "UPDATE users SET '.$quizname.' = '$score' WHERE username = '$username'";
$sql_correct = "UPDATE users SET " . $quizname . " = " . $score . " WHERE username = " . $username;

//Yours.
echo $sql;

echo "<br /><br />";

//Mine.
echo $sql_correct;

?>

I just wanted you to see where not paying attention to quotes can get you into trouble. But, hey, you're commenting the crap out of your code. Keep it up!

The method you're trying, is not how you want to do this, mind you. I highly recommend utilizing the method mentioned by Atli. Don't get me wrong, build what you have to build for school... but go back and try it the better way.

This post has been edited by GhandiJones: 28 April 2011 - 11:49 PM

[Dormilich]

@GhandiJones: if $score is not a number, you need to add quotes.

[jimmyo88]

i tried your code Ghandi and I can see where mine was looking incorrect. I've tried using your $sql_correct but this still doesn't work either?
Have i misunderstood the above example? Was that just to show me where I was making errors?

[GhandiJones]

@Dormilich: Correct sir. I was tired when I put that together, just trying to show him that he was mixing single and double quotes.