Ryan R Posted August 31, 2011 Share Posted August 31, 2011 Hi all, I am sure some of you already checked my two previous post about inventory and the error issue on billing.php. You can just ignore it. I figured it out. Thanks for your efforts and attentions to my past posts. Today, I have a just simple question about stored procedures. I recently bought a hosting service from Godaddy.com and the version of PHP is 5.2 and MySQL is 5.0. I try to run this stored procedure: DELIMITER $$ CREATE PROCEDURE select_categories (type VARCHAR(6)) BEGIN IF type = 'coffee' THEN SELECT * FROM general_coffees ORDER by category; ELSEIF type = 'other' THEN SELECT * FROM non_coffee_categories ORDER by category; END IF; END$$ DELIMITER ; But it gives an error : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1 Interestingly, other stored procedures are working really well... Can anyone give me a good advice for this issue? Thanks again for your help. Link to comment Share on other sites More sharing options...
Larry Posted August 31, 2011 Share Posted August 31, 2011 To be clear, you're not trying to run that stored procedure but actually create it. And you don't say how you're trying to create it (i.e., using what interface), but if you're using phpMyAdmin, it has a checkbox for changing the delimiter, so that you don't use the delimiter line. Link to comment Share on other sites More sharing options...
Ryan R Posted August 31, 2011 Author Share Posted August 31, 2011 Thank you very much, Larry. Yes, I am using phpMyAdmin. I am sorry but I will need to ask you where the check box is... Only thing I can find is that there's a box for delimiter and in that box, semicolon is typed in as a default. Should I change this to $$? Link to comment Share on other sites More sharing options...
Larry Posted August 31, 2011 Share Posted August 31, 2011 Yes, that's correct. Do that. I must have misremembered it. Please let us know if that works for you. Link to comment Share on other sites More sharing options...
Ryan R Posted September 2, 2011 Author Share Posted September 2, 2011 Thank you very much for your reply, Larry I tried "select_categories" stored procedure by typing $$ on the delimiter blank box. But it still doesn't work. The error message is: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1 Interestingly, When I check "select_categories" stored procedure on the phpmyadmin, it shows me that it exists. So I made a test web page in which I put a query that called this stored procedure. When I put it on my server, it didn't work... I guess that "select_categories" procedure isn't working but somehow it shows it's existing in phpmyadmin. Thanks again for your efforts and helps. I look forward to hearing from you soon. Link to comment Share on other sites More sharing options...
Larry Posted September 2, 2011 Share Posted September 2, 2011 What do you mean you "tried" the stored procedure? Do you mean you tried to create it or tried to execute it? Also, please do me a favor and drop the "I look forward to hearing from you soon." Link to comment Share on other sites More sharing options...
HartleySan Posted September 5, 2011 Share Posted September 5, 2011 Ryan R, I was dealing with an annoying hosting company the other day and their version of phpMyAdmin. They had all sorts of restrictions on even letting me create stored procedures in the first place. After contacting them and getting the proper permissions, I tried again, but got the same error message as you, yet the procedures were actually created. The real kicker though is that even though the procedures were created, I couldn't actually use them. I guess the moral of the story is that I have no clue what was going on, but time was an issue, so I just switched everything to prepared statements, which run on the PHP side and are guaranteed to work. Link to comment Share on other sites More sharing options...
Ryan R Posted September 13, 2011 Author Share Posted September 13, 2011 Thanks HartleySan, It is good to know. Maybe I should do that according to what you recommended me. Larry - I am sorry. I won't use that last word again. I created the stored procedure and executed it. But it didn't work. Like HartlySan mentioned above, I am having the exact same issue as his. I guess I will need to switch them to prepared statements. Link to comment Share on other sites More sharing options...
Larry Posted September 14, 2011 Share Posted September 14, 2011 The error you posted above seems to suggest that you saw that error when trying to create the stored procedure. I'd be curious to see what error occurs when you execute the stored procedure. In my experience, stored procedure support in phpMyAdmin is buggy and can vary greatly from one version of phpMyAdmin to another. But if you want to use stored procedures, you shouldn't let that stop you. If you want to give up on the stored procedures, on my main site there are a bunch of posts on converting the stored procedures to standard PHP-MySQL. Link to comment Share on other sites More sharing options...
Recommended Posts