PHP Update Multiple Row Based on "New" Data

Question

product_to_category

product_id  category_id parent_id
1          500          0
1          554          500
1          630          554

category
category_id category_name       parent_id
500          Pets           0
554          Pet Supplies       500
630          Pet Healthcare     554
700          Groceries          0
760          Bake Supplies      700
830          Toppings           760

Above is the format for category and the category path for a product , what i wanted is that

product_id 1 changed the category from 630 to 760 (Pet Healthcare to Toppings) means that the parent id would also need to change , but i have totally no idea how should i do it for all parent_id

Below is my code that i managed to do ( but only able to update the last product_to_category) , but did not update the parent_id for other line

$this->writedb->query("UPDATE product_to_category SET parent_id = '" . $getparentcategory . "' WHERE product_id =  '" . $getallproduct['product_id'] . "' AND category_id = '" . (int)$category_id . "' ");

Original :

Pets > Pet Supplies > Pet Healthcare

If edit "Pet Healthcare" to Toppings i want it to update to :

Groceries > Bake Supplies > Toppings

In easier explaination is that when updating the last category ,it will also update the parent id to the latest one . ( for example if edit "Pet Healthcare" to "Topping" , "Pets > Pet Supplies" will also update to " Groceries > Back Supplies "

The problem right now is from "product_to_category" , which need to remove/update it to correct category and parent_id

See about sql injection and the importance of prepared and bound queries — Strawberry, Nov 24 '20 at 08:42
I would fix your DB design first. Why are you storing twice the same information, the category id and its parent in **both** tables. [DRY!](https://en.wikipedia.org/wiki/Don%27t_repeat_yourself) — Cid, Nov 24 '20 at 08:43
in 'product_to_category' table it was perm storing the parent_id of the category, but for category['parent_id'] is updateable /dynamic , where it can be change anytime by staff — greenboxgoolu, Nov 24 '20 at 13:25
@Cid is right, you absolutely do not need to have the `parent_id` in `product_to_category`. So when a product changes categories, you should only update `category_id`. — El_Vanja, Dec 11 '20 at 09:23
*product_id 1 changed the category from 630 to 760 (Pet Healthcare to Toppings) means that the parent id would also need to change* Show desired final tables data state. PS. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=161fae1403900acb729fdfcbb6005753 — Akina, Dec 12 '20 at 05:59
The table `product_to_category` must contain only 2 columns - `product_id` and `category_id`, and only one row `(1, 630)`. Column `parent_id` and first 2 rows must be removed. — Akina, Dec 12 '20 at 06:05
@Akina but if product_to_category only 1 row , means that i cant get the parent category that i wanted. what i wanted example : `Groceries > Bake Supplies > Toppings` but if remove other row it might become `Groceries> Toppings` — greenboxgoolu, Dec 15 '20 at 01:02
*if product_to_category only 1 row , means that i cant get the parent category that i wanted.* o_O why? you simply gather all categories tree from `category` table starting from the category the product belongs to. Common recursive CTE. — Akina, Dec 15 '20 at 05:19
@Akina please post an answer so that the OP has a smart solution to throw the bounty on. — mickmackusa, Dec 17 '20 at 23:37

user2463644 · Answer 1 · 2020-12-17T21:01:12.050

Get all the categories (both old and new) related to the category being changed

$categories = fetchCategories($category_id);
$newCategories = fetchCategories($new_category_id);

function fetchCategories($category_id){
    $categories = [];
    do {
        $query = "SELECT category_id, parent_id FROM category WHERE category_id =:category_id";
        // prepare statement, bind value, execute, and fetch the results as associative array and push it to $categories
        $categories[] = $result;
        $category_id = $result['parent_id'];
    } while ($category_id);

    return $categories;
}

So, suppose your old category is Pets Health, id 630, and new category is Toppings, id 830, then the above code should return the following two arrays

//old
[
    ['category_id' => 630, 'parent_id' => 554],
    ['category_id' => 554, 'parent_id' => 500],
    ['category_id' => 500, 'parent_id' => 0]
]

//new
[
    ['category_id' => 830, 'parent_id' => 760],
    ['category_id' => 760, 'parent_id' => 700],
    ['category_id' => 700, 'parent_id' => 0]
]

Loop over them and update

for ($i = 0; $i < count($categories); $i++) {
    $query = 'UPDATE product_to_category SET category_id =:new_category_id, parent_id =:new_parent_id WHERE product_id =:product_id AND category_id =:category_id;
    //prepare statement, bind your values, execute
}

UPDATE

Assuming you have an array of two products whose category you want to change

$myArray = [
    [product_id, category_id, new_category_id],
    [second_product_id, second_category_id, second_new_category_id]
];

for ($i = 0; $i < count($myArray); $i++) {
    $product_id = $myArray[$i][0];
    $category_id = $myArray[$i][1];
    $new_category_id = $myArray[$i][2];
    $categories = fetchCategories($category_id);
    $newCategories = fetchCategories($new_category_id);
    for ($j = 0; $j < count($categories); $j++) {
        $query = 'UPDATE product_to_category SET category_id =:new_category_id, parent_id =:new_parent_id WHERE product_id =:product_id AND category_id =:category_id;
        //prepare statement, bind your values, execute
    }
}

to getallcategories this part is what i wanted , but the problem that i met right now is how do i update it to "product_to_category" table, as i tried to use foreach , but unable to achieve what i wanted — greenboxgoolu, Dec 16 '20 at 08:27
Do not declare unchanging prepared statements in a loop. You only need to declare it one time, then you can bind and execute in the loop. — mickmackusa, Dec 17 '20 at 21:32

Shaolin · Answer 2 · 2020-12-17T08:16:16.293

Hi here is my solution :

// arrays are passed by reference
function getParentCategory(array &$categories, array &$productCategories, int $parentId)
{
    foreach($categories as $category) {
        if ($category['id'] !== $parentId) {
            continue;
        }
        $productCategories[] = $category;
        if ($category['parent_id'] > 0) {
            getParentCategory($categories, $productCategories, $category['parent_id']);
            break;
        }
    }
}

// should be an array retreived from a PDO result like
// SELECT category_id, category_name, parent_id FROM category;
$categories = [
    [
        'id' => 500,
        'name' => 'Pets',
        'parent_id' => 0,
    ],
    [
        'id' => 554,
        'name' => 'Pet Supplies',
        'parent_id' => 500,
    ],
    [
        'id' => 630,
        'name' => 'Pet Healthcare',
        'parent_id' => 554,
    ],
    [
        'id' => 700,
        'name' => 'Groceries',
        'parent_id' => 0,
    ],
    [
        'id' => 760,
        'name' => 'Bake Supplies',
        'parent_id' => 700,
    ],
    [
        'id' => 830,
        'name' => 'Toppings',
        'parent_id' => 760,
    ],
];

$newCategoryId = 830;// should be set from for exemple $_POST['an_input_field']
$productCategories = [];
foreach ($categories as $category) {
    if ($category['id'] === $newCategoryId) {
        $productCategories[] = $category;
        getParentCategory($categories, $productCategories, $category['parent_id']);
        break;
    }
}
//you should delete all existing relations instead of updated them
$productId = 1;
$delete = sprintf("DELETE FROM product_to_category WHERE product_id = %d", $productId);
//$pdo->query($delete);
foreach($productCategories as $category) {
    $insert = sprintf("INSERT INTO product_to_category (product_id, category_id, parent_id) VALUES (%d, %d, %d)", $productId, $category['id'], $category['parent_id']);
    echo $insert."\n";
    //$pdo->query($insert);
}
$path = "";
$separator = " > ";
foreach (array_reverse($productCategories) as $category) {
    $path .= $category['name'].$separator;
}
echo rtrim($path, $separator);
//display Groceries > Bake Supplies > Toppings

As a personal note, I would suggest like some others to change your database design/structure. It is better to use a closure table or a nested tree to manage hierarchy. Also there is no need to store all the product's categories inside product_to_category. If you store only the last caterogy it is possible to retreive the whole tree.

edit: I've just noticed that since mysql 8.0 you can use recursive cte : https://www.mysqltutorial.org/mysql-recursive-cte/ So the function getParentCategory could be put aside. If you have a lower version of mysql then closure table is definitely a good bet.

You don't need to roll your own statements with `sprintf()`, using a single prepared statement (over and over) is the recommended way to securely query the db. PDO makes this easier than mysqli by allowing your to drop the values into the `execute()` call. — mickmackusa, Dec 17 '20 at 21:35
You are commenting a commented line, your are missing some points. This code is a standalone, and its purpose is to display the recursive function's results. Any outputed data must be escaped and `sprintf` fits the objective. As a result the outputed string is also safe for being queried so it can be run in pdo without prepared statement. But I agree if the purpose was not to display the queries then a prepared statement would have been mandatory. — Shaolin, Dec 18 '20 at 08:38
The OP is not asking for code that displays queries, the OP wants to modify the DB. You should edit your answer so that it resolves the question asked. The iterated INSERT query should most appropriately be a single prepared statement that is reused. https://stackoverflow.com/a/36166265/2943403 For consistency, I would use a prepared statement for any query receiving values from variables. — mickmackusa, Dec 18 '20 at 10:21

PHP Update Multiple Row Based on "New" Data

2 Answers2