Stored Procedures Not Working

[jwarrentx]

I'm having a problem on one of the servers that I am using the stored procedures on. I have had luck with another (older) version of php and MySQL, but the following stored procedure won't work on the newer version. Any ideas:

 

 

 

CREATE PROCEDURE `addToCart`(uid CHAR(32), pid MEDIUMINT, sid VARCHAR(6), qty TINYINT)

BEGIN

DECLARE cid INT;

SELECT cartId INTO cid FROM carts

WHERE user_session_id=uid AND productsId=pid AND sizeId=sid;

IF cid > 0 THEN

UPDATE carts SET quantity=quantity+qty, date_modified=NOW() WHERE cartId=cid;

ELSE

INSERT INTO carts (user_session_id, productsId, sizeId, quantity) VALUES (uid, pid, sid, qty);

END IF;

END

 

The problem seems to be in user_session_id=uid and sizeId=sid. If I use sizeId='$sid', the sizeId works. But if I use user_session_id='$uid', it doesn't. This is the error I get if I use the code above:

 

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

An error occurred in script '/home/baertrax/public_html/eshop/cart.php' on line 87:

mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

 

On one server that is working, I am using:

PHP Version 5.2.14

MYSQL 5.0.77

 

On another server where it is not working, I am using:

PHP Version 5.2.17

MySQL 5.1.56

[Jonathon]

Yes Andrew I think had the solution for this. Search for illegal in the forum and you'll see the fix. I'd send you the link but I'm on my phone.

[jwarrentx]

I found it, and it worked. I had to change the sid to CONVERT(sid USING utf8) COLLATE utf8_general_ci also. I want to build code that is reusable for other shopping carts. Will this work in the older version of MySQL that I am using, or will I have to have two different versions of the Stored Procedures?

[Jonathon]

I'm not really sure on the ins and outs of it. I think I had to use it on an I'd field which was an INT so I found that strange as its just a number. Sorry I can't be more help, but at least it works.