Insert data

I want to insert with php questions in database and each quiz to have 26 questions. If my table has id, quiz, question, what will be the query to insert 26 questions and to have id from 1 to 26, and then when I insert quiz #2 the id to start again from 1 to 26?

What form are your questions in at the moment? Or will you enter them individually through a form?

I imagine id is a primary key in your table? If so you will need another field (eg qn_number) or else a different key (eg quiz, qn_number).

This is my form. Thank you for the idea. I guess I need another id in my table, not the main one, but I don’t want to enter manually this id. Is it possible to be entered automatically ?

<form enctype="multipart/form-data" id="insert_questions" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" >
   <br />
      <p><label>Quiz nr.</label><br />
      <input name="quiz_id" /></p>
      <br />
      <p><label>Question nr.</label><br />
      <input name="question_id" /></p>
      <br />
      <p><label>Question </label><br />
      <textarea name="question" ></textarea></p>
      <br />
      <p><label>Opt a</label><br />
      <textarea name="opt1" ></textarea></p>
      <br />
      <p><label>Opt b</label><br />
      <textarea name="opt2" ></textarea></p>
      <br />
      <p><label>Opt c</label><br />
      <textarea name="opt3" ></textarea></p>
      <br />
      <p><label>Answer</label><br />(a, b, c, ab, bc, ac, abc, 0)<br />
      <input type="text" name="answer"  /></p>
      <br />
      <label>Picture</label><br />
      <input type="file" name="photo_upload" />
     
      <br />
      <br />
      <input type="Submit" name="submit" value="Send" class="button" />

    </form>

One more question, can you enter a question for a certain quiz? Or does the 27th qn just cause a new quiz to be created?

Yes, the 27th qn has to be actually question #1 for quiz #2. Maybe I will put an echo, if it’s possible, for when I get to 26 that this is the last nr, now I will start a new quiz.

If your id field accepts duplicate ids then this can be easily done from a increment variable from php itself. If your id field does not accept duplicate ids (if it is a primary/unique key) then you have to have another field for this.

Edit:
Are you going to insert the questions for all the quizes continuously or that can be done randomly like inserting 2 questions for quiz 1 and 4 questions for quiz 2 and again 1 question for quiz 1? If it is randomly, then it can be difficult to manage such number.

BTW, why do you need this actually? If it is just to show in a page while printing the questions, then it can be done there only by incrementing a variable by 1.

Then you can just have id (auto_increment) and question in the db and use

SELECT * FROM questions ORDER BY id LIMIT 0, 26
SELECT * FROM questions ORDER BY id LIMIT 26, 26
...

for quiz 1 and 2 respectively.

No need for anything but question storage.

For insert you just need INSERT INTO quiz (question, answer) VALUES ($qn, $ans)

thank you.

I didn’t give you more details, but this is what I want.

lol, my bad, thought you were asking about inserting things, not printing :confused: Not sure where I got that from

I want to insert with php questions in database

Don’t worry hash. It’s happening sometimes with me, too :).

I don’t need to know about your business logic for myself but I asked you so that I can try to give you a right solution. No offence!!

But if you are comfortable to add a field as ‘qno’ in the questions table then I would suggest to do something like this:


$quiz 	= 1;
$result = mysql_query("SELECT MAX(qno) AS max_qno FROM questions WHERE quiz=$quiz") or die(mysql_error());
$row 	= mysql_fetch_object($result);
$max_qno = $row->max_qno;
$max_qno = (empty($max_qno)) ? 1 : $max_qno + 1;

$sql = "INSERT INTO questions(id, quiz, qno, question) VALUES('',$quiz, $max_qno,'Questions goes here');";

Heh, not worried, you asked how to insert stuff, what you meant was how do I add 1 to a variable in a loop.

They are about as different (in this respect) as asking how to get to the south pole when you mean how do I count my fingers.

Props to Rajug for recognizing this :slight_smile:

Please explain me what I have to insert in qno and max_qno, when every time I insert a question and If I have to insert in this rows automatically or not.

Please try to understand what i have done above. The code is for adding number automatically to the field. BTW there is only one field in the table that is to be added that is ‘qno’ max_qno is the variable (and alias in the query).


$quiz     = 1;
// get the maximum qno number for the particular quiz
$result = mysql_query("SELECT MAX(qno) AS max_qno FROM questions WHERE quiz=$quiz") or die(mysql_error());
$row     = mysql_fetch_object($result);
$max_qno = $row->max_qno;
// add one in the maximum number or assign 1 if there is no maximum number not found
$max_qno = (empty($max_qno)) ? 1 : $max_qno + 1;

//you can directly do something like as well
$max_qno += 1;

// now insert the record/question with the following query
$sql = "INSERT INTO questions(id, quiz, qno, question) VALUES('',$quiz, $max_qno,'Questions goes here');";

See and analyze last INSERT query above.

I agree with hash but I think it has some syntax errors.

sorin, the problem is your goal. We can’t know it. If all you want to do is select 26 questions from a db and number them, that is very different to inserting and tracking question with numbers (and quiz numbers), (which is also different again from reading a csv file and bulk inserting) …

What I want to do is to insert 200 questions in the same table and to display each time 26, but not randomly, for a quiz. Thanks to Rajug, I have a question solved. But what I wanted to know is, for ex in database i insert in my table

id 1
quiz_id 1
question question1

id 2
quiz_id 1
question question 2

and I didn’t know how to change the quiz _id to next number, every time after I inserted 26 questions.

Now I do this manually, but I can see that is not a good idea, I mean to type 1 every time, and to look in database to see if i have already 26 questions inserted, to put 2 or what number is next.

As above, you don’t need this, just use auto_increment id and question. Then you can just use LIMIT to get quiz 1, 2 etc. The only problem you might have is if you delete a qn, then all qns above that will shift one place down. That is, delete qn 26 and qn 27 is now qn 26.

I understand that using LIMIT 0, 26 you are selecting only 26 questions, but I don’t know what means LIMIT 26, 26.

The first number is the row to start from (not related to the id as such, just the row in the record set), the second is how many rows.

so LIMIT 26, 26 means start from row 26, get 26 more rows
then LIMIT 52, 26 for the third quiz etc