Question 20846 in Mysql Question posted in
The official MySQL documentation can be found here.

Writing a DELETE query for 3 tables – Mysql

I am trying to write a simply query that will delete an event based off its event_id. That event_id could be in 1 of 3 tables in a database however.

Leagues
Tournaments
Trainings

Each event_id is generated by a randomizer function so each id is unique.

I have tried a few different syntaxs for SQL but none of them will actually delete the event from the DB table. Any suggestions?

Here is what I have:

if(isset($_POST['delete'])){
   
    $query = "delete from leagues where event_id= '$event_id', delete from tournaments where event_id= '$event_id', delete from trainings where event_id= '$event_id'"; 
    $result = mysqli_query($conn, $query);
    
    header('location: index_admin.php');
    
    
} else {
   
}

I have also tried using the union command, using multiple delete queries, putting a semicolon after each delete command. What else can I try?

Tags:

2

Answers


  1. 0 
    if(isset($_POST['delete'])){
    $stmt = $pdo->prepare('delete from leagues where event_id = :event_id');
    $stmt->execute([ ':event_id' => $event_id]);
    $res = $stmt->fetch();
    header('location: index_admin.php');
}else{
   
}
    Login or Signup to reply.
  2. 0

    No reason to execute 3 separate DELETEs.

    DELETE leagues, tournaments, trainings
FROM (SELECT $event_id AS event_id) src -- use parameter instead of immediate insert
LEFT JOIN leagues USING (event_id)
LEFT JOIN tournaments USING (event_id)
LEFT JOIN trainings USING (event_id)

    Of course you’d not insert the value into the query like above – you’d use prepared statement.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search