CODEWITHSUNDEEP
phpmysql
Dark King Rayleigh
Dark King Rayleigh

Reputation: 45

How to add a sequencial number to my insert query in mysql/php ps. not auto increment

I would like to insert a sequencial item number when everytime the user creates an exam. everytime he creates an exam it restarts to 1. I extremely need your help, people. More power. :)

here is my code:

$examid = $_SESSION['examid'];
$itemnum = 1;

$sql = mysql_query("INSERT INTO exam_questions (question_description, question_type, question_exam_id) VALUES ('$question', '$type', '$examid')")or die(mysql_error());
$lastinsertid = mysql_insert_id();
mysql_query("UPDATE exam_questions SET question_set_id='<??????????>' WHERE question_id='$lastinsertid' LIMIT 1")or die(mysql_error());

Upvotes: 1

Views: 224

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Try doing this with a join:

UPDATE exam_questions eq cross join
       (select max(question_set_id) as qsi
        from exam_questions
        where question_id='$lastinsertid'
       ) as const
    SET eq.question_set_id = const.qsi + 1
    WHERE question_id='$lastinsertid';

Upvotes: 1

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324790

You could try something like this:

SELECT COUNT(`id`) INTO @tmpvar FROM `exam_questions` WHERE `question_exam_id`='$examid';
INSERT INTO `exam_questions`
    (`question_description`,`question_type`,`question_exam_id`,`question_id`)
    VALUES ('$question','$type','$examid',@tmpvar+1);

Note that those will have to be sent as separate mysql_query calls, but it should work nicely. I'm also assuming that you've correctly escaped your parameters.

Upvotes: 0

Related Questions