IF-ELSE using PHP and SQL

Question

I am creating a stock trading game with PHP and SQL. I have a 'buy' function that adds the stock id, user id and quantity to a table named 'ownedstocks'.

I am having trouble in implementing a simple 'If-Else' statement. Basically, what I want:

If the user id and the stock id of the stock being purchased are already exists in 'ownedstocks' table, then I just want to update the quantity. Else if no rows exist for the given user id and stock id, then I want to insert a new row.

I have the code but unsure of using IF-ELSE.

Thanks in advance!

<?php 
include_once 'header.php';
$user = $_SESSION['user'];
$id = $_SESSION['id'];  
$price = $_SESSION['price'];
$amount=$_POST['amount'];
$total = $amount*$price;
$balance = $_SESSION['balance'];
$con=mysqli_connect("localhost","root","usbw","stocktrading");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

            $newbalance = ($balance - $total);

            queryMysql("UPDATE ownedstocks
                            SET quantity = (quantity+$amount)
                            WHERE ownedstocks.userid = '$user' and ownedstocks.stockid = '$id'");

            queryMysql("INSERT INTO ownedstocks
                        VALUES ('$user', '$id', '$amount')");

            queryMysql("UPDATE members
                        SET balance = $newbalance
                        WHERE members.user = '$user'");


            echo("You just purchased $id costing $price per stock<br/><br/>
            You bought $amount stocks<br/><br/>
            To calculate your bill: $price x $amount which equals $total<br/><br/>
            You have just spent $total, your new balance is $newbalance <br/><br/>");

            ?>

mbarlocker · Accepted Answer · 2013-05-02 19:34:25Z

1

What you want is the MySQL INSERT INTO ON DUPLICATE KEY UPDATE function.

Basically:

INSERT INTO ownedstocks values (...) ON DUPLICATE KEY UPDATE amount = amount + '$amount'

answered May 2, 2013 at 19:34

mbarlocker

1,40611 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MalvEarp Over a year ago

This worked wonders, thanks! But only once I had added an index; CREATE UNIQUE INDEX user_stock ON ownedstocks (userid, stockid); Thank you!

Mike · Accepted Answer · 2013-05-02 19:36:52Z

0

Add unique on (user, stock_id):

ALTER TABLE `ownedstocks` ADD UNIQUE (
    `user` ,
    `stock_id`
);

Then do something like:

INSERT INTO ownedstocks
VALUES ('$user', '$id', '$amount')
ON DUPLICATE KEY UPDATE
`amount` = `amount` + '$amount';

answered May 2, 2013 at 19:36

Mike

24.5k14 gold badges84 silver badges93 bronze badges

Comments

forseta · Accepted Answer · 2013-05-02 19:38:06Z

0

You could use the mysqli.affected-rows variable http://php.net/manual/en/mysqli.affected-rows.php to achieve the IF statement.

answered May 2, 2013 at 19:38

forseta

894 bronze badges

Collectives™ on Stack Overflow

IF-ELSE using PHP and SQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related