Recent News

Insert Comma Separated String to Database table in My SQL





 If the database table contains this id than it update the data if not contain it insert a new record which are not in table

 I am writing this for my requirement ,you can also change the input parameter as per your requirement.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `USP_Update_ServiceTask`(
   
        in  in_Action           varchar(10),
        in  PKService_Task_ID   int,
        in  in_serviceid        int,
        in  in_TaskIds          varchar(8000),
        in  in_Updatedby        varchar(50),
        out  message            varchar(50)

)
BEGIN
    DECLARE vDone tinyint(1) DEFAULT 1;
    DECLARE vIndex INT DEFAULT 1;
    DECLARE vSubString VARCHAR(15);
    DECLARE  vSeparator VARCHAR(5);
    DECLARE  count      INT;

       if in_Action='U' then
      
            SET vSeparator = ',';
            WHILE vDone > 0
                DO
                  SET vSubString = SUBSTRING(in_TaskIds, vIndex,
                                    IF(LOCATE(vSeparator, in_TaskIds, vIndex) > 0,
                                      LOCATE(vSeparator, in_TaskIds, vIndex) - vIndex,
                                      LENGTH(in_TaskIds)
                                    ));
                                   
                  IF LENGTH(vSubString) > 0 THEN
                                    
                      SET vIndex = vIndex + LENGTH(vSubString) + 1;
                     
                     SET count = (select count(*) from remotecorp_bpm.tbl_service_task where TAX_ID=vSubString  and Service_id=in_serviceid );             
                          if(count = 0) then
                         
                             INSERT INTO remotecorp_bpm.tbl_service_task(Service_id,TAX_ID,Created_Date,Created_By)
                             VALUES (in_serviceid,vSubString,now(),in_Updatedby);
                            
                               ELSE
                                   UPDATE remotecorp_bpm.tbl_service_task SET Service_id=in_serviceid,TAX_ID=vSubString,
                                   Modified_date=now(),Modified_By=in_Updatedby where TAX_ID=vSubString;
                            
                           end if; 
                     
                  ELSE
                      SET vDone = 0;
                  END IF;
 
  END WHILE;
 
         SET message='UP';
         
      
        end if;

END

Split Comma Separated String in My SQL


DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `String_Split`(
 vString VARCHAR(8000),
  vSeparator VARCHAR(5)
)
BEGIN
DECLARE vDone tinyint(1) DEFAULT 1;
DECLARE vIndex INT DEFAULT 1;
DECLARE vSubString VARCHAR(15);

DROP TABLE IF EXISTS tmpIDList;
CREATE TEMPORARY TABLE tmpIDList (ID INT);

WHILE vDone > 0
DO
  SET vSubString = SUBSTRING(vString, vIndex,
                    IF(LOCATE(vSeparator, vString, vIndex) > 0,
                      LOCATE(vSeparator, vString, vIndex) - vIndex,
                      LENGTH(vString)
                    ));
                   
                     IF LENGTH(vSubString) > 0 THEN
      SET vIndex = vIndex + LENGTH(vSubString) + 1;
      INSERT INTO tmpIDList VALUES (vSubString);
  ELSE
      SET vDone = 0;
  END IF;
 
  END WHILE;
 
END


you can call this for every string passed by fronend and use this any sql query ,After that you should drop the temp table
    like :  call  String_Split('1,1,1,12,3,34,22',',');
     select mem_name from member where Id in (select id from tmpIDList )
      DROP table tmpIDList;
   

How to creat slideshow in javascript




Slidesh
Step 1: Open your webpage and paste the following code anywhere between <body> and </body>:

<!---------------------------------------------->
<!-- START OF CODE FOR THE SLIDESHOW -->
<!---------------------------------------------->

<!-- CONFIGURATION OF TEXT-STYLE STARTS HERE -->
<style>
.textstyle {
                /* style attributes for the comments */
                font-family:Arial;
                font-size:8pt;
                color:#aaaaaa;
                background-color:#FFFFFF;
                text-align:center;
                vertical-align:middle;
}
.boxstyle{
                /* style attributes for the slideshow-box */
                overflow:hidden;
                border-style:solid;
                border-width:1px;
                border-color:white;

                /* shadow for Firefox */
                -moz-box-shadow: 5px 5px 8px #AAAAAA;

                /* shadow for Safari and Chrome */
                -webkit-box-shadow: 5px 5px 8px #AAAAAA;

                /* shadow for Opera */
                box-shadow: 5px 5px 5px #AAAAAA;

                /* shadow for Internet Explorer */
                filter: progid:DXImageTransform.Microsoft.Shadow(Strength=5, Direction=135, Color='#AAAAAA');
}
</style>
<!-- CONFIGURATION OF TEXT-STYLE ENDS HERE -->

<script>
// CREDITS:
// Slideshow with a combination of tranparency and blur effect
// Permission given to use the script provided that this notice remains as is.
// IMPORTANT:
// If you add this script to a script-library or script-archive
// where this script will be running.
/////////////////////////////////////////////////
// START OF SCRIPT-CONFIGURATION /////////
/////////////////////////////////////////////////

var imgurl= new Array()
// Your images. Add as many images as you like
imgurl[0]="sample1.jpg"
imgurl[1]="sample2.jpg"
imgurl[2]="sample3.jpg"
imgurl[3]="sample4.jpg"

var imglink= new Array()
// Add the links corresponding to the images above. Enter "#" if you don't want to add a link
imglink[0]="http://"
imglink[1]="http://"
imglink[2]="#"
imglink[3]="http://www.google.com"

var message=new Array()
// Add the messages corresponding to the images above
message[0]="Do you love Mary?"
message[1]="I like Nina as well!"
message[2]="Tonight I'll date Gina, tough"
message[3]="Don't tell granny, please!"

// Set the number of bluring layers. A high value will stretch the transition effect
var number_blur_layers=30

// Set the width of image, pixels
var img_width=200

// Set the height of images, pixels
var img_height=150

// Set the height of textbox below image, pixels
var text_height=25

// Set the stillstand, seconds
var pause=2

/////////////////////////////////////////////////
// END OF SCRIPT-CONFIGURATION /////////
/////////////////////////////////////////////////

// Do not edit below this line

var i_span=number_blur_layers-1
var i_opacity=1
var i_imgurl=0
var timer
var low_range=0
var high_range=12
var total_height=img_height+text_height
pause*=1000
var ie=document.all?1:0

function sharpen(){
                if (i_span>=1) {
                                document.getElementById('span'+i_span).style.visibility="hidden"
                                if (ie) {
                                                document.getElementById('span0').filters.alpha.opacity=i_opacity
                                }
                                else {
                                                document.getElementById('span0').style.opacity=i_opacity/100
                                }
                                i_span--
                                i_opacity+=100/number_blur_layers
                               
                                timer=setTimeout("sharpen()",30)
                }
                else {
                                document.getElementById('textbox').innerHTML="<table cellpadding=0 cellspacing=0 height="+text_height+" width="+img_width+"><tr><td class='textstyle'>"+message[i_imgurl]+"</td></tr></table>"
                                document.getElementById('span0').style.visibility="visible"
                                clearTimeout(timer)
                                i_span=1
                                i_imgurl++
                                if (i_imgurl>=imgurl.length) {i_imgurl=0}
                                i_opacity=100
                                timer=setTimeout("doblur()",pause)
                }
}


function doblur(){
                if (i_span<number_blur_layers) {
                                document.getElementById('textbox').innerHTML=""
                                document.getElementById('span'+i_span).innerHTML="<img src='"+imgurl[i_imgurl]+"'>"
                                document.getElementById('span'+i_span).style.visibility="visible"
                                if (ie) {
                                                document.getElementById('span0').filters.alpha.opacity=i_opacity
                                }
                                else {
                                                document.getElementById('span0').style.opacity=i_opacity/100
                                }
                                i_opacity-=100/number_blur_layers
                                i_span++
                                timer=setTimeout("doblur()",30)
                }
                else {
                                document.getElementById('span0').innerHTML="<a href='"+imglink[i_imgurl]+"'><img src='"+imgurl[i_imgurl]+"' border=0></a>"
                                clearTimeout(timer)
                                i_span=number_blur_layers-1
                                i_opacity=0
                                sharpen()
                }
}

document.write("<div id='roof' class='boxstyle' style='position:relative; width:"+img_width+"px; height:"+total_height+"px;overflow:hidden;'>")
document.write("<div id='span0' style='position:absolute;top:0px;left:0px;filter:alpha(opacity=0);opacity:0;'><a href="+imglink[0]+"><img src='"+imgurl[0]+"' border=0></a></div>")
for (i=1;i<number_blur_layers;i++) {
                var x_ran=(Math.round(Math.random()*high_range)+low_range)-(high_range/2)
                var y_ran=(Math.round(Math.random()*high_range)+low_range)-(high_range/2)
    document.write("<div id='span"+i+"' class='ticker' style='position:absolute;top:"+y_ran+"px;left:"+x_ran+"px;filter:alpha(opacity=2);opacity:0.02;'><img src='"+imgurl[0]+"'></div>")
}
document.write("<div id='textbox' class='textstyle' style='position:absolute;top:"+img_height+"px;left:0px;height:"+text_height+"px;width:"+img_width+"px;'><table height="+text_height+" width="+img_width+"><tr><td class='textstyle'>"+message[0]+"</td></tr></table></div>")
document.write("</div")

window.onload=sharpen

</script>

<!---------------------------------------------->
<!-- END OF CODE FOR THE SLIDESHOW -->
<!---------------------------------------------->

Step 2: Configure the text-style and boxstyle on top of the code (within the style-tags). Then go on top of the script code and configure the images, the messages, the links, the width and the height of the slideshow.


Ajaya

Poll

Pages

Total Pageviews

ajaya rout. Powered by Blogger.

Followers

Popular Posts

Recent Comments | Recent Posts


Bloggerized by : GosuBlogger | designed by: Website Builder | Coded by: Blog Directory | Provided by: Wedding photojournalism chicago
bottom
Blogger Widgets