Unexpected T_VARIABLE or T_ECHO variable error in PHP

If you are newbie in PHP programming, you may have encountered to above problem. It is just a minor error. But, the nature of error message is some tricky. Generally, the message comes like this.

 

Parse error: parse error, unexpected T_ECHO in F:\Websites\AptechKtm\Course\index.php on line 246

 

I found this error in following lines.

 

$dob = $_POST['ddl_DOTYear'].$_POST['ddl_DOTMonth'].$_POST['ddl_DOTDate']

echo($sql);      

 

The error has come due to not placing semicolon (;) mark at the end of first statement to mark the end of that statement. So, the PHP engine found echo keyword unexpectedly. Hence, the error message.

 

 

Read more...

Create SQL Job to shrink database

If you are a newbie in SQL database, this post may be helpful for you to create SQL job in SQL Server 2000. SQL Jobs are similar to Windows Scheduled job. It also can be scheduled to run hourly, daily, weekly or monthly as per required, provided that SQL Server agent is on and no other external things stops the execution.

 

1. Open the SQL Server Enterprise Manager.
2. Go to Microsoft SQL Servers -> SQL Server Group (or other group) -> Open the node of required SQL Server running -> Open Management folder -> Open SQL Server Agent -> Jobs.
3. In the Right Pane, right click and select New Job.
4. In General tab, give an appropriate name (say "Shrink Database – testdb") for new job and select owner.
5. In Steps tab, click New to open a pop-up window.
6. Write an appropriate name for step (say "Step 1").
7. Type the required query to shrink database in Command multiline box.

             DBCC SHRINKDATABASE (N'testdb', 10)

             It shrinks database named "testdb". But, it keeps 10% free space in the database.

             It gives the freed space to operating system for its use.

 

8. On Advanced tab, you can choose what to do on successful completion of job and on failure. For now, you can leave it to default.
9. Click Ok to save and return to main pop up window of "Shrink Database – testdb".
10. On Schedule tab of the pop up, click New Schedule.
11. Give the name of new schedule (say "Shrink Database – testdb").
12. Select Recurring and click Change.
13. Select frequency of job and time for the job and click Ok to return.
14. Click Ok to return to main pop up window "Shrink Database – testdb".
15. If you have configured SQL main operator, you can send mail on different stages of job execution through Notification tab. I will discuss more on it in my forth coming posts. For now, you can leave it.
16. Click Ok to save the job and you are done.
17. Check if the job is executed on time or not and executed properly or not.
18. You can always run any job instantly by right clicking the job and selecting Start Job.

 

Shrinking database through SQL Jobs can definitely remove burden on database administrators to manually shrink database each time. I hope this post is helpful for new comers to create basic jobs in SQL Server.

 

 

Read more...

Convert Amount into Words according to Indian or Nepali Numbering Style.

 
In the post http://techcreeze.blogspot.com/2008/11/convert-amount-into-words-according-to.html, I have talked about the converting amount (in number) to amount in words according to English Numbering Style.
 
I am including here, source code of functions required for converting Number into words according to Indian or Nepali Numbering style. In Indian or Nepali Numbering style, 100000 is 1 Lakh and 100 Lakhs or 10000000 is 1 Crore. This makes the numbering style different from English and International Numbering Style.
 
1. Function to Convert one Digit Number to words.
 
CREATE    Function dbo.fConvertDigit(@decNumber decimal)
returns varchar(6)
as
Begin
declare
@strWords varchar(6)
 Select @strWords = Case @decNumber
     When '1' then 'One'
     When '2' then 'Two'
     When '3' then 'Three'
     When '4' then 'Four'
     When '5' then 'Five'
     When '6' then 'Six'
     When '7' then 'Seven'
     When '8' then 'Eight'
     When '9' then 'Nine'
     Else ''
 end
return @strWords
end

 
2. Function to convert 2 digit number to words.
 

CREATE    Function dbo.fConvertTens(@decNumber varchar(2))
returns varchar(30)
as
Begin
declare @strWords varchar(30)
--Is value between 10 and 19?
If Left(@decNumber, 1) = 1
begin
 Select @strWords = Case @decNumber
     When '10' then 'Ten'
     When '11' then 'Eleven'
     When '12' then 'Twelve'
     When '13' then 'Thirteen'
     When '14' then 'Fourteen'
     When '15' then 'Fifteen'
     When '16' then 'Sixteen'
     When '17' then 'Seventeen'
     When '18' then 'Eighteen'
     When '19' then 'Nineteen'
 end
end
else  -- otherwise it's between 20 and 99.
begin
 Select @strWords = Case Left(@decNumber, 1)
     When '0' then ''  
     When '2' then 'Twenty '
     When '3' then 'Thirty '
     When '4' then 'Forty '
     When '5' then 'Fifty '
     When '6' then 'Sixty '
     When '7' then 'Seventy '
     When '8' then 'Eighty '
     When '9' then 'Ninety '
 end
 Select @strWords = @strWords + dbo.fConvertDigit(Right(@decNumber, 1))
end
 --Convert ones place digit.
 
return @strWords
end
 
 
3. Function to convert amt in numbers to words. (Built with the help of above 2 functions)
CREATE function dbo.fNumToWords (@decNumber decimal(12, 2))
returns varchar(300)
As
Begin
Declare
 @strNumber varchar(100),
 @strRupees varchar(200),
 @strPaise varchar(100),
 @strWords varchar(300),
 @intIndex integer,
 @intAndFlag integer

Select @strNumber = Cast(@decNumber as varchar(100))
Select @intIndex = CharIndex('.', @strNumber)
if(@decNumber>99999999.99)
BEGIN 
 RETURN ''
END
If @intIndex > 0
begin
 Select @strPaise = dbo.fConvertTens(Right(@strNumber, Len(@strNumber) - @intIndex))
 Select @strNumber = SubString(@strNumber, 1, Len(@strNumber) - 3)
 If Len(@strPaise) > 0 Select @strPaise = @strPaise + ' paise'
end
Select @strRupees = ''
Select @intIndex=len(@strNumber)
Select @intAndFlag=2
while(@intIndex>0)
begin
 if(@intIndex=8)
 begin
  Select @strRupees=@strRupees+dbo.fConvertDigit(left(@decNumber,1))+' Crore '
  Select @strNumber=substring(@strNumber,2,len(@strNumber))
  Select @intIndex=@intIndex-1
  
 end
 else if(@intIndex=7)
 begin
  if(substring(@strNumber,1,1)='0')
  begin
   if substring(@strNumber,2,1)<>'0'
   begin 
    if (@strRupees<>NULL and substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and substring(@strNumber,7,1)='0' and @intAndFlag=2 and @strPaise=NULL)
    begin
     Select @strRupees=@strRupees+' and ' +dbo.fConvertDigit(substring(@strNumber,2,1))+' Lakh '
     Select @intAndFlag=1
    end
    else
    begin
     Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,2,1))+' Lakh '
    end
    
    Select @strNumber=substring(@strNumber,3,len(@strNumber))
    Select @intIndex=@intIndex-2
   end
   else
   begin
    Select @strNumber=substring(@strNumber,3,len(@strNumber))
    Select @intIndex=@intIndex-2
   end
  end 
  else
  begin
   if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and substring(@strNumber,7,1)='0'  and @intAndFlag=2 and @strPaise='')
   begin   
    Select @strRupees=@strRupees+' and ' + dbo.fConvertTens(substring(@strNumber,1,2))+' Lakhs '
    Select @intAndFlag=1
   end
   else
   begin
    Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))+' Lakhs '
   end
   Select @strNumber=substring(@strNumber,3,len(@strNumber))
   Select @intIndex=@intIndex-2
  end
 end 
 else if(@intIndex=6)
  begin
   if(substring(@strNumber,2,1)<>'0' or substring(@strNumber,3,1)<>'0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and @intAndFlag=2 and @strPaise='')
   begin
    
    if len(@strRupees) <= 0
    begin
     if convert(int,substring(@strNumber,1,1)) = 1
     begin
      Select @strRupees=@strRupees+'' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
      Select @intAndFlag=2
     end
     else
     begin
      Select @strRupees=@strRupees+'' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
      Select @intAndFlag=2
     end
    end
    else
    begin
     if convert(int,substring(@strNumber,1,1)) = 1
     begin
      Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
      Select @intAndFlag=1
     end
     else
     begin
      Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
      Select @intAndFlag=1
     end 
    end
   end
   else
   begin
    if convert(int,substring(@strNumber,1,1)) = 1
    begin
     Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
    end
    else
    begin 
     Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
    end 
   end
   Select @strNumber=substring(@strNumber,2,len(@strNumber))
   Select @intIndex=@intIndex-1
  end
 else if(@intIndex=5)
  begin
   if(substring(@strNumber,1,1)='0')
   begin
    if substring(@strNumber,2,1)<>'0'
    begin
     if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and @intAndFlag=2 and @strPaise='')
     begin
      Select @strRupees=@strRupees+' and ' +dbo.fConvertDigit(substring(@strNumber,2,1))+' Thousand '
      Select @intAndFlag=1
     end
     else
     begin
      Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,2,1))+' Thousand '
     end
     Select @strNumber=substring(@strNumber,3,len(@strNumber))
     Select @intIndex=@intIndex-2
    end
    else
    begin
     Select @strNumber=substring(@strNumber,3,len(@strNumber))
     Select @intIndex=@intIndex-2
    end
   end 
   else
   begin
    if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and @intAndFlag=2 and @strPaise='')
    begin
     Select @strRupees=@strRupees+' and '+dbo.fConvertTens(substring(@strNumber,1,2))+' Thousand '
     Select @intAndFlag=1
    end
    else
    begin
     Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))+' Thousand '
    end
    Select @strNumber=substring(@strNumber,3,len(@strNumber))
    Select @intIndex=@intIndex-2
   end
  end 
 else if(@intIndex=4)
  begin
   if ( (substring(@strNumber,3,1)<>'0' or substring(@strNumber,4,1)<>'0') and substring(@strNumber,2,1)='0' and  @intAndFlag=2 and @strPaise='')
   begin
    Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Thousand '
    Select @intAndFlag=1
   end
   else
   begin
   Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Thousand '
   end
   Select @strNumber=substring(@strNumber,2,len(@strNumber))
   Select @intIndex=@intIndex-1
  end
 else if(@intIndex=3)
  begin
   if  substring(@strNumber,1,1)<>'0'
   begin
    Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Hundred '
    Select @strNumber=substring(@strNumber,2,len(@strNumber))
    
    if( (substring(@strNumber,1,1)<>'0' or  substring(@strNumber,2,1)<>'0') and @intAndFlag=2 )
    begin
     Select @strRupees=@strRupees+' and '
     Select @intAndFlag=1
    end
    Select @intIndex=@intIndex-1
   end
   else
   begin
    Select @strNumber=substring(@strNumber,2,len(@strNumber))
    Select @intIndex=@intIndex-1
   end
  end 
 else if(@intIndex=2)
  begin
   if substring(@strNumber,1,1)<>'0'
   begin
    Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))
    Select @intIndex=@intIndex-2
   end
   else
   begin
    Select @intIndex=@intIndex-1
   end
  end
 else if(@intIndex=1)
  begin
   if(@strNumber<>'0')
   begin
    Select @strRupees=@strRupees+dbo.fConvertDigit(@strNumber)
   end
   Select @intIndex=@intIndex-1
    
  end
continue
end
if len(@strRupees)>0 Select @strRupees=@strRupees+ ' rupees '
IF(len(@strPaise)<>0)
BEGIN
 if len(@strRupees)>0 Select @strRupees=@strRupees + ' and '
END
Select @strWords = IsNull(@strRupees, '') + IsNull(@strPaise, '')
select @strWords = @strWords + ' only'
Return @strWords
End
 
 

Read more...

Compare records of Same SQL Table

If you have to compare a record of SQL table to another record (which could be any), you would probably think of a nested while loop. Nested while loop is ok, but it may costlier. Here, I am discussing a trick to avoid loop to compare results.

 

Consider a case of Bank Charge. It is an amount charged by bank for transferring amt to another bank. Here, let's say a manufacturing company has many customers. The customers pay some bank charge while transferring amt from his bank to company's bank. The company pays back some amount to customers against the bank charges. It has own standards to calculate the bank charges.

 

If you are not up to previous example, leave it. Just assume a table with following data.

-- Table Structure

create table tbl_bank_charge

(

            tid                    int identity(1,1),

            customer          varchar(3),

            bank                 varchar(20),

            from_amt          numeric(18,3),

            to_amt              numeric(18,3),

            bc_amt             numeric(18,3)

)

 

-- Table Data

Tid	Customer	Bank	From_Amt	To_Amt	BC_Amt
8	ABC	Nabil	0	50,000	100
9	ABC	Nabil	50,001	100,000	200
10	ABC	RBB	50,000	100,000	333
11	ABC	RBB	200,000	300,000	600
12	SBO	RBB	0	10,000	50
13	ABC	Nabil	1,000	50,000	120
14	SBO	RBB	10,001	90,000	500

 

Check out the data in rows with tid 8 and 13 carefully. Customer ABC is given Rs. 100 for deposit amt of 0 to 50,000 in row with tid 8, while in row with tid 13 it is given Rs 120 for deposit amt of 10,000 to 50,000. This is not correct. How will you find out that? Using nested loop, you will have to compare each row with every another row in table. Now, use the query below to find out overlapping buckets.

 

 

if exists (           select    *

                        from     tbl_bank_charge a

                        inner join tbl_bank_charge b

                                    on        a.customer        = b.customer

                                    and       a.bank              = b.bank

                        where   a.tid      <> b.tid

                        and       (           a.from_amt between b.from_amt and b.to_amt

                                    or         a.to_amt between b.from_amt and b.to_amt)

            )

            begin

                        print 'Error: Buckets Overlap'

                        return

            end

 

It simply joins the same table 2 times and compares each row with another row having same customer and bank. If it finds out any overlapping margins then prints error and returns. Thus, it is a simple tweak to remove loop and optimize query processing.

 

Read more...