AndrewT

What you need to do before trying to fix your Stored Procedure and re-upload it is run this query: DROP PROCEDURE IF EXISTS select_categories; You can replace "select_categories" with any procedure name. To be specific, that code needs to be ran on the database that you have all your tables and stored procedures on. You can do this in phpMyAdmin simply by selecting the database on the left side, then hitting the "SQL" tab and running the query. There are other ways as well, but that is how I do it. That query will destroy the procedure from your database, allowing you to fix any code in it and re-create the procedure. Hope this helps! The first time I got that error, it was because the user I was using in mysql.inc.php to run the stored procedures didn't have EXECUTE permission on the database. You may have that permission, but it is something to check just to make sure.

Well I fixed the problem. The problem was that for whatever reason, the 'uid' was being read as utf8_unicode_ci instead of utf8_general_ci. So wherever it mentioned 'uid' I changed it to read CONVERT(uid USING utf8) COLLATE utf8_general_ci and it fixed it. Now the queries run just fine. Only problem is the cart stays empty haha >.> I will work on that problem for a while though before I ask for help. Thanks Larry!

I have checked all the columns and they are all set to utf8_general_ci. However when I run the procedure I still get the Illegal Mix of Collations error. Here is the exact code in the procedure: DELIMITER $$ CREATE PROCEDURE add_to_cart (uid CHAR(32), type VARCHAR(6), pid MEDIUMINT, qty TINYINT) BEGIN DECLARE cid INT; SELECT id INTO cid FROM carts WHERE user_session_id=uid AND product_type=type AND product_id=pid; IF cid > 0 THEN UPDATE carts SET quantity=quantity+qty, date_modified=NOW() WHERE id=cid; ELSE INSERT INTO carts (user_session_id, product_type, product_id, quantity) VALUES (uid, type, pid, qty); END IF; END$$ DELIMITER ; Sorry it took so long to get back, I was out of town for the weekend.

I will look in the information.schema and see what exactly all the table columns in that query are using for collation. How do I check to see what collation the PHP script is communicating in? I assumed it was utf8_general_ci.

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' That is the exact error I get when I try to add a product to the cart. I added the testing code to the "add_to_cart" call, and the above error is displaying. $r = mysqli_query($dbc, "CALL add_to_cart('$uid','$sp_type',$pid,1)"); if(!$r) echo mysqli_error($dbc); What would be causing this? Obviously it is something that I am doing wrong, or else the forums would be blown up with posts asking this question. I Google'd it and got quite a few vague answers. Any ideas? If anyone needs anything else to figure out the problem (code snippets, explanations) just ask.

EDIT: FIXED A few things were wrong but I basically took the code and wrote it out word for word using my own tables and table abbreviations, and got it working. I am trying to figure out why the "select_sale_items" function is only returning items with the product_category of 'other'. To be clear, it returns only prodcuts with a product_category of 'other' no matter whether it is set to 'true' or 'false'. I have posted my SQL code below. I have changed a few of the table names, and instead of "coffee" the database keeps track of another product, so where CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole) would be, I simply have s.size Hopefully someone will be able to tell me what I am doing wrong. DELIMITER $$ CREATE PROCEDURE select_sale_items (get_all BOOLEAN) BEGIN IF get_all = 1 THEN SELECT CONCAT("O", ncp.id) AS sku, sa.price AS sale_price, ncc.category, ncp.image, ncp.name, ncp.price, ncp.stock, ncp.description FROM sales AS sa INNER JOIN soap_products AS ncp ON sa.product_id=ncp.id INNER JOIN soap_categories AS ncc ON ncc.id=ncp.soap_category_id WHERE sa.product_type="other" AND ((NOW() BETWEEN sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) ) UNION SELECT CONCAT("C", sc.id), sa.price, gc.category, gc.image, s.size, sc.price, sc.stock, gc.description FROM sales AS sa INNER JOIN specific_products AS sc ON sa.product_id=sc.id INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_products AS gc ON gc.id=sc.general_products_id WHERE sa.product_type="coffee" AND ((NOW() BETWEEN sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) ); ELSE (SELECT CONCAT("O", ncp.id) AS sku, sa.price AS sale_price, ncc.category, ncp.image, ncp.name FROM sales AS sa INNER JOIN soap_products AS ncp ON sa.product_id=ncp.id INNER JOIN soap_categories AS ncc ON ncc.id=ncp.soap_category_id WHERE sa.product_type="other" AND ((NOW() BETWEEN sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) ) ORDER BY RAND() LIMIT 2) UNION (SELECT CONCAT("C", sc.id), sa.price, gc.category, gc.image, s.size FROM sales AS sa INNER JOIN specific_products AS sc ON sa.product_id=sc.id INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_products AS gc ON gc.id=sc.general_products_id WHERE sa.product_type="coffee" AND ((NOW() BETWEEN sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) ) ORDER BY RAND() LIMIT 2); END IF; END$$ DELIMITER ; EDIT: I have realized that, due to my table structure, 'other' and 'coffee' should be switched. I have adapted the tables for a different product. I was also wondering why it was displaying the sale price for the 'other' products, but the info and image of the 'coffee' product. But I believe that is because I haven't switched the 'other' and 'coffee' like I said. I have class, but I will try that when I get home. However, I still have the original problem above.

