How to get the next auto-increment id from table

[dmacman1962]

Hi all,

I am trying to figure out how to get the next value from a table that has an ID field that auto-increments. The reason being I am storing my questions and answers for a quiz in the database and I need to get the values for the answers fields before I store them.

IE
(my answers form is built dynamically, and I want to get the next ID to attach to the answers).
[answer]_____ --- [correct_answer_id] radio
[answer]_____ --- [correct_answer_id] radio
[answer]_____ --- [correct_answer_id] radio
[answer]_____ --- [correct_answer_id] radio

So when they enter in the answers, they can just check the "correct answer" radio button. I am storing the answers and in the questions table I have a [correct_answer_id] field and I update that table.

Does this make sense?

Thanks in advance,

Don

[leatherback]

this does not make sense, and you run the risk of wrecking your data integrety.

please elaborate what exactly you want to do, and why you need to get the ID of a non-existing field.

you usually get the ID for a field when you store the info, after that you do other processing.

[dmacman1962]

I am building the admin side of the quiz and I need to make the number of questions dynamic, so I do the following.

1) Ask them for the name of the quiz.
---Then I store that name and get the quiz_id

2) I ask them how many questions in the quiz (a drop down) and then I build a form with that many questions, and I hide the quiz_id for relating the tables.

3) I let them enter in all the questions, then when they hit submit, I store the questions, then query the database to get the question_id and question fields.
---- Then I build another form where it shows each question, then lets them enter in the 4 possible answers. (Here is where I was trying to get the answer_id's ahead of time).

4) Next I wanted to then store the answers and record the correct_answer_id so I can then update the questions table. The answer table is auto-incrementing the answer_id field.

answer_id -------- question_id ------- answer
1--------------------------14----------------Not yet.
2--------------------------14----------------Soon
3--------------------------14----------------Later
4--------------------------14----------------Never

and in the questions table I store the questions/correct answer like this...
ID------question_id-------question------------------------------correct_answer_id
1------------1----------------Whatever-----------------------------------3 [from answer table]
2------------2----------------Next question-----------------------------------1 [from answer table]

Does that clear it up? I am probably going about this all wrong, but this seemed like the only way based on the table structure.

Thanks,
Don

[leatherback]

hi,

I am not sure how you are passing the answers.

One option would be to pre-fill the answers in the database with empty fields, and retrieve the ID's that way, and do an update once you know the answers

The other would be to do something like:

// while looping though your questions:
echo "<input type=\"text\" name=\"answer[".$question_id."][]\"><input type=\"radio\" name=\"correct[".$question_id."]\" value=\"0\">";
echo "<input type=\"text\" name=\"answer[".$question_id."][]\"><input type=\"radio\" name=\"correct[".$question_id."]\" value=\"1\">";
echo "<input type=\"text\" name=\"answer[".$question_id."][]\"><input type=\"radio\" name=\"correct[".$question_id."]\" value=\"2\">";
echo "<input type=\"text\" name=\"answer[".$question_id."][]\"><input type=\"radio\" name=\"correct[".$question_id."]\" value=\"3\">";

Which would give the user the option to select which answer is correct.

Than you can query the database for the correct answers

[dmacman1962]

I follow your logic, but I am grey on the value of the radio button. I need that to reflects the answer_id value from the database. How will I relate the correct answer to all the answers stored if I use the values (0,1,2,3) without getting that from the answer_id ??

Thanks,

Don

[leatherback]

hi Don

You have 2 options
- Use the answer ID from the database, after inserting empty records, as an identifier in the fieldname
- Use 1-2-3-4 as the sequence number of the individual answer. You can then query these answers back, or at run-time decide that you need to set that answer as correct in the database (If you insert them one answer at a time).

A third option would be to have them insert the 3 incorrect and the 4th correct answer as separate fields:
3 fields answer[$questionid], one field correctanswer[questionid] and insert those individualy.

I am not sure which method I would use myself. I think the empty records is the easiest, procedure wise. However, it also is fail prone: If a use does not finish the creation of answers you have empty records in the database, with all the problems of that

[Roger_Ramjet]

