Im having some trouble running a MySQL function with PHP, its actually a series of 'DROP FUNCTION IF EXISTS' and 'CREATE FUNCTION' statements followed a query that uses the functions.
The whole thing runs in bare MySQL where i also use 'DELIMITER' statements (as is the way).
In PHP I've tried running the whole thing, function declarations and then query together as one big string which lead me to understand that i wouldn't need the 'DELIMITER' statements but when php gets to executing the query, it still cant access the functions.
Ive also tried using 'mysqli_multi_query()' in the same way and running each of the queries separately on the same link, but in the end i get the same errors.
I wont post my whole code, i have a feeling that Im doing something fundamentally wrong, my drop / function declarations look like :
DROP FUNCTION IF EXISTS PQTY
CREATE FUNCTION PQTY (v_product_id INT,v_p_qty INT, v_option_id INT, v_option_value_id INT) RETURNS INT
NOT DETERMINISTIC
BEGIN
DECLARE qty INT;
SELECT products_stock_quantity INTO qty
FROM products_stock
WHERE products_stock_attributes = CONCAT(v_option_id,'-',v_option_value_id)
AND products_id = v_product_id;
IF ISNULL(qty) THEN
RETURN v_p_qty;
ELSE
RETURN qty;
END IF;
END
and the main query :
SELECT PQTY(p.products_id,p.products_quantity, po.products_options_id, pov.products_options_values_id) AS 'qty'
FROM products p
LEFT JOIN products_description pd
ON p.products_id = pd.products_id
LEFT JOIN manufacturers m
ON m.manufacturers_id = p.manufacturers_id
LEFT JOIN products_attributes pa
ON p.products_id = pa.products_id
LEFT JOIN products_options po
ON po.products_options_id = pa.options_id
LEFT JOIN products_options_values pov
ON pov.products_options_values_id = pa.options_values_id
LEFT JOIN products_options_text pot
ON pot.products_options_text_id = po.products_options_id
Like i said, in PHP I've tried running each statement separately but on the same link and also as one big string and I've tried up till now using both the legacy mysql functions and mysqli functions (the procedural ones).
Any help would be greatly appreciated.
