Page 1 of 1

convert mysql code into php code

Posted: Fri Jul 31, 2009 12:32 am
by lordrt
Hello all I have the following sql statements which I need to convert to php compatible code:

Code: Select all

 
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?

Re: convert mysql code into php code

Posted: Mon Aug 03, 2009 12:57 am
by lordrt
I managed to convert to the following but still its not working and is skipping the 'case' statements, and nothing being created in phpmyadmin. Anyone plz help, need this urgent :!:

Code: Select all

<?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;
        }
    }
}
 
$input = ImportArticle($ArtNo, $ArtDesc, $ArtBody, $Price, $ImageName, $ImagePath, $Op);
echo "closing connection";
mysql_close();
 
?>