How Do I Delete Row With Primary Key Using Foreign Key From Other Table?

I have a table called 'agenda' //translation: dairy with the following rows: idagenda // primary key title waar organisatie ... etc. ... I also have a table for the date of an dia

Solution 1:

You can delete from multiple tables in one query:

DELETE agenda.*, agendadatum.*FROM agenda
JOIN agendadatum USING (idagenda)
WHERE tot < NOW();

This will delete rows from both agenda and agendadatum, and only those rows matching the conditions (same rows as returned by the query if you replaced DELETE with SELECT).

Solution 2:

In your create table you need to mention the Foreign Key Constraint like

FOREIGN KEY (product_id) REFERENCES products (id)
       ONDELETE CASCADE
       ONUPDATE CASCADE,

and after that If you run the delete query, automatically the rows will be deleted, which is referencing the ids of the table

You can go through the explanation present in delete on cascade

Solution 3:

if you are using php you can try the following

//get all agentadatum data with date now and before$select_agendadatum = mysql_query("SELECT * FROM agendadatum WHERE tot <= NOW()") ordie (mysql_error());
//loop through rowswhile($row_agendadatum = mysql_fetch_assoc($select_agendadatum))
{
     //delete agendadatum row$delete_agendadatum = mysql_query("DELETE FROM agendadatum WHERE id = '".$row_agendadatum['id']."'") ordie (mysql_error());
    //delete agenda row$delete_agenda= mysql_query("DELETE FROM agenda WHERE idagenda = '".$row_agendadatum['idagenda']."'") ordie (mysql_error());
}

if your logic accepts more than one agendadatum per agenta you can simply count if there is more than 1 agendadatum in a specific agenta before deleting the agenta...

hope this helps