/*GOODS TABLE, WILL CONTAIN ALL GOODS*/
CREATE TABLE GOODS(
	ID INT(3) AUTO_INCREMENT,
	NAME CHAR(20) UNIQUE,
	IMGFILE CHAR(20),
	L_PRICE INT(4),
	S_PRICE INT(4),
	H_PRICE INT(4),
	C_PRICE INT(4),
	PRIMARY KEY(ID));
 
/*TRADERS TABLE, WILL CONTAIN ALL USERS AND THEIR DATA*/
CREATE TABLE TRADERS(
	ID INT(3) AUTO_INCREMENT,
	USERNAME CHAR(20) NOT NULL,
	PASSWORD CHAR(20) NOT NULL,
	MAIL CHAR(40) NOT NULL,
	CREDITS CHAR(20) NOT NULL,
	PRIMARY KEY(ID));
 
/*STOCKS TABLE, WILL CONTAIN EACH USER'S (TRADER) STOCK*/
CREATE TABLE STOCKS(
	GOOD_ID INT(3),
	TRADER_ID INT(3),
	AMMOUNT INT(10),
	AVG_BUY INT(4),
	FOREIGN KEY(GOOD_ID)
		REFERENCES GOODS(ID)
		ON DELETE CASCADE,
	FOREIGN KEY(TRADER_ID)
		REFERENCES TRADERS(ID)
		ON DELETE CASCADE);

/*THIS PROCEDURE ALLOWS THE TRADERS TO BUY STOCKS*/
CREATE PROCEDURE BUY_STOCK(P_GOOD INT,P_TRADER INT,P_AMMOUNT INT)
BEGIN
	DECLARE V_PRICE, V_COST INT;
	SELECT C_PRICE INTO V_PRICE 
		FROM GOODS 
		WHERE ID = P_GOOD;
 
	SET V_COST = V_PRICE*P_AMMOUNT;
 
	IF HAS_CREDITS(P_TRADER,V_COST) THEN
		IF HAS_STOCK(P_GOOD,P_TRADER) THEN
			UPDATE STOCKS
				SET AMMOUNT = AMMOUNT + P_AMMOUNT
				WHERE GOOD_ID = P_GOOD 
				AND TRADER_ID = P_TRADER;
			UPDATE STOCKS
				SET AVG_BUY = ((AVG_BUY*(AMMOUNT-P_AMMOUNT))+(V_PRICE*P_AMMOUNT))/AMMOUNT
				WHERE GOOD_ID = P_GOOD 
				AND TRADER_ID = P_TRADER;
		ELSE
			INSERT INTO STOCKS
				VALUES(P_GOOD,P_TRADER,P_AMMOUNT,V_PRICE);
		END IF;	
		UPDATE TRADERS
			SET CREDITS = CREDITS - V_COST
			WHERE ID = P_TRADER;
	END IF;
END;

<?php
	session_name('trademaster');
	session_start();
 
	if($_POST['transaction'] = "Buy")
	{
 
		$good = $_POST['goodid'];
		$id = $_SESSION['id'];
		$ammount = $_SESSION['ammount'];
		$db = new PDO("mysql:host=localhost;dbname=trademaster", 'trader', '********');
 
		$x = $db->prepare('CALL BUY_STOCK(:good, :trader, :ammount)');
 
		$x->bindValue(':good', $good, PDO::PARAM_INT);
		$x->bindValue(':trader', $id, PDO::PARAM_INT);
		$x->bindValue(':ammount', $ammount, PDO::PARAM_INT);
 
		$x->execute();
 
		unset($db);
 
		header("location:stocks.php");
		exit();
	}
?>

PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1370 execute command denied to user 'trader'@'localhost' for routine 'trademaster.BUY_STOCK'

'CALL BUY_STOCK(:good, :trader, :ammount)'

'CALL BUY_STOCK(1,1,10)'

$x = $db->prepare('CALL BUY_STOCK(?,?,?)');
$x->bindParam(1, $good, PDO::PARAM_INT);
$x->bindParam(2, $id, PDO::PARAM_INT);
$x->bindParam(3, $ammount, PDO::PARAM_INT);
 

'CALL BUY_STOCK(?,?,?)'