0

After a few tries and looking for some answers online, I'm a bit stuck. I'm not sure where, or how exactly I'm supposed to enter the SQL statements and how to make it so that the linker table has IDs from the portfolio and members tables inserted into it, and so that then the website can then display that info. Not sure if that's clear enough so let me explain what exactly I want to achieve with this. I wanted to be able to display the stock records for a single user, including the stock symbol and the amount of shares owned by that user. While I have created all the tables and linked them properly (I think), I've no idea how to go about actually inserting and then retrieving the records into/from the proper places with php. I have a linker table with stockID & memberID which act as foreign keys and then obviously the same primary keys in portfolio & members tables. For example, when adding stocks to a portfolio, I have this statement:

if(empty($_POST['var1']) or empty($_POST['pri1']) or empty($_POST['vol1']))
{
?><h1><center>To add values please fill atleast first row completely.</center></h1><?php
die();
}

for($x=1;$x<=5;$x++)
{
$var = [];
$pri = [];
$vol = [];
if (!empty($_POST['var'.$x]) and !empty($_POST['pri'.$x]) and !empty($_POST['vol'.$x])) 
{
$var[$x] = $_POST['var'.$x];
$pri[$x] = $_POST['pri'.$x];
$vol[$x] = $_POST['vol'.$x];
$sql = "INSERT INTO portfolio 
(stocks_symbol, price, volume)
VALUES ('$var[$x]', $pri[$x], $vol[$x])
ON DUPLICATE KEY UPDATE
price=$pri[$x], volume=$vol[$x]";

// Check if values are added successfully
if(mysqli_query($conn, $sql))
{
?><h1><center><?php
echo $x.". Values added.";
?></h1><center><?php
}
else
{
?><h3><center><?php
echo $x.". Error adding values to table". "<br>". $sql.
"<br>". $conn->error;
?></h3><center><?php
}
}
}
mysqli_close($conn);
}

But as you can see, it's adding a record to the portfolio table and doesn't mention the user ID anywhere, which means that the IDs are not linked and the linker table isn't involved... And so that portfolio is being displayed to everyone, no matter what user is logged in. As for then displaying that data in a table for a user, I have this:

$sql = "SELECT * FROM portfolio";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) 
{
while($row = mysqli_fetch_assoc($result)) 
{
$sym[$y] = $row["stocks_symbol"];
$pri[$y] = $row["price"];
$vol[$y] = $row["volume"];
$y += 1;
}
}
// If database empty
else 
{
?><h1><center><?php
echo "Portfolio Empty";
?></h1></center><?php
die();
}
mysqli_close($conn);

I'm not sure if I'm wording this correctly but hopefully you'll understand what I mean and would really appreciate some help/guidance as I couldn't find a solution to this.

Frenzyy
  • 3
  • 3
  • where is the ID ? or which defination equal to ID ? – Ivan Barayev Nov 19 '17 at 19:43
  • Your `INSERT` statement says to insert the following values `stocks_symbol, price, volume`. If you want a user ID in there too then you need to specify that. Foreign keys are for values which are the same across tables, you should add a user ID column to this table and use that as a foreign key from your users table – miknik Nov 19 '17 at 19:54
  • ID for a stock is added automatically. As for the userID, I'm not sure where to include it, that's the problem for me because if I add it to the statement "INSERT INTO portfolio ..." that will lead to duplicate data due to a user being able to have multiple investments. This is the part I'm not sure about... How to basically use that third table- linker, to store and later retrieve the appropriate data. – Frenzyy Nov 19 '17 at 19:55
  • Add a userID column to portfolio, it doesn't have to contain a unique value, then you insert the userid next to each transaction and when you want to display just the investments for the current user you can include their userID in your database query like this `SELECT * FROM portfolio WHERE userID = 1` – miknik Nov 19 '17 at 20:05
  • Thanks! It worked. – Frenzyy Nov 26 '17 at 11:17

0 Answers0