Setting SQL variables in prepared statuses in PHP does not work

Question

Situation: I have a join table with columns testFK, questionFKand ordinal.

    testFK | questionFK | ordinal
       2         14          1
       2         15          2
       2         16          3
      _____________________________
 NEW   2         17          4

I want to add a new row to the table with testFK = 2and questionFK = 17, however, I want it to be ordinalautomatically created based on what is already in the table. Since the highest sequence number is 3, I want it to SQLautomatically generate 4.

I tried:

$stmt = $db->prepare('
        SET @QOrdinal = (SELECT MAX(ordinal) FROM junc_test_question WHERE testFK = ?);

        INSERT 
        INTO 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 I hardcode ordinalin, but I can't get SQLit to do it myself.

Any entry is welcome!

UPDATE: Error I get

Fatal error:  Call to a member function bind_param() on a non-object in \classes\Test.php on line 91

Therefore, I assume that it is SQLincorrect.

Answer 1

, , , .

MyISAM , , MyISAM , 1- (. MyISAM ):

MyISAM AUTO_INCREMENT . AUTO_INCREMENT MAX (auto_increment_column) + 1 WHERE prefix = . , .

, : , :

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 INTO my_table VALUES
(2,1,14),          
(2,2,15),          
(2,3,16);

INSERT INTO my_table (testFK,questionFK) VALUES
(2,17);

SELECT * FROM my_table;

+--------+---------+------------+
| testFK | ordinal | questionFK |
+--------+---------+------------+
|      2 |       1 |         14 |
|      2 |       2 |         15 |
|      2 |       3 |         16 |
|      2 |       4 |         17 |
+--------+---------+------------+

- (), :

INSERT INTO my_table (testFK,questionFK,ordinal) 
SELECT 2,18,MAX(ordinal+1) FROM my_table;

SELECT * FROM my_table;
+--------+---------+------------+
| testFK | ordinal | questionFK |
+--------+---------+------------+
|      2 |       1 |         14 |
|      2 |       2 |         15 |
|      2 |       3 |         16 |
|      2 |       4 |         17 |
|      2 |       5 |         18 |
+--------+---------+------------+

Answer 2

:

INSERT INTO junc_test_question (testFK, questionFK, ordinal)
VALUES (2, 17, (select * from (select ordinal+1 from junc_test_question order by ordinal desc limit 1) as p) );

1.

Answer 3

INSERT INTO junc_test_question (testFK, questionFK, ordinal)
VALUES (?, ?, (
  select MAX(ordinal)+1 from  (select * from junc_test_question ) as rftable where testFK=?
) );

this is my answer.