I believe that, for learning purposes, the other folders that require some form of security are secured via .htaccess. When you have an index.html file inside of a folder that contains just images, it isn't a huge thing because people won't be able to mess with your server if they happen upon some images. However for sections such as the admin you need more security. I could be totally off base with this though. This isn't to say that you can't put an index.html in the other folders if you would so choose to. It won't hurt anything I don't believe.

I agree with Jonathan. I am using HostGator right now and have had a great experience so far. I have contacted support a couple times and every time the response was extremely fast, and extremely helpful. On the flip side, I would avoid 1and1 like the plague. In my opinion they are more concerned about roping you in than keeping you. They have a custom control panel that is lightyears behind cPanel, and they have just about everything locked down tight. Good host if you use their template generator I imagine, but when you have to jump through hoops just to set up a MySQL database you should look elsewhere.

To connect remotely to my server I have to log into the cPanel and add my IP to the hosts list. And since my IP is changing all the time (student) I delete the IP from the list as soon as I am done. I figured out the problem though. The user that I am using in the mysql.inc.php file to log into the database to display categories and products didn't have permission to execute Stored Procedures. All I had to do was give that user Execute permission and the script works flawlessly now. So it turns out Jonathan was right, I just misinterpreted his post. I thought he meant do I have the permission from the administrator perspective, not do I have the permission set on the user logging in. My bad!

I have a local installation of Xampp for testing some PHP scripts, and I believe that I can connect to my remote server using the MySQL command line installed with Xampp. I will Google it and try. I also might install a standalone MySQL administration client and connect remotely. I am hoping that the error with my PHP script is something simple and I wont have to go re-write all the Stored Procedures haha. Thanks for the help! I will update with what the problem is when I figure it out.

Yeah I have full permission on the database server. Here is the error I get when I try to run a stored procedure: #1312 - PROCEDURE andrew_pss.select_categories can't return a result set in the given context It auto appends andrew_pss (the database name) to the procedure. I have tried it from just about everywhere inside phpMyAdmin and I still get that error. I get that error on every procedure as well.

I am using this book currently as a guide of sorts to create an e-commerce store that doesn't sell coffee. So far, I have only modified the SQL here and there. To be specific, I am creating a store that sells handmade soap (long story). I have modified the SQL so that the tables, instead of having "coffee" in the name, have "soap" in the name. Also, instead of passing "goodies" or "coffee" to the "select_categories" procedure in shop.php, it will pass either "goodies" or "soaps". I have changed the corresponding stored procedures accordingly when I inserted them. Here is my problem. When I test the script shop.php, I get the error "mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given". I have troubleshooted for a while now and figured I would post on the forums in the hope that someone could help me out. I haven't been able to test the Stored Procedures as I am using phpMyAdmin and get errors when I try to run stored procedures. I created them fine, but I can't test them, at least not in phpMyAdmin. Is there a way to make sure they work outside of phpMyAdmin? My guess is that the stored procedure is getting screwed up somehow, and so it is returning "false" instead of a number of rows returned. If anyone has any questions to better understand my code or anything, just let me know and I will be happy to do anything to get this problem resolved. Thanks!