Laminas Db Delete

Questions Components & MVC
1

I would like to find out how to delete rows from three related tables with no foreign keys set? For example, the table quizzes is related to table answers and to table tallies using the column quiz_id. The documentation and API are not clear on this. Thanks from a beginner.

2

You can add foreign keys yourself to get the results you want.
Doing something like this might work out:

DROP TABLE IF EXISTS `quizzes`;
CREATE TABLE IF NOT EXISTS `quizzes` (
  ...
  FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `answers`;
CREATE TABLE IF NOT EXISTS `answers` (
  ...
  FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`quiz_id`)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `tallies`;
CREATE TABLE IF NOT EXISTS `tallies` (
  ...
  FOREIGN KEY (`answer_id`) REFERENCES `answers` (`answer_id`)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

Then your deleteQuiz method in the QuizzesTable class would look something like this:

public function deleteQuiz($quizId)
{
    $sqlQuery = $this->sql->delete()->where(['quiz_id' => $quizId]);
    $sqlStmt = $this->sql->prepareStatementForSqlObject($sqlQuery);
    return $sqlStmt->execute();
}

I hope this does it for you.

3

Thank you for helping me with this problem.

1 Like