Problem updating three tables that are linked to eachother

Question

I am trying to update three tables in my database that are linked to eachother.

My tables are set up like this:

templates

id
title
id_company

questioncat

id
title
ordering
tid (id of templates)

questions

id
question
catid (id of questioncat)
ordering

templates contains the name of my question list, questioncat contains all categories in my list, and questions contains all the questions belonging to a category.

Inserting that data works fine, I do that like this:

First my PHP script gets an array that for example looks like this:

Array
(
    [0] => Array
        (
            [name] => lijsttitle
            [value] => Lijst nieuw
        )

    [1] => Array
        (
            [name] => category[]
            [value] => cat1
        )

    [2] => Array
        (
            [name] => sortorder
            [value] => 1
        )

    [3] => Array
        (
            [name] => question[]
            [value] => q1
        )

    [4] => Array
        (
            [name] => category[]
            [value] => cat2
        )

    [5] => Array
        (
            [name] => sortorder
            [value] => 2
        )

    [6] => Array
        (
            [name] => question[]
            [value] => q1
        )

)

I do a check if the list already exists like this:

$check = '
SELECT *
FROM templates
WHERE title = "'.$conn->real_escape_string($title["value"]).'"';
$checkcon = $conn->query($check);
$check = $checkcon->fetch_assoc();
// If there is more than 1 result, update data instead of inserting
if($checkcon->num_rows > 0){

// Else insert data as new list
}else{
    // Insert template title and companyid
    $inserttemplate = '
    INSERT INTO templates (title, id_company) VALUES ("'.$conn->real_escape_string($title["value"]).'","'.$conn->real_escape_string($companyid).'")';
    $inserttemplatecon = $conn->query($inserttemplate);
    $lastinserted = $conn->inserted_id();

    $currCat = '';
    $sortorder = '';

    foreach($arr as $a) {
      $val = $a['value'];
      // handle category
      if($a['name'] == 'category[]') {
        // save cat name
        $currCat = $val;
        // init questions array
        $store[$currCat] = [];
      }else if($a['name'] == 'sortorder') {
            $sortorder = $val;
        $store[$currCat]['sortorder'] = $val;
      }else {
        // add question to question array
        $store[$currCat]['question'][] = $val;
      }
    }

    array_shift($store);
    // $key is de waarde van de categorie, $lijst is een array met alles onder de categorie
    foreach($store as $keycat => $lijst){
        $sortorder = $lijst['sortorder'];

        $insertcats = '
        INSERT INTO questioncat (title, tid, ordering) VALUES ("'.$conn->real_escape_string($keycat).'", "'.$conn->real_escape_string($lastinserted).'", "'.$conn->real_escape_string($sortorder).'")';
        $insertcatscon = $conn->query($insertcats);
        $lastinserted1 = $conn->inserted_id();

        $questionarr = $lijst['question'];

            foreach($questionarr as $q){
                $insertquestions = '
                INSERT INTO questions (question, catid) VALUES ("'.$conn->real_escape_string($q).'", "'.$conn->real_escape_string($lastinserted1).'")';
                $insertquestionscon = $conn->query($insertquestions);
            }
    }

    echo 'Uw lijst is toegevoegd';
}

If there are 0 results the list does not exist and is added to my database which works fine, but if there is more than 1 result it means the list does already exist and needs to be updated.

This is where I start getting problems.

Can I update all three tables in one query?

I first tried updating only the category names with the following code inside my if statement (the one where I check if a template list already exists):

foreach($arr as $a) {
    $val = $a['value'];
    // handle category
    if($a['name'] == 'category[]') {
        // save cat name
        $currCat = $val;
        // init questions array
        $store[$currCat] = [];
    }else if($a['name'] == 'sortorder') {
        $sortorder = $val;
        $store[$currCat]['sortorder'] = $val;
    }else {
        // add question to question array
        $store[$currCat]['question'][] = $val;
    }

    $updatetemplate = '
    UPDATE questioncat c
    INNER JOIN templates t
    ON c.tid = t.id
    SET t.title = "'.$conn->real_escape_string($title["value"]).'",
    c.title = "'.$conn->real_escape_string($currCat).'",
    c.ordering = "'.$conn->real_escape_string($sortorder).'"
    WHERE t.title = "'.$conn->real_escape_string($getcats['title']).'"
    AND c.id = "'.$conn->real_escape_string($getcats["id"]).'"';
    $updatetemplatecon = $conn->query($updatetemplate);
    echo 'Uw lijst is gewijzigd.';
}

But all category titles are the same and the ordering is aswell.

Is it possible to update the three tables in one query? They are basically three levels, Template - Categories beneath template - Questions beneath a category is it possible and if so, how?

Answer 1

You may want to start looking for PL/SQL procedures, they can save you coding and much secure.

BRIEFING :

When an event occurs in the 1st table it can execute multiple events on the database, the event could be (ON DELETE,ON UPDATE or ON INSERT) with AFTER and BEFORE conditions.

PL/SQL Triggers : Source

DEFINITION

Triggers are stored programs, which are automatically executed or fired when some events occur.

As for the Syntax it variate depending a little bit depending on which DBMS you are using. I hope my explanation was good.