convert sql code into php code

[lordrt]

Hello all, I have to convert the following code into php code:

 

create procedure ImportArticles(ArtNo int(10), ArtDesc varchar(300), ArtBody varchar(300), Price Decimal(6,2), ImageName varchar(50), ImagePath varchar(100), Op int(1))

begin

declare xnid int(10);

declare xfid int(10);

declare xvid int(10);

set xnid = 0;

set xfid = 0;

set xvid = 0;

case Op

when 0 then

begin

select count(nid) into xnid from uc_products where ArticleNo  = ArtNo;

if xnid > 0 then

begin

select nid into xnid from uc_products where ArticleNo = ArtNo;

update uc_products set sell_price = Price where nid  = xnid;

update node_revisions set title = ArtDesc, body = ArtBody, teaser = ArtBody where nid = xnid;

if ImageName <> '' then

begin

select distinct field_uc_image_fid into xfid from files f, content_type_product ctp where ctp.nid = xnid;

if xfid > 0 then

update files set filename = ImageName, FilePath = ImagePath where fid = xfid;

end if;

end;

end if;

end;

else

begin

select max(nid) into xnid from node;

set xnid = xnid + 1;

insert into node(nid, vid, type, language, title) values(xnid, xnid, 'product', 'fr', ArtDesc);

insert into node_revisions(nid, vid, title, body, teaser) values (xnid, xnid, ArtDesc, ArtBody, ArtBody);

insert into uc_products(vid,nid,Articleno, sell_price) values (xnid,xnid,ArtNo, Price);

if ImageName <> '' then

begin

select max(fid) into xfid from files;

set xfid = xfid + 1;

insert into files(fid,uid,filename,filepath,filemime,status) values (xnid,1,Imagename,ImagePath,'image/jpeg',1);

select max(vid) into xvid from content_type_product;

set xvid = xvid + 1;

insert into content_type_product(vid, nid, field_uc_image_fid) values (xvid, xnid, xfid) ;

end;

end if;

end;

end if;

end;

when 1 then

begin

select nid into xnid from uc_products where ArticleNo = ArtNo;

update uc_products set sell_price = Price where nid  = xnid;

update node_revisions set title = ArtDesc, body = ArtBody, teaser = ArtBody where nid = xnid;

if ImageName <> '' then

begin

select distinct field_uc_image_fid into xfid from files f, content_type_product ctp where ctp.nid = xnid;

if xfid > 0 then

update files set filename = ImageName, FilePath = ImagePath where fid = xfid;

end if;

end;

end if;

end;

end case;

end

 

Can anyone help me with the conversion 

[trq]

Can anyone help me with the conversion

 

Sure, where exactly are you stuck?

[lordrt]

it's mostly the part " create procedure......" and the select, update statements

for the create i can use a function?

[trq]

You want to post what you have?

[trq]

Also, why does this need to be re-written in php? You can just as easily execute this code from within php if need be, all it does is create a procedure though.

 

Its probably best to create the procedure using sql, then simply call it using php.

[lordrt]

managed to convert the sql commands to php ones, i hope it works fine now, not tested yet 

[lordrt]

Hello all, i converted the sp from mysql which i posted 1st time in to a php one below but it is not entering the 'case' parts and also not creating the function in phpmyadmin db, any help plz... 

 

 

<?php

 

mysql_connect("127.0.0.1", "root", "") or die(mysql_error());

echo "connected to host .<br/> ";

mysql_select_db("drupal_db") or die(mysql_error());

echo "connected to DB . <br/> ";

 

function ImportArticle($ArtNo, $ArtDesc, $ArtBody, $Price, $ImageName, $ImagePath, $Op)

{

    $nid = 0;

    $fid = 0;

    $vid = 0;

   

    switch($Op)

    {

        case 0:

        {

            echo "entering case 0";

            mysql_query("select count(nid) into $nid from uc_products where ArticleNo = $ArtNo");

            if ($nid > 0)

            {   

                mysql_query("select nid into $nid from uc_products where ArticleNo = $ArtNo");

                mysql_query("update uc_products set sell_price = $Price where nid  = $nid");

                mysql_query("update node_revisions set title = $ArtDesc, body = $ArtBody, teaser = $ArtBody where nid = $nid");

               

                if ($ImageName <> '')

                {

                    mysql_query("select distinct field_uc_image_fid into $fid from files f, content_type_product ctp where ctp.nid = $nid");

                    if ($fid > 0)

                    {

                        mysql_query("update files set filename = $ImageName, FilePath = $ImagePath where fid = $fid");

                    }

                }

            }

            else

            {

                mysql_query("select max(nid) into $nid from node");

                $nid = $nid + 1;

                mysql_query("insert into node(nid, vid, type, language, title) values($nid, $nid, 'product', 'fr', $ArtDesc)");

                mysql_query("insert into node_revisions(nid, vid, title, body, teaser) values ($nid, $nid, $ArtDesc, $ArtBody, $ArtBody)");

                mysql_query("insert into uc_products(vid,nid,Articleno, sell_price) values ($nid, $nid, $ArtNo, $Price)");

               

                if ($ImageName <> '')

                {

                    mysql_query("select max(fid) into $fid from files");

                    $fid = $fid + 1;

                    mysql_query("insert into files(fid,uid,filename,filepath,filemime,status) values ($nid,1,$ImageName,$ImagePath,'image/jpeg',1)");

                   

                    mysql_query("select max(vid) into $vid from content_type_product");

                    $vid = $vid + 1;

                    mysql_query("insert into content_type_product(vid, nid, field_uc_image_fid) values ($vid, $nid, $fid)");

                }

            }

            break;

        }

       

        case 1:

        {

            echo "entering case 1";

            mysql_query("select nid into $nid from uc_products where ArticleNo = $ArtNo");

            mysql_query("update uc_products set sell_price = $Price where nid  = $nid");

            mysql_query("update node_revisions set title = $ArtDesc, body = $ArtBody, teaser = $ArtBody where nid = $nid");

           

            if ($ImageName <> '')

            {

                mysql_query("select distinct field_uc_image_fid into $fid from files f, content_type_product ctp where ctp.nid = $nid");

                if ($fid > 0)

                {

                    mysql_query("update files set filename = $ImageName, FilePath = $ImagePath where fid = $fid");

                }

            }

            break;

        }

    }

}

 

echo "closing connection";

mysql_close();

 

?>

[JonathanV]

Could you be a bit more precise about where precisely things are going wrong ?

 

Furthermore, please use the code tags on this forum, it makes your code a hell of a lot easier to go trough for us to help you out.

[lordrt]

Could you be a bit more precise about where precisely things are going wrong ?

 

Furthermore, please use the code tags on this forum, it makes your code a hell of a lot easier to go trough for us to help you out.

 

Sorry didnt know about the code tags

In fact when am running this script it does not give any error, but i added echo for case 0 and 1 to know if they are executed, but it just displays message for the connection and disconnection to DB. In my phpmyadmin db as well it does not create the function. In fact the whole code has been translated from mysql to php, my php knowledge being limited. In mysql it works fine, the code is for creating a mysql stored procedure