Inserting and recalculating table in phpmyadmin

Question

i want to check and update "part" table. So i have table "StockEntry" http://prntscr.com/gvkc60 i can compare them by part_id. And i have "part" table: http://prntscr.com/gvkcqd

What i want to do is, when i insert export product on my site i use this code:

<?php
        $servername = "localhost";
        $username = "xxxxx";
        $password = "xxxxx";
        $dbname = "xxxxx";
        $dateTimeVariable = date( "Y-m-d H:i:s" );
        $stock = -$_POST[ sto ];
        // Create connection
        $conn = new mysqli( $servername, $username, $password, $dbname );
        // Check connection
        if ( $conn->connect_error ) {
            die( "Connection failed: " . $conn->connect_error );
        }
        if ( !empty( $_POST[ 'vnos' ] ) ) {
            $sql = "INSERT INTO StockEntry (part_id, user_id, stockLevel, price, dateTime, correction, comment)
                    VALUES ('$_POST[vnos]', '3', '$stock', '', '$dateTimeVariable', '0', 'test')";

            if ( $conn->query( $sql ) === TRUE ) {
                echo "New record created successfully";
            } else {
                echo "Error: " . $sql . "<br>" . $conn->error;
            }
        }
        $conn->close();

        ?>

That code insert -1 stock or whatever i take out and sort them with part_ID, every product has own part_ID. After that i want to check "part" table and product with same id (so part_id = id) to change number.. let's say if i had 30 products when i take 10 table update number on 20.

How can i do that?

so basicly i can do id from "part" - part_id from "stcokEntry" but dont know how.. can you guys help me please?

If someone could leave discord or mail or something to quick chat please i just need that 1 sentence.. need it by 3o'clock..

What do you have so far of your update statement? And how does the changed value need to be calculated? — jeroen, Oct 10 '17 at 10:10
Can you give us a screenshot of the corresponding entries in the StockEntry table as well? This will help us get more clarity as to the kind of solution that you are looking for. — DeadLock, Oct 10 '17 at 10:21

score 0 · Answer 1 · answered Oct 10 '17 at 10:19

If you are storing the same stockLevel in both the tables, (assuming each stockLevel corresponds to a single part_id), then you should just store/update it against that part_id in the Part table.

And every time you are in need of the stockLevel for a given part_id, just join the StockEntry table with the Part table with a join query. Something like

SELECT stockLevel 
    FROM StockEntry se
        LEFT JOIN Part p
            ON ( se.part_id = p.id);

Not sure if this is really what you needed. But this concept is called Normalization (quite certain you already knew that).

I edited my post to be more clear what i want.. im sorry for first post.. can you check again please — Snoopy, Oct 10 '17 at 10:43

Inserting and recalculating table in phpmyadmin

1 Answers1