特定用户会话的SQL UPDATE

Question

I can add and delete the data I want to/from a db for a specific user, based on the session ID however, when I try to update the data for that specific user, I get an error. That duplicate key statement doesn't work properly either because the key is set to record ID vs stock's symbol. Any idea how to resolve that because now when a person tries to add the same stock twice, they are allowed to do so and so this leads to issues later on. Not sure if I'm going about this the correct way now with that Update statement instead, for the update part, so I'd appreciate any feedback/help. Thanks!

Code for INSERT & UPDATE:

// When the Buy button is pressed, specific action will be triggered according to the input given.
    if(isset($_POST['Buy']))
    { 
        // Checking whether first line is completely filled.
        if(empty($_POST['sym1']) or empty($_POST['pri1']) or empty($_POST['q1']))
        {
            ?><h2><center>To add values, please fill out at least the first row completely.</center></h2><?php
        // die();
    }
    // Loop through the form to allow for an appropriate db update.
    for($x=1;$x<=4;$x++)
    {
        $sym = [];
        $pri = [];
        $q = [];
        // If input provided is correct then update the db.
        if (!empty($_POST['sym'.$x]) and !empty($_POST['pri'.$x]) and !empty($_POST['q'.$x])) 
        {
            $sym[$x] = $_POST['sym'.$x];
            $pri[$x] = $_POST['pri'.$x];
            $q[$x] = $_POST['q'.$x];
            $memberid = $_SESSION['memberID'];
            $sql = "INSERT INTO portfolio2 
                (stocks_symbol, price, quantity, memberID)
                VALUES ('$sym[$x]', $pri[$x], $q[$x], $memberid)
                ON DUPLICATE KEY UPDATE
                price=$pri[$x], quantity=$q[$x]";

            // Check if values are added successfully and if so, then display a message to the user.
            if(mysqli_query($conn, $sql))
            {
                ?><h2><center><?php
                echo "Stocks added successfully!";
                ?></h2><center><?php
            }
            else
            {
                ?><h2><center><?php
                echo "Error- Stocks weren't added!". "<br>". $sql.
                "<br>". $conn->error;
                ?></h2><center><?php
            }
        }
    }
    mysqli_close($conn);
}
// UPDATE 
    elseif(isset($_POST['Update']))
{
    // Check to see whether the stock symbol has been provided
    if(empty($_POST['sym1']))
    {
        ?><h2><center>To update values, please enter the symbol of the stock to be updated.</center></h2><?php
        // die();
    }

    // Loop through the form to allow for an appropriate db update.
    for($x=1;$x<=4;$x++)
    {
        $sym = [];
        $pri = [];
        $q = [];

        // When all three values to be updated are given and are correct, update the db accordingly.
        if (!empty($_POST['sym'.$x]) and !empty($_POST['pri'.$x]) and !empty($_POST['q'.$x])) 
        {
            $sym[$x] = $_POST['sym'.$x];
            $pri[$x] = $_POST['pri'.$x];
            $q[$x] = $_POST['q'.$x];
            $memberid = $_SESSION['memberID'];
            $sql = "UPDATE portfolio2 SET price=$pri[$x] and quantity=$q[$x] WHERE stocks_symbol='$sym[$x]' and memberid=$memberid";

            // Check to see whether the values are updated successfully and if so, then display a message to the user.
            if(mysqli_query($conn, $sql))
            {
                ?><h2><center><?php
                echo "Stocks updated successfully!";
                ?></h2><center><?php
            }
            else
            {
                ?><h2><center><?php
                echo "Error- Couldn't update stocks from the table". "<br>". $sql.
                "<br>". $conn->error;
                ?></h2><center><?php
            }
        }   
    }
    mysqli_close($conn);
}

Table structure: portfolio2

CREATE TABLE `portfolio2` (
 `stockID` int(11) NOT NULL AUTO_INCREMENT,
 `stocks_symbol` varchar(30) NOT NULL,
 `price` decimal(30,2) DEFAULT NULL,
 `quantity` int(30) DEFAULT NULL,
 `memberid` int(11) NOT NULL,
 PRIMARY KEY (`stockID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

Answer 1

If you want to prevent users from adding the same stock twice, you can do it by creating a UNIQUE index:

ALTER TABLE `portfolio2` ADD UNIQUE `unique_idx`(`memberid`, `stocks_symbol`);