I have what I hope is a simple question involving creating the SELECT query with a string parameter in a MySQL stored procedure and subsequently passing a CALL query from within PHP. This is from a login page and is therefore calling the procedure for login. 'Wowcrofty' in the error is the username. The stored procedure I am using is included below.
DELIMITER //
CREATE PROCEDURE retrieve_user_info (IN username VARCHAR(50))
BEGIN
SELECT * FROM db.dbo WHERE user_username=username;
END //
DELIMITER ;
This is how I am calling the procedure from PHP.
$result = $conn->query("CALL retrieve_user_info($enteredusername)");
I have loaded the page containg the query and this is the error I am receiving.
Uncaught mysqli_sql_exception: Unknown column 'wowcrofty' in 'field list'
I have created a few other stored procedures and they are working perfectly. However, this is the first one that uses a string as a parameter. What am I doing wrong? Thank you for any suggestions.
Quote the parameter:
query("CALL retrieve_user_info('$enteredusername')");
Or better, use the library to prevent SQL injection:
stmt = $connection->prepare("CALL retrieve_user_info(?)");
$stmt->bind_param("s", $enteredusername);
$stmt->execute();
$result = $stmt->get_result();