Enter a question AND the answers at the same time, only way to do it and the only way I as a user would want to do it. So you build your form with fields for the questions and the answers all in one go. Personally, I would just have them enter one question and its answers at a time as well ie form for q1 -> enter, form for q2 -> enter.
Indeed, I would not even bother to ask how many questions in the quiz, just continue presenting the question input form until they hit finished. The same form can then be used to add questions at a later date, or to edit any exisiting question and answers.

[Roger_Ramjet]

PS you can NEVER predict what the next autoinc id will be even if it is a single user system. The reason for this is that any failed insert will waste an id and you never know if the last user had a failed insert. In a multi-user system, someone can do an insert after you queried for the last id used so the one you expect to use has already been taken.

[dmacman1962]

Hi rr,

OK, I made the question/answers one form, and it is working except I cannot seem to get the logic for the 'correct answer' radio button. Here is what I have so far.

<table>
<tr><td colspan="3" align="center"><?php echo $quiz_name; ?></td></tr>
<tr><td width="90">
  <label>Question</label></td><td colspan="2">
  <input name="question" type="text" size="100"/>
  </td></tr>
  <tr><td width="90">
  <label>Answer 1</label></td><td>
  <input name="answers[]" type="text" size="100"/>
  </td><td><?php echo '<input type="radio" name="correct" value="1">'; ?></td></tr>
  <tr><td width="90">
  <label>Answer 2</label></td><td>
  <input name="answers[]" type="text" size="100"/>
  </td><td><?php echo '<input type="radio" name="correct" value="1">'; ?></td></tr>
  <tr><td width="90">
  <label>Answer 3</label></td><td>
  <input name="answers[]" type="text" size="100"/>
  </td><td><?php echo '<input type="radio" name="correct" value="1">'; ?></td></tr>
  <tr><td width="90">
  <label>Answer 4</label></td><td>
  <input name="answers[]" type="text" size="100"/>
  </td><td><?php echo '<input type="radio" name="correct" value="1">'; ?></td></tr>
  </table>

Then on the process page, I save the question.

mysql_select_db($dbname, $conn) or die (mysql_error());
$sql ="insert into quiz_questions "; 
$sql .="(quiz_id) ";
$sql .="values ('$quiz_id')";
 if(!mysql_query($sql)){
		echo mysql_errno().'(1)<br>'.mysql_error().'<br><br>';
									  }	

Then insert the quiz_id to relate it to the question.

mysql_select_db($dbname, $conn) or die (mysql_error());
	$query="SELECT question_id
		FROM quiz_questions
		WHERE quiz_id='".$quiz_id."'";
		echo $query.'<br />';
		$result = mysql_query($query) or die(mysql_error());
						while($myrow=mysql_fetch_array($result)) {
							$question_id = $myrow['question_id'];
							}

Then get the question_id for that question.

mysql_select_db($dbname, $conn) or die (mysql_error());
		$sql ="insert into questions "; 
		$sql .="(question_id, question) ";
		$sql .="values ('$question_id', '$question')";
			if(!mysql_query($sql)){
				echo mysql_errno().'(2)<br>'.mysql_error().'<br><br>';
									  }	

Now try to insert the answers...

$sql ="insert into answers "; 
$sql .="(question_id, answer, correct) ";
$sql .="values ('$question_id', '$answers', '$correct')";
$result = mysql_query($sql) or die(mysql_error()); 

since there can only be 1 correct answer and for the other rows, there is no value for $correct, I get "Column count doesn't match value count at row 1".

Am I going down the correct path, or am I way off base with this method?

Thanks,

Don

[Roger_Ramjet]

Your main problem is that you are giving them all the same value: they need to be unique - radio buttons always need to be unique.

Personally I would not have the answers in an array but name them a1, a2, a3, a4 and the radio button the same then you know which is which - I mean the values of the radio buttons not the names.

[Roger_Ramjet]

Simplify things for yourself.
Save the quiz id in a session var.
Keep a count of the question number for the quiz in a session var

Store the correct answer id in the questions table as I said before - it elliminates the possibility of 2 answers being flagged as correct, and makes marking very much easier as I showed you

Form

// assume you've made the quiz entry, saved the name and id in session
session_start();

$quizid = $_SESSION['quizid'];
$quiz = $_SESSION['quiz'];

