pset7 problem with buy and portfolios table

Question

Hello again!

I have a problem concerning my buy.php code. The whole site is ready to launch but I keep having a problem with this particular bug, that I seem to fix every time but it keeps coming back. I think there is a problem with this shared key thing where the user id and symbol must be unique. But whenever I buy a stock it is only updating the shares of one row without creating a new one, even if it is a new user. Here is my php code.

<?php

// configuration
require("../includes/config.php"); 

// if user reached page via GET (as by cliccking a link or via redirect)
if ($_SERVER["REQUEST_METHOD"] == "GET")
{
    // else render form
    render("buy_form.php", ["title" => "Buy"]);
}

// else if user reached page via POST (as by submitting a form via POST)
else if($_SERVER["REQUEST_METHOD"] == "POST")
{
    // checks if in both fields something was typed
    if (empty($_POST["shares"]) || empty($_POST["symbol"]))
    {
        apologize("You have to fill in both fields.");
    }

    // checks for non-negative integer
    if (!preg_match("/^\d+$/", $_POST["shares"]) || !is_numeric($_POST["shares"]))
    {
        apologize("The 'Shares' field has to be a non-negative integer.");
    }

    // finds stock information
    $symbol = strtoupper($_POST["symbol"]);
    $stock = lookup($symbol);

    if (!$stock)
    {
        apologize("Symbol does not exist.");
    }

    $usercash = CS50::query("SELECT * FROM users WHERE id = ?", $_SESSION["id"]);

    // calculates amount to be paid and checks if it exceeds users cash
    $amount = $_POST["shares"] * $stock["price"];
    if ($amount > $usercash[0]["cash"])
    {
        apologize("Not enough money in your account.");
    }

    // update portofolio table
    $insert = CS50::query("INSERT INTO portofolios (user_id, symbol, shares) VALUES(?, ?, ?) ON DUPLICATE KEY UPDATE shares = shares + VALUES(shares)", 
    $_SESSION["id"], $symbol, $_POST["shares"]);
    if ($insert == 0)
    {
        apologize("Could not buy stocks.");
    }

    // updates users cash
    $update = CS50::query("UPDATE users SET cash = cash - ? WHERE id = ?", $amount, $_SESSION["id"]);
    if ($update == 0)
    {
        apologize("Could not buy stocks.");
    }

    // update history
    $history = CS50::query("INSERT INTO history (user_id, bos, symbol, shares, price) VALUES (?, 'BUY', ?, ?, ?)", $_SESSION["id"], $stock["symbol"],
    $_POST["shares"], $stock["price"]);

    redirect("/");
}
?>

Below you can see the properties of my phpmyadmin database as well.

CREATE TABLE `portofolios` (
`id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`symbol` varchar(5) NOT NULL,
`shares` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`,`symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `portofolios`
--

LOCK TABLES `portofolios` WRITE;
/*!40000 ALTER TABLE `portofolios` DISABLE KEYS */;
INSERT INTO `portofolios` VALUES (0,9,'TIF',81),(1,9,'GOOG',10),    (2,9,'AAPL',20);
/*!40000 ALTER TABLE `portofolios` ENABLE KEYS */;
UNLOCK TABLES;

Thank you folks!

EDIT:

It seems that you have understood wrong my columns in the portofolios table. The id column is not identical to the user_id column and it is just a way of identifying the columns, that's why I have it as my primary key. This is what the documentation for this pset says. Could it be a problem, that I have not implemented AUTO INCREMENT for the id column?

Answer 1

Without seeing the details of your user table, or knowing if you have any foreign keys to that table, I can't be sure, but I'm fairly confident that your problem is in your primary key.

I'm guessing that the ID column is the userid and not a sequential ID number for rows in the table. If this is true, that means that this table only allows one row per user. The impact of this is that a user can only own one stock and all updates will happen to that row.

You also have a unique key for the combination of userid and symbol. This, or something like this, should probably be your primary key. Doing this should cure your problem.

Except, I just reread your question and am concerned about what you said - it only updates one row, even if it's a new userid? Are you saying that you're only getting one row in the portfolios table???? I would expect one row per userid, and that it would only update that user's row, but multiple user rows would exist. If there is only one row, there's something else going on. I would take a close look at how users are added and make sure they're getting unique entries in the ID column. Next, if you can create multiple users in the user table, I'd check to make sure that each user is getting a unique ID for their user_id in the portfolio table. Anyways, something is amiss. If you can't sort all this out, can you please add the phpmyadmin table creation info for your user table to the question, along with any foreign keys (if you have any), related to both tables?

Of course, if my assumptions are incorrect and you've done something really different, I could be really off, but we'll see. ;-)

Side notes: there are a couple of other issues.

Assuming that ID is actually the user id, having both ID and user_id as columns in the same table may not be a good idea. If the design is to have a unique system ID in the user table, the user_id is stored there in a column, but the unique ID is used everywhere else throughout the database, not the user_id, which could potentially be changed by the user later. Mostly, it's about not duplicating data in the database. (google database normalization for a thorough explanation of the concept.)

Next, you might want to add a foreign key from the portfolio table to the user table. For your purposes, a foreign key basically says that in order to add an ID to the portfolio table, it must already exist in the user table. (google foreign key).

Finally, the unique key is named user_id, also the name of a column. This could easily lead to confusion and problems later. The key should have a unique name, preferably something more descriptive, like maybe user_symbol.

Just some thoughts from a database engineer. ;-)

If this answers your questions, please click on the check mark to accept. Let's keep up on forum maintenance. ;-)