pset7 index, portfolio table problem

Question

Cleaned up question to make it easier to understand :)

In my Portfolio table, whenever I buy or sell shares, the shares update in a single row, whose ID is 0. Even if I log in with a different username, the shares update here, even when I buy shares with a different symbol. Basically my Portfolio table looks like this right now (It only has one row) :

Id = 0 ; User_id = 11; Symbol = F; Shares = 30

This is my index.php code:

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

$positions = [];
$rows = CS50::query("SELECT * FROM portfolio WHERE user_id = ?", $_SESSION["id"]);
foreach ($rows as $row)
{
    $stock = lookup($row["symbol"]);
    if ($stock !== false)
    {
        $positions[] = [
            "name" => $stock["name"],
            "price" => number_format($stock["price"], 2, '.', ''),
            "shares" => $row["shares"],
            "symbol" => strtoupper($row["symbol"])
        ];
    }
}
$users = CS50::query("SELECT * FROM users WHERE id = ?", $_SESSION["id"]);
$balance = $users[0]["cash"];

render("portfolio.php", ["positions" => $positions, "title" => "Portfolio", "balance" => $balance]);

And this is how my Portfolio and Users tables are laid out:

Portfolio

ID: int(10), UNSIGNED, Primary

User_id: int(11), Unique

Symbol: varchar(255), Unique

Shares: int(11)

Index

ID: int(10), UNSIGNED, Primary

Username: varchar(255), Unique

Hash: varchar(255)

Cash: decimal(65,4), UNSIGNED

There are other problems too but hopefully I can figure them out once this one is sorted. Thanks for any help at all :)

Answer 1

After looking at the original question and the edited version, I think I see a couple of issues, but there's still missing information. First, I'm assuming that in the edited question, the table title of index was supposed to mean that the info that followed was a description of the USERS table.

The first thing that makes me suspicious is this:

$rows = CS50::query("SELECT * FROM portfolio WHERE user_id = ?", $_SESSION["id"]);

The implication from reading all of your code and question is that this uses $_SESSION["id"] for the userid. In my code and everything I've ever looked at for this pset, this field doesn't map to the user_id column, it maps to the ID column in/from the USER table. Assuming that you're following suit, this query would be in error.

On a related note, I'm concerned about your user table setup. The ID column takes an unsigned int and is the primary key. What I don't see is any mention of whether it is also automatically assigned. If not, this is likely to cause problems. The ID column should assign sequential numbers automatically as rows are added/inserted.

Next, on the portfolio table, the ID column is again the primary key. This may cause severe problems. It can be complicated. This issue depends on what the ID column is used for. If it is merely a sequentially assigned number for each row inserted, then it isn't a problem. BUT, this column is not used for any other purpose, particularly to map back to the ID column in the USER table.

On the other hand, if this ID column maps back to the ID column in the USER table, it's a big issue. This table is supposed to have rows based on both the user and the stock symbol. The combination of these two needs to be the primary key (or if there is no primary key, at least be a unique key on both columns). Is the user_id column in this table mapped back to the user_id or ID column in the USER table? If you make ONLY the ID column the primary key and the ID column is mapped back to the USER table, there can only be one row for each user, so there can only be one stock owned by any given user. Further, making the userid column unique will have the same effect. There needs to be a unique key composed of two columns - one that maps back to the user and one that contains the stock symbol.

Next, I would question what the actual value is in $_SESSION["id"]. Have you verified what value is there? Is it 0?

You might also find it useful to use alpha user_id names instead of numbers - maybe fred or george? By using numbers for both, it's going to be more difficult to distinguish where they are coming from and going to.

Have you taken a good look at the contents of the user table?

Now, here's a lesson they didn't give in the class. It has a lot to do with the earlier discussion about mapping user_id's. In database engineering, there's a concept called normalization. It's a big concept that you can google at your leisure. Despite the fact that the instructions recommend it, having the user_id column in the portfolio table as well as in the user table is generally not a good idea. Duplicating data is error-prone and is usually only done for performance reasons. Instead, consider this. The primary key in the user table is the ID column, not the user_id column. In that table, it is necessary for both columns to be unique to prevent cross-linking data. However, the ID column will link the user's data across all tables in the database that have user data such as stocks bought, sold, held, etc. The primary key of the ID column in the user table becomes a foreign key for any other table that needs to correlate data to a particular user. (You can google foreign key too. )

OK, so if you're not too confused, this should give you a few ideas of where to look. ;-)

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