Your Location is: Home > Php

Linking foreign keys between tables

From: Thailand View: 3441 Elisa Gulino 

Question

I have two tables: TEMA e SOTTOTEMA. In TEMA I have, as primary key, IDtema which is auto-increment. I want it to be foreign key in the sottotema table and I wrote, in phpmyadmin, where I have my db,

ALTER TABLE sottotema ADD FOREIGN KEY (IDtema) REFERENCES tema (IDtema)

It doesn't give me errors, but the foreign key doesn't work. I have predefined themes in a select option, and depending on the theme you choose, you can insert a sub-theme yourself.

       <select id = "tema" name = "tema">
           <option hidden></option>
           <option value = "Animali"> Animali</option>
           <option value = "Cucina"> Cucina </option>
           <option value = "Sport"> Sport </option>
           <option value = "Musica"> Musica </option>
           <option value = "Cinema"> Cinema </option>
           <option value = "Letteratura"> Letteratura </option>
       </select></br>
      
       <div id = "sottotema" style = "display:none">
           <p id = "titolosottotema"> Sottotema </p>
           <input type = "text" placeholder="Scrivi un sottotema" id = "st" name = "st"/>
       </div> 

All this obviously is inside a FORM and everything works, except for inserting the IDtema as a foreign key, in the sottotema table.

I report only the data entry queries in the db, but in my php code there is something else written, including the connection to the db obviously

<?php
   
$sottotema = $_POST['st'];

$query = "INSERT INTO sottotemi (nomeSottotema) VALUES ('$sottotema')";
$result = mysqli_query($mysqli, $query);
if (!$result){​​​​​​
echo "errore 1";
}​​​​​​ else {​​​​​​
$query2 = "INSERT INTO blog (titoloBlog,nomeSottotema,nomeUtente,sfondo, font, colorefont) VALUES ('$titoloblog','$sottotema',(SELECT nomeUtente FROM utentiregistrati WHERE nomeUtente = '$nomeutente'),'$sfondo','$font','$colore');";
$result2 = mysqli_query($mysqli, $query2);
if(!$result2){​​​​​​
echo 'errore 2';
}​​​​​​

?>

In the db I have already entered my predefined themes, so the idtema, primary key, is already associated with a specific theme (eg 1 - Animali, etc.). Please help me I'm desperate !!!!!

Best answer

For the foreign key to work you need to specify the foreign table identifier in the INSERT operation, otherwise the row would be orphan form the start. Instead of INSERT INTO sottotemi (nomeSottotema) VALUES ('$sottotema') you'll need to find identifier (IDtema) to the foreign table (tema) and also provide it in the INSERT operation as follows:

INSERT INTO sottotemi
  (nomeSottotema, IDtema)
SELECT
  '$sottotema', T.IDtema
FROM tema AS T
WHERE T.nome = '$tema'