Avatar of GarySB
GarySB
 asked on

PHP & Stored Procedures not working anymore

My stored procedures and php files worked great.
My web hosting company upgraded servers to:
PHP version 5.2.9
MySQL version 5.0.96-community
and now the same exact code that worked before returns nothing to my VB 6 program.  I was able to tell that one of the php instructions it returns an error on is:    declare MyVarible varchar(5);   this worked before the upgrade.

Using the same code i created the procedure with now returns a syntax error on the declare statement above.  How should this be re-written ?
Does a guide exist that you know of that will show changes to make in this newer version ? Thank You
MySQL ServerPHP

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
Terry Woods

Which version did you upgrade from? There is a guide here if you upgraded from 4.1: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-previous-series.html

Is it PHP or MySQL returning the syntax error? The "PHP instruction" you quoted is MySQL rather than PHP, though it may be code in PHP that it's trying to run through MySQL.
GarySB

ASKER
Your correct, that instruction is from my stored procedure routine.  My company cannot tell me what version it was before, but judging from the existing syntax that worked I was hopping you could tell.
GarySB

ASKER
excuse my typo
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Terry Woods

It may help to copy and paste more of the code surrounding that line, and also the error message.
GarySB

ASKER
Below a decision is made      
case cntstats
when 0 then programtype.product_name
else concat (programtype.product_name, char(65), cntstats)

It always comes up as zero even when a valid email is provided.  The ELSE should really execute.

My procedure as it used to work.

delimiter $$
create procedure get_lessons (user_email varchar(96), userrange as integer)

begin
declare cntstats varchar(5);

select count(customer.customerid) into cntstats
from customer,
orders,
orderproduct,
programtype
where
customer.customerid=orders.customerid and
orders.orders_id = orderproduct.orders_id and
orders.ordersvalue = 2 and
customer.useremailaddress = user_email;

select
programtype.productid as lid,
case cntstats
when 0 then programtype.product_name
else concat (programtype.product_name, char(65), cntstats)
END as mytitle,
programtype.product_data as ltext
from customer,  
programtype,
product
where
customer.useremailaddress = user_email and
programtype.productid > userrange-26 and
programtype.productid < userrange and
programtype.productid = product.productid and
product.productvalue=1;
END
$$
ste5an

First of all, check whether your stored procedures work. Use the Work Bench, Toad or a MySQL managment tool of your choice to verify this. Use the strict mode to hunt down subtle bugs in your code.

Have you also verified that the necessary PDO libs (or what data access do you use) was also properly updated? Have you also verified the configuration for your data access?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ray Paseur

Run this script, shown here in its entirety, on both the old system and the new system.  Compare the outputs.  It's a lot of information to sift through, but it will at least tell you the differences.  Once we know the differences we can advise on how to get the new installation up to your requirements.
<?php phpinfo();

Open in new window

Also, why in the world would anyone be "upgrading" to a PHP 5.2 release?  PHP 5.2 is not supported at all any more (has not been for several years) and is no longer even considered for security fixes.  In other words, PHP 5.2 is obsolete.  The current versions of PHP are listed right on the PHP.net home page.  PHP 5.5+ is current.  I believe that MySQL 5.7+ is current.
Terry Woods

Ray, we're dealing with a hosting business here; they are probably under pressure from customers to move slowly on upgrades because it means work for the customers in fixing their code (even though more work might be needed by other customers who suffer data loss or other issues by running an old version... often those wanting the status quo seem to be given priority in my experience!).

Anyway, that also means the old version won't be available to test on.
ASKER CERTIFIED SOLUTION
Terry Woods

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ray Paseur

Wow, I don't know what to say.  Maybe, "Good luck!"
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes