Monthly Archives: June 2012

Type on native language through Google Transliteration API

Have seen how to read write multi language data from database? If not, click here. Now times to type multi language through Google Transliteration API. Google Transliteration supports so many languages. Click to view list.

What is Transliteration?

Transliteration is form of translation to convert a text from one script to another. Convert phonetic text to another script.

How it works?

Have you ever used Google Translator? If yes, that’s it. Here same theory use in your own page.

Javascript Code:

Load below script in <head> section:

<script src=”https://www.google.com/jsapi” type=”text/javascript”></script>
<script type=”text/javascript”>
google.load(“elements”, “1”, {
packages: “transliteration”
});
function onLoad(){
var options = {
sourceLanguage: google.elements.transliteration.LanguageCode.ENGLISH,
destinationLanguage: [google.elements.transliteration.LanguageCode.GUJARATI],
shortcutKey: ‘ctrl+g’,
transliterationEnabled: true
};
var control = new google.elements.transliteration.TransliterationControl(options);
control.makeTransliteratable([‘transliterateTextarea’]);
}
google.setOnLoadCallback(onLoad);
</script>

sourceLanguage : Give source language whatever you want.
destinationLanguage : Give destination language whatever you want to convert from source language.
makeTransliteratable : Give element id. You can pass comma sparated multiple ids.

Here I explain with my own language (GUJARATI). GUJARATI is Indian language.

HTML Code:

<div>
Type in Gujarati (Press Ctrl+g to toggle between English and Gujarati)<br>
<textarea id=”transliterateTextarea” style=”width:600px;height:200px”></textarea>
</div>

Now ready to type in GUJARATI. Click to view in details and demo.

A Complete Google Transliteration Code:

Enjoy Coding…

* Native language must support by Google Transliteration API.

MySql, PHP and UTF8

Hopefully as per title you understand what I want to share with you. Yes exactly here I am going to explain how to play with UTF8 data in PHP and MySql. I will try my best to explain each thing.

There are so many questions like:

What thing need to take care while creating table to store UTF8 data?
How to store it with PHP?
How to display in page?

Let’s start one by one.

MySql

First of all seen what need to do while creating table? Nothing new in order to create table just set Collation property to utf8_unicode_ci for each field as well as for table. That’s it from database. Table structure should like:

MySql Unicode Table Structure

MySql Unicode Table Structure

PHP

Now turn on PHP how to read write data. Make sure whenever going to write data don’t forget to initialize default charset before inserting or updating data. See below syntax to initialize charset.

ini_set(‘default_charset’, ‘UTF-8’);

One more thing needs to take care always prepend before value of each field.

insert into table (name, heading) values (‘$name’, N’$heading’);
update table set name = ‘$name’, heading = N’$heading’ where id = $id; 

And here operation performed successfully! Now time to read from database and list out UTF8 data. For reading data need to tell MySql I want UTF8 result set. How can we tell to MySql?

$conn = mysql_connect(HOSTNAME,USERNAME,PASSWORD) or die (mysql_error());
mysql_select_db(DATABASE,$conn);
mysql_query (“set character_set_results=’utf8′”);
$sql = “select  name, heading from table;”;
$result = mysql_query($sql, $conn);

Hope you understand and rest of the process remain same.

Page Level

Wait not finish game its continue now turn on client site i.e. browser. Must need to set charset meta for UTF8 in <head> section:

<meta http-equiv=”Content-Type” content=”text/html; charset=UTF-8″ />

Page should be created under UTF8 encode.

That’s it game completed.

Enjoy coding…

 

 

SQL Server : copy data from one to another existing table

Hello All,

Today I come with one more SQL Server tips to copy data from one to another existing table in single line statement. Let’s see how we can copy data:

Syntex:

insert into <table name>
select <field list> from <table name from copy data>

Also, copy only filtered field:

insert into <table name> (field list)
select <field list> from <table name from copy data>

Example:

insert into table2
select * from table1

insert into table2 (no,name,city)
select no,name,city from table1

Run below query in SQL Server editor:

Declare @employee table(
no numeric(18,0) not null identity,
name varchar(50),
city varchar(100)
)

insert into @employee values (‘abc’,’abc1′),(‘pqr’,’pqr1′),(‘xyz’,’xyz1′);

Declare @employee1 table(
no numeric(18,0) not null identity,
name varchar(50),
city varchar(100)
)

insert into @employee1
select name,city from @employee

select * from @employee
select * from @employee1

Read and Write excel sheet in SQL Server

Recently, one of the projects needs to insert mass data into SQL server database. Data present in excel sheet with large number of row. There is a way to insert data into database through any programming language. After little bit research knowing that SQL server also provide read and write data in excel sheet. It’s a SQL server’s in-built function ‘OPENROWSET’.

Follow below procedure to read or write excel’s data:

To import Excel file:

First of all need to reconfigure SQL server options. Run below in-built store procedure step by step to set option.

  • sp_configure ‘show advanced options’, 1
    reconfigure
  • sp_configure ‘Ad Hoc Distributed Queries’, 1
  • reconfigure

1. Put Excel file on server, means we need to put files on server, If we are accessing it from local.
2. Write following TSQL, to read data from excel file.

SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=D:\test.xls’,
‘SELECT *
FROM [sheet1$]’)

OR Write data from excel sheet.

SELECT * INTO Temp
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=D:\Test.xls’,
‘SELECT *
FROM [sheet1$]’)

If you are using window 7 Operating System:

*** Read Data ****

SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 8.0;Database=D:\test.xls’,
‘SELECT *
FROM [sheet1$]’)

*** Write Data ***

SELECT * INTO Test
FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 8.0;Database=D:\text.xls’,
‘SELECT * FROM [sheet1$]’)

Disable past date in jquery datepicker

jQuery UI is open source. jQuery UI provides very useful widget for developing web application. Like Date Picker, Slider, Accordion, Dialog and many more. It’s very easy to play jQuery UI widget. See here put date picker onclick on textbox and disabled past date so no one can select past date.

For putting jQuery UI date picker first of all need to load jQuery, jQuery UI, jQuery UI Css. You can download from jQuery.com, jQueryUi.com. Add below code on jQuery on load function.

jQuery(‘#appointmentdate’).datepicker({minDate:0});

In case, require to give ability to select any date:

jQuery(‘#appointmentdate’).datepicker();

A complete code, How to disable past date in jquery datepicker?

// load all script and css
<script>
jQuery(function(){
jQuery(‘#appointmentdate’).datepicker({minDate:0});
})
</script>
<input type=”text” name=”appointmentdate” id=”appointmentdate” />

SQL Server: Get table list from column name

Every business application must have database and it becomes very huge and complex day by day and difficult to remember everything. I.e. which table has which fields? Sometimes needs to find out all table lists from column name.

The story behind this topic is: currently works on very huge project and needs to change column name on each table of database. It was very difficult job for me and for resolving this task start searching and find out a small beauty code of SQL Server which returns list of table in which column name exists. See below query.

select sys.tables.name
from sys.objects inner join sys.tables
on sys.objects.object_id = sys.tables.object_id
and sys.tables.type = ‘U’
inner join sys.columns on sys.tables.object_id = sys.columns.object_id
where sys.columns.name = ‘XYZ’
order by sys.tables.name

Enjoy Coding…

SQL Server Rank() v/s Dense_rank()

Yesterday night I was searching about SQL Server system functions and got a nice puzzle of SQL Server. The puzzle likes to find out second highest salary of each department and tie salary should also count.

Finally decide to solve this puzzle and thinking It will be sort out by Rank() function. There is one another function to find out Rank and it is Dense_rank().

What is the different of both function Rank() and Dense_rank()?

The Rank() function returns the position of value within partition of result set and it skips number if ranks are tie.

The Dense_rank() function returns the position of value within partition of result set and it does not skip rank if ranks are tie.

Copy below query and run in your Sql Server Query editor. Don’t worry it won’t create new table in your database as @tablename create dummy table.

— Employees table
DECLARE @Employees TABLE(
EmployeeID INT IDENTITY,
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)

— Insert data with different salary
INSERT INTO @Employees (EmployeeName, Department, Salary)
VALUES (‘T Cook’,’Finance’, 40000),(‘D Michael’,’Finance’, 25000),(‘A Smith’,’Finance’, 25000),(‘D
Adams’,’Finance’, 15000),(‘M Williams’,’IT’, 80000),(‘D Jones’,’IT’, 40000),(‘J Miller’,’IT’, 50000),(‘L
Lewis’,’IT’, 50000),(‘A Anderson’,’Back-Office’, 25000)
,(‘S Martin’,’Back-Office’, 15000)
,(‘J Garcia’,’Back-Office’, 15000)
,(‘T Clerk’,’Back-Office’, 10000);

— Rank() function
select *,RANK() over(partition by department order by salary desc) as ranking
from @Employees

— DENSE_RANK() function
select *,DENSE_RANK() over(partition by department order by salary desc) as ranking
from @Employees

RANK() function:

rank function query

rank function query output

DENSE_RANK() function:

Dense_Rank query

Dense_Rank query output

Finally, comes to the puzzle. I think you have got totally idea how does it resolve? Run below query to get second

Highest salary of each department.

select * from (
select *,RANK() over(partition by department order by salary desc) as ranking
from @Employees
) As T where T.ranking = 2

Output:

Second Highest Salary data

Second Highest Salary data

Enjoy coding…

Hide or Disable jQuery dialog Close(X) top right corner button

We have recently implemented jQuery Dialog box to show pop up notification on page. It’s very easy to implement jQuery dialog but there was requirement that no one can close pop up window without submitting require information so we just off its parameters. Like closeOnEscape:false, draggable:false,resizable:false etc. Now feel that the pop up window is not resizable, draggable and not close on escape and hit the page and see OMG what about close(x) top right corner buttom. There is no parameter to set close button off.

jQuery Dialog Close(X) button

jQuery Dialog Close(X) button

After doing little bit effort getting solution. Just needs to right below code after calling dialog box function to hide/disable close(x) button.

jQuery(“a.ui-dialog-titlebar-close”).hide();

Look a complete code to hide/disable jQuery dialog close(X) top right corner button.

Enjoy coding…

Edited on 13th Mar 2013

Today I found jQuery latest version change some graphical change in jQuery dialog box. I don’t know from which version they have changed but if you fail on above solution just change “a” to “button”.

jQuery(“button.ui-dialog-titlebar-close”).hide();