Basically there are three vectors of improvement:
- of course you can make your queries optimal, avoiding a loop
- also, I would suggest to optimize the the process of executing the query, as it is evident that each query takes too much code to write
- you should never put a single variable directly in the query, but always use a prepared statement instead.
To solve the first problem, you can always use a JOIN. Yes, DELETE queries support JOINs as well as SELECT ones. So it goes:
DELETE c.* FROM classes c, schedules s WHERE c.scheduleid=s.id AND s.userid = ?
To solve the second one would propose to use PDO (which can solve the third one as well) as it lets you to run a query in one line:
$pdo->prepare("DELETE FROM users WHERE userid = ?")->execute([$userid]);
So the overall code would be
$sql = "DELETE c.* FROM classes c, schedules s WHERE c.scheduleid=s.scheduleid AND s.userid = ?";
$pdo->prepare($sql)->execute([$userid]);
$sql = "DELETE c.* FROM schools sc, schedules s WHERE sc.scheduleid=s.scheduleid AND s.userid = ?";
$pdo->prepare($sql)->execute([$userid]);
$sql = "DELETE FROM schedules WHERE s.userid = ?";
$pdo->prepare($sql)->execute([$userid]);
$sql = "DELETE FROM users WHERE s.userid = ?";
$pdo->prepare($sql)->execute([$userid]);
As long as you are connecting to PDO properly, this code will report errors automatically, without a single extra line of code!
Should I add the userid field to the other tables?
Quite contrary, you should NOT add such a field, keeping your database structure as normalized as possible
