How to use ON DUPLICATE KEY in insert statement

How to use ON DUPLICATE KEY in insert statement

Problem:
In case of inserting or updating record we use separate logic using if statement or case statement:
e.g.:

if(i==0)
insert here

if(i=1)
update here

we can terminate the above if statement by using On Duplicate Key.
by using this only one statement can be use for insert and update record.

*note: user_nm coulmn must be unique

Example:

INSERT INTO `posts` (`user_nm`, `no_of_update`, `inserted_on`, `update_on`)
VALUES ('a_user', 1, NOW(), NOW())
ON DUPLICATE KEY
UPDATE `no_of_update` = `no_of_update` + 1, `update_on` = NOW()

Fore more, continue here...

Email Blaster

Email Blaster
*Features
- Send unlimited emails.
- Send test email, so you can see your email format and looks.
- Show real time progress in counting sent emails.
- Show progress in percentage(%)
- Send email both format Text and HTML format.
- Alternate Solution of Cron Job(Less feature).
- Alternate Solution of SQL Agent Job(Less feature).
- Solution of Script Timeout.

Fore more, continue here...

PHP with MSSQL Server Simple Query

PHP with MSSQL Server Simple Query.

<?php
session_start();
$sqlServerIP = '127.0.0.1';
$link = mssql_connect($sqlServerIP, 'sa', 'amit123');
if (!$link) {
    die('Wrong connection');
}

mssql_select_db('AssetMgmt', $link);
$User_Email="email@email.com";
$User_Pass='test';
$ssql="select *from USER_MASTER where User_Email='".$User_Email."' and User_Pass='" .$User_Pass ."'";
$result = mssql_query($ssql);
$numrow=mssql_num_rows($result);
if (!mssql_num_rows($result)) {
    echo 'Login/Pass is invalid';
}
else
{
    echo '<ul>';
    while ($row = mssql_fetch_assoc($result)) {
        echo '<li>' . $row['Name'] . ' (' . $row['EmpID'] . ')</li>';
    }
    echo '</ul>';
}
mssql_free_result($result);
?>

Fore more, continue here...

PHP with MSSql Server Stored procedure return records

Connect PHP with MSSql Server Stored procedure and return records.


<?php
$sqlServerIP = '127.0.0.1';
$link = mssql_connect($sqlServerIP, 'sa', 'amit123');
if (!$link) {
    die('wrong connection');
}
mssql_select_db('amittest', $link);
$stmt = mssql_init('ListEmp');
$ID = 100;
mssql_bind($stmt, '@ID',            $ID,              SQLINT4,     false, false, 4);
$result=mssql_execute($stmt);
$num = mssql_num_rows($result);

if($num>0) {
    while($row = mssql_fetch_assoc($result)) {
        echo "<li>" . $row['ID'] . $row['EmpID']."</li>";
    }
    mssql_free_statement($stmt);
}
else {
    echo "No records found";
}
echo mssql_get_last_message();
/*
CREATE PROCEDURE dbo.ListEmpmp
    @ID bigint
    as
begin
    select ID,EmpID from Employee where ID>=@ID
End
*/
?>

Fore more, continue here...

PHP with MSSQL Server Stored Procedure

Here is the code to connect PHP with Mssql Server Stored procedure.



<?php
$sqlServerIP = '127.0.0.1';
$link = mssql_connect($sqlServerIP, 'sa', 'amit123');
if (!$link) {
    die('wrong connection.');
}
mssql_select_db('amittest', $link);
$stmt = mssql_init('LoginChk');
$ID = 1;
$EmpID = 'Emp001';
$OutputLogin=0;
// Bind values
mssql_bind($stmt, '@ID',            $ID,              SQLINT4,     false, false, 60);
mssql_bind($stmt, '@EmpID',         $EmpID,           SQLVARCHAR,  false, false, 20);
mssql_bind($stmt, '@OutputLogin',     $OutputLogin,   SQLINT4,     true,  false, 20);
mssql_execute($stmt);
mssql_free_statement($stmt);
echo $OutputLogin;
echo mssql_get_last_message();
/*
////////////////////////////////////////////////////////
CREATE PROCEDURE dbo.LoginChk
    @ID varchar(50),
    @EmpID varchar(50),
    @OutputLogin bigint OUTPUT
    as
begin
    IF(EXISTS(select ID,EmpID, from Employee where ID=@ID and EmpID=@EmpID))
        begin
            --select  @OutputFirstName=vFirstName from AdminInfo where vUserId=@UserId and vPassword=@Password
            select @OutputLogin =1
        End
    else
        begin
            select @OutputLogin =0
        End
End
*/
?>

Fore more, continue here...

Difference between primary key and unique key in sql

Difference between primary key and unique key in sql.
======================================================

Primary key                            Unique key
1-can not be null                    1-can be null and multiple null
2-can not be same value        2-can not be same value
3-only be one column name  3-multiple column could be unique

suppose emp_id is primary key and city is the unique key
primary key                                   unique key
-----------------------------------------------------------------------------------
emp_id        emp_nm        sal        city
-----------------------------------------------------------------------------------
0                  AA            18000        (null)      
1                 BB             18000        NEW DELHI
3                CC             18000        NOIDA
2                DD             18000        (null)
4                EE             18000        KANPUR
5                FF             18000        (null)
6                GG             18000        (null)
----------------------------------------------------------------------------------

Fore more, continue here...