I have written a small web app using php. I am now trying to replace my SQL written directly in the php scripts with calls to stored procedures.
Example
I have replaced:
$GetIDforInsertSQL=("SELECT idUser FROM user WHERE userName='$username'");
With:
$GetIDforInsertSQL="CALL Get_User_ID('$username')";
Here is the code for Get_User_ID
CREATE DEFINER=`root`@`localhost` PROCEDURE `Get_User_ID`(IN UserNametoCheck varchar(45) )
BEGIN
SELECT idUser FROM user
WHERE userName=UserNametoCheck;
END
When I run this query and extract the result set i.e.
$result=($conn->query($GetIDforInsertSQL));
while($row=mysqli_fetch_array($result))// Get the Userid from the users table to insert into watchlist
{
$Userid=$row['idUser'];
echo $Userid;
}
It echos out the expected value of $Userid which is 8.
However, further down in my script I have another query
$AddToWatchlistSQL=("INSERT INTO watchlist(UserID,AdvertID)VALUES('$Userid','$Advertid')");
followed by:
$results=($conn->query($AddToWatchlistSQL));
if($results)
{
echo" Advert Added to watchlist, Redirecting";
header('Refresh: 3; "Indexlogged.php');
}
else
{
echo "Thats an error";
}
Since I have replaced the Select state to get the userId with the stored procedure Get_User_ID I keep getting the error message for the insert query.
When I comment out the stored procedure call and just use the original query:
$GetIDforInsertSQL=("SELECT idUser FROM user WHERE userName='$username'");
It works, I don't understand why as when I echo the value of $Userid after the stored procedure call it outputs the expected result. Any Ideas as to why this is happening?
Edit: Output of log file after attempting insert using stored procedure:
160421 18:35:30 9 Connect root@localhost as anonymous on carhubdb
9 Query SELECT * FROM caradvert WHERE Advert_ID='13'
9 Query SELECT * FROM user WHERE userName='John123'
9 Query SELECT AdvertImagePath FROM advertimage WHERE Advert_IDENTIFIER='13'
9 Query SELECT userName FROM user WHERE idUser='9'
9 Query SELECT * FROM watchlist WHERE UserID='8' AND AdvertID='13'
9 Quit
160421 18:35:32 10 Connect root@localhost as anonymous on carhubdb
10 Query CALL `carhubdb`.`Get_UserID`('John123')