// check to see if finished
if (isset($_POST['finished'])) {
  // do whatever and leave
} else {

// process any form data    

if (isset($_POST['submit'])) {
  $q = $_POST['q'];
  $c = $_POST['c'];
  $correct = $_POST[$c];

  $sql = "INSERT INTO questions (quiz_id, q_no, question) VALUES ($quizid, $qno, '$q')";

  // get question id $qid

  // insert answers
  $a1 = $_POST['a1'];
  $a2 = $_POST['a2'];
  $a3 = $_POST['a3'];
  $a4 = $_POST['a4'];
  $sql = "INSERT INTO answers (quiz_id, qid, answer) VALUES ($quizid, $qid, '$a1'),($quizid, $qid, '$a2'),($quizid, $qid, '$a3'),($quizid, $qid, '$a4')";

  // to get the id of the correct answer
  $sql = "SELECT a_id FROM answers WHERE quiz_id=quizid AND qid=$qid AND answer='$correct'";

  // then update the question with it
  $sql = "UPDATE questions SET correct=$aid WHERE q_id=$qid";
}


// then display the form for next question
if (!isset($_SESSION['q'])) {
  $_SESSION['q'] = 1;
} else {
  $_SESSION['q'] += 1;
}
$q = $_SESSION['q'];

$cap = 'Question ' . $q . ' for Quiz ' . $quiz;

echo '<form><caption>' . $cap . '</caption>
    <label for="q">Question</label>
    <input type="text" name="q" size="100" />
    <label for="a1">Amswer 1</label>
    <input type="text" name="a1" size="100" />
    <input type="radio" name="c" value="a1" />
    <label for="a2">Amswer 2</label>
    <input type="text" name="a2" size="100" />
    <input type="radio" name="c" value="a2" />
    <label for="a3">Amswer 3</label>
    <input type="text" name="a3" size="100" />
    <input type="radio" name="c" value="a3" />
    <label for="a4">Amswer 4</label>
    <input type="text" name="a4" size="100" />
    <input type="radio" name="c" value="a4" />
    <input type="submit .... '; // etc for the form

}

[dmacman1962]

Hi rr,

I understand and appreciate that you did not test this and are writing it totally on this forum, so I do not expect finished code.

I see your logic, but I am confused on a few items. I am not trying to point out errors, I am just trying to get your code to work with my brain.

I may be wrong, but it seems that the question # and the actual question are both defined as '$q'.

// then display the form for next question
if (!isset($SESSION['q'])) {
$SESSION['q'] = 1;
} else {
$SESSION['q'] += 1;
}
$q = $SESSION['q'];

and then in the form, the question text field...

<label for="q">Question</label>
<input type="text" name="q" size="100" />

---

And I dont understand why the 'correct answer' radio buttons are a1 - a4.

---

These may get me closer to a functioning form.

I really do appreciate all your help.

Don

[Roger_Ramjet]

If I've used the same var name , sorry, but use your initiative and change one. It is actually not going to matter as they are used in different contexts so their scopr does not overlap, but not good practice I agree.

[edit] I just did it to save typing, you should always check code as you are doing and change var names to things that are meaningful to you[/edit]

The value of the radio button corresponds to the name of the text box it is associated with. This just makes it easy to find out which answer was ticked as the correct one.

  $c = $_POST['c'];              // eg $c = 'a2'
  $correct = $_POST[$c];    // now reads as $_POST['a2']

So $correct will be the answer entered into text box a2. We can use this to query for the id of that answer in the answers table and store it with the question in the questions table.

The reason for storing it in the questions table goes back to where we started with this: marking the tests. You query for the question ids and the correct answers ids, bung them into an array and use array comparison functions with the user's answers to find out which questions they got right or wrong.

Sometimes you have to set things up like this to make future processing easier. However, even if it was not going to make the marking easier, you would still store the correct answer id with the question because
a) correct answer is an attribute of question
b) you can have a unique index on that column to trap errors
c) you cannot have 2 answers marked as correct when there is only 1 table cell in 1 row, if you stored it in the answers table then easy for 2 answers to be marked as correct for the same question as there are 4 table cells in 4 rows.

