Stored procedure works in localhost but not in server

This bunch of codes work greatly on localhost(homestead virtual machine and mysql). Actually, the stored procedure code was written in mysql workbench but when I migrate the code to the online version, it does not work and I don’t see any error it output. This is the code below

DELIMITER $
CREATE DEFINER=`homestead`@`%` PROCEDURE `catalog_get_products_on_catalog`(
IN inShortProductDescriptionLength INT,
IN inProductsPerPage INT, IN inStartItem INT)
BEGIN
PREPARE statement FROM
"SELECT p.id, p.name,
IF(LENGTH(p.description) <= ?,
p.description,
CONCAT(LEFT(p.description, ?),
'...')) AS description,
p.price, p.discounted_price, i.name AS img_name
FROM products p, imgs i
WHERE display = 1 OR display = 3
AND p.id = i.product_id
ORDER BY display DESC
LIMIT ?, ?";
SET @p1 = inShortProductDescriptionLength;
SET @p2 = inShortProductDescriptionLength;
SET @p3 = inStartItem;
SET @p4 = inProductsPerPage;
EXECUTE statement USING @p1, @p2, @p3, @p4;
END$
DELIMITER ;

What might be the possible problem? Is it because it was created with mysql workbench and I created it with phpmyadmin in the cpanel? And how do I fix it

this is not gonna work the way you think –

WHERE display = 1 OR display = 3 AND p.id = i.product_id

it is evaluated as

WHERE ( display = 1 ) OR ( display = 3 AND p.id = i.product_id )
1 Like

Oh thanks

you’re welcome

do me a favour, change this –

FROM products p, imgs i
WHERE display = 1 OR display = 3
AND p.id = i.product_id

to this –

  FROM products AS p
INNER 
  JOIN imgs AS i
    ON i.product_id = p.id
 WHERE p.display IN ( 1 , 3 )