php - Get the returned value from a prepared statement MySQL
Get the solution ↓↓↓I was trying to make a stored procedure where I want to get a random value on the database where the table name and other conditions of my query are dynamic (in stored proc params).
DROP PROCEDURE IF EXISTS `myproc`;
DELIMITER //
CREATE PROCEDURE `myproc`(IN `tablename` VARCHAR(50), IN `isShow` TINYINT(1), IN `isAdd` TINYINT(1))
BEGIN
SET @tname := tablename;
SET @isshow := isShow;
SET @isAdd := isAdd;
SET @pcode := '';
SET @s = CONCAT('SELECT t.pcode
FROM ', @tname, ' AS t
JOIN (SELECT ROUND(RAND() * (SELECT MAX(idx) FROM ', @tname,' )) AS id) AS x
WHERE
t.idx >= x.id AND
t.isShow = ', @isshow,' AND
t.isAdd = ', @isAdd,
' LIMIT 1');
checkhand: WHILE (!@pcode) DO
PREPARE sp_query FROM @s;
EXECUTE sp_query;
DEALLOCATE PREPARE sp_query;
LEAVE checkhand;
END WHILE ;
END //
DELIMITER ;
Since I use @var as tablename, I referred to this SO and create my SQL query inCONCAT
then run it asStored Procedure
. Right now, my tables has holes so what I did was to do aWHILE
loop until get a record with pcode value.
My problem was, it is still returning blank pcode which means my @pcode var doesnt get the return value of query. Honestly I don't have idea how to store the result of my query ran in prepared statement to a variable and return it as result of my stored procedure.
Any help will do. Please correct me. Not pro sql user. Thanks
My while loop supposed to do like this:
while (!t.pcode) {
// do query and check if i get t.pcode
return t.pcode
}
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: target class [commandmakecommand] does not exist.
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.