[Roger_Ramjet]

What is not working by the way?

[dmacman1962]

I got this working, and I think I followed your logic.

I had to do a couple of things different.

1) On the first page of the setup, I had to add...

session_register("q");
$q=0;

Because the question count keep increasing, even if I emptied the tables to start over (during my tests).

And on the form page, I have not figured out how to let the user enter the info in for the last question and somehow terminate the script.

Here is my final(?) version of what you supplied. Feel free to comment on any and all of this. Note I had to use 'Submit2' for the isset, otherwise it caught my Submit from the form that they entered the name of the quiz on.

if (isset($_POST['Submit2'])) {
  $question = $_POST['question'];
  $q = $_POST['q'];
  $c = $_POST['c'];
  $correct = $_POST[$c];

mysql_select_db($dbname, $conn) or die (mysql_error());
  $sql = "INSERT INTO questions2 (quiz_id, q_no, question, caid) VALUES ($quiz_id, ".$q.", '$question', '$correct')";
  			$result = mysql_query($sql) or die(mysql_error());

  // get question id $qid
    mysql_select_db($dbname, $conn) or die (mysql_error());
		  $sql = "SELECT ID FROM questions2 WHERE quiz_id=$quiz_id AND q_no=$q";
				$result = mysql_query($sql) or die(mysql_error());
					while($myrow=mysql_fetch_array($result)) {
							$qid = $myrow['ID'];
							}

mysql_select_db($dbname, $conn) or die (mysql_error());
  // insert answers
  $a1 = $_POST['a1'];
  $a2 = $_POST['a2'];
  $a3 = $_POST['a3'];
  $a4 = $_POST['a4'];
  $sql = "INSERT INTO answers2 (quiz_id, q_id, answer) VALUES ($quiz_id, $qid, '$a1'),($quiz_id, $qid, '$a2'),($quiz_id, $qid, '$a3'),($quiz_id, $qid, '$a4')";
 	  			$result = mysql_query($sql) or die(mysql_error());

mysql_select_db($dbname, $conn) or die (mysql_error());
  // to get the id of the correct answer
  $sql = "SELECT answer_id FROM answers2 WHERE quiz_id=$quiz_id AND q_id=$qid AND answer='$correct'";
  	  			$result = mysql_query($sql) or die(mysql_error());
					while($myrow=mysql_fetch_array($result)) {
							$caid = $myrow['answer_id'];
							}

  // then update the question with it
mysql_select_db($dbname, $conn) or die (mysql_error());
  $sql = "UPDATE questions2 SET caid=$caid WHERE q_no=$q";
  		$result = mysql_query($sql) or die(mysql_error());
}


// then display the form for next question
if (!isset($_SESSION['q'])) {
  $_SESSION['q'] = 1;
} else {
  $_SESSION['q'] += 1;
}
$q = $_SESSION['q'];

$cap = 'Question ' . $q . ' for Quiz ' . $quiz_name;

echo '<form action="rr.php" method="post" name="quiz" target="_self"><caption>' . $cap . '</caption><br />
    <label for="question">Question</label>
    <input type="text" name="question" size="100" /><br /><br />
    <label for="a1">Answer 1</label>
    <input type="text" name="a1" size="100" />
    <input type="radio" name="c" value="a1" /><br />
    <label for="a2">Answer 2</label>
    <input type="text" name="a2" size="100" />
    <input type="radio" name="c" value="a2" /><br />
    <label for="a3">Answer 3</label>
    <input type="text" name="a3" size="100" />
    <input type="radio" name="c" value="a3" /><br />
    <label for="a4">Answer 4</label>
    <input type="text" name="a4" size="100" />
    <input type="radio" name="c" value="a4" /><br /><br /><br />
	Click here if this is the last question. <input type="radio" name="finished" value="finished" /><br /><br />
    <input name="Submit2" type="submit" value="Submit2" />
	&nbsp;&nbsp;
	<input name="Reset" type="reset" value="Reset" /><input type="hidden" name="q" value="'.$q.'">
	</form>'; // etc for the form

} 

How close am I to what you were thinking?

Thanks,
Don

[Roger_Ramjet]

Well, I would say if it is working then you are there. Now, if you want to have the 'finished' on the form like that then the logic goes

1 if submit: process
2 if not finished: display form
3 if finished: tidy up, unset session vars, say thank you, redirect

It is because you are not initialising and unsetting session vars that you end up using session register: never ever used it myself so I'm vague on what it does, never needed to use it.

I would personally present them with the finished quiz once they have finished so that they can review it and edit anything.

Also, when it comes to running the quiz make sure that you randomise the order of the answers for each question, you can do it with a subquery for the answers when you query for the quiz questions to display rather than just doing a join on the tables.

[dmacman1962]

OK, I', stumped on this.

I got everything working except now when I try to go thru the results of the quiz, and show the student how they did.

Here is my 'messed up attempt to loop thru the resutls.

I am probably making this way more complicated than needed, so I am throwing myself on the mercy of the board.

I store the students answers in the 'quiz_results' table.
ID ----- quiz_id - student_id - question_id - answer_id(the correct answers id)
250 ---- 109 -------1 --------------15 --------------54
251 ---- 109 -------1 --------------16 --------------58
252 ---- 109 -------1 --------------17 --------------62
253 ---- 109 -------1 --------------18 --------------68

And then in the questions table
q_id --- quiz_id --- question ----------------------- caid

15 ------109 --------Here is the 1st question.------- 53
16 ------109 --------Here is the 2nd question.-------58
17 ------109 --------Here is the 3rd question.--------63
18 ------109 --------Here is the 4th question.--------68

So I did a query...

mysql_select_db ($dbname, $conn) or die (mysql_error());
$answer = array();
$z=1;
$query = "SELECT question_id
as qid,
answer_id as aid
FROM quiz_results
WHERE quiz_id='".$quiz_id."'
AND student_id='".$student_id."'
ORDER BY qid";
$result = mysql_query($query) or die(mysql_error());
			while ($row = mysql_fetch_array($result)) {
					$answered = $row['aid'];
					$answer["$z"] = $answered;
					$z++;
					}

and a print_r($answer) gives me.

students answer array

Array
(
[1] => 54
[2] => 58
[3] => 62
[4] => 68
)

And then I query the database for the correct answers.

	mysql_select_db ($dbname, $conn) or die (mysql_error());
	$query = "SELECT caid
	FROM questions
	WHERE quiz_id=".$quiz_id."
	ORDER BY caid";
	$ca = array(); 
	$z=1;
	$result = mysql_query($query) or die(mysql_error());
	while ($row = mysql_fetch_array($result)) {
		$correct = $row['caid'];
		$ca["$z"] = $correct;
		$z++;
	} 					

and a print_r($ca) give me.

ca(correct answers from db) array

Array
(
[1] => 53
[2] => 58
[3] => 63
[4] => 68
)

Now I have tried about a zillion ways of looping thru and displaying

Question 1
--Answer 1 <= The correct answer.
--Answer 2 <= Your answer.
--Answer 3
--Answer 4

Question 2
--Answer 1
--Answer 2 <- You answered this one correctly.
--Answer 3
--Answer 4

My code us huge and confusing, so I did not include it, but can if it will help.

I would appreciate (big-time) any direction here.

Thanks,
Don

[Roger_Ramjet]

In sql

$sql = "SELECT q.q_id, q.question, q.caid, a1.answer, r.student_id, r.answer_id, a2.answer, IF(q.caid=r.answer_id, 1, 0) AS mark
FROM question q INNER JOIN quiz_results r ON(q.q_id=r.question_id)
INNER JOIN answer a1 ON(q.caid=a1.a_id)
INNER JOIN answers a2 ON(r.answer_id=a2.a_id)
WHERE q.quiz_id = $quiz_id AND r.student_id=$student_id AND r.quiz_id=$quiz_id
ORDER BY q.q_id"; 

Now this does depend on students only taking the quiz once and so only having a result for each question_id once: which should be the case and anyway applies to every method you use to mark the answers.

Gives you
q_id -- question -- correct answer id -- correct answer -- student id -- student's answer id -- student's answer -- mark (1 for correct, 0 for wrong)

Display them how you will.

You can also do it in php with array functions as I showed you elsewhere.