mysql - Setting SQL variables in Prepared statments in PHP not working -

situation: have junction table columns testfk, questionfk, , ordinal.

    testfk | questionfk | ordinal        2         14          1        2         15          2        2         16          3       _____________________________  new   2         17          4 

i want add new row table testfk = 2 , questionfk = 17, however, want ordinal automatically generated based on in table. since highest ordinal 3, want sql automatically generate 4.

i've tried:

$stmt = $db->prepare('         set @qordinal = (select max(ordinal) junc_test_question testfk = ?);          insert          junc_test_question         (junc_test_question.testfk, junc_test_question.questionfk, junc_test_question.ordinal)         values (?, ?, @qordinal);     ');       $stmt->bind_param('iii', $this->testid, $this->testid, $question_id);     //var_dump($stmt);     if($stmt->execute()) {         return true;     } else {         return false;     } 

this works if hard code ordinal in, can't seem make sql on it's own.

any input appreciated!

update: error i'm getting

fatal error:  call member function bind_param() on non-object in \classes\test.php on line 91 

so i'm assuming sql isn't correct.

in context of ops recent comment, solutions presented below not appropriate.

if use myisam table type , define primary key in auto increment column 2nd one, myisam automatically calculates the increments grouped 1st field (see myisam notes in linked document):

for myisam tables, can specify auto_increment on secondary column in multiple-column index. in case, generated value auto_increment column calculated max(auto_increment_column) + 1 prefix=given-prefix. useful when want put data ordered groups.

so, can define table follows - although, in practice, i'm not sure i'd store ordinal @ all:

drop table if exists my_table;  create table my_table (testfk int not null ,ordinal int not null auto_increment ,questionfk int not null ,primary key(testfk,ordinal) ) engine = myisam;  insert my_table values (2,1,14),           (2,2,15),           (2,3,16);  insert my_table (testfk,questionfk) values (2,17);  select * my_table;  +--------+---------+------------+ | testfk | ordinal | questionfk | +--------+---------+------------+ |      2 |       1 |         14 | |      2 |       2 |         15 | |      2 |       3 |         16 | |      2 |       4 |         17 | +--------+---------+------------+ 

alternatively, can along these lines (simplified) - i'd never advocate solution:

insert my_table (testfk,questionfk,ordinal)  select 2,18,max(ordinal+1) my_table;  select * my_table; +--------+---------+------------+ | testfk | ordinal | questionfk | +--------+---------+------------+ |      2 |       1 |         14 | |      2 |       2 |         15 | |      2 |       3 |         16 | |      2 |       4 |         17 | |      2 |       5 |         18 | +--------+---------+------------+