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?

user_8004593 · Answer

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.

user_8601712 · Answer

To update three tables at the same time you should do it in a single database transaction.

If you have some triggers (ON UPDATE/DELETE/INSERT) on those tables that should be executed, some database engines, like PostgreSQL allows to defer execution moment of triggers. If the trigger is deferred, it will be executed right after commiting the transaction, and thanks to it you can even update all key types (primary, foreign, unique, complex etc.).

user_2575671 · Answer

Try update with a single query does not make the process simpler. It would be a very complex update query. And still you need a way to identify somehow the "child" rows (category, question) for every "parent" (templates, category).

Solution with multiple update/insert queries:

Every table have a unique id in your example. You could use this id to update specific rows. In your php array (POST/GET?) you have name and value for each item, but no id. Just add "id" additionaly.

If id is empty => INSERT a new row (This item comes not from database)

if id is defined => UPDATE the row with specific id (This item already exists and may have been changed)

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

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

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

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

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

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

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

)

Problem updating three tables that are linked to eachother

3 Answers