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?
from Problem updating three tables that are linked to eachother
No comments:
Post a Comment