MSSQL condition query and case sensitivity

If exists select, else  insert

if not exists (select 1 from  [ClinRefFileTypeMaster] where [ClinRefTypeName] =@name)
begin
Insert into [ClinRefFileTypeMaster] ([ClinRefTypeName]) values (@name) 
end
else
begin
select (as desired) from ClinRefFileTypeMaster where where [ClinRefTypeName] =@name
end

https://stackoverflow.com/questions/32095623/return-id-if-record-exist-else-insert-and-return-id/32095900

MSSQl case sentitive?

depends on collation, default collation ends with _cs is case insensitive _cs means case

case insensitive 

utf8_general_ci  makes it case insensitive, where CI means case insensitive

Comparisons are case insensitive when the column uses a collation which ends with _ci (such as the default latin1_general_ci collation) and they are case sensitive when the column uses a collation which ends with _cs or _bin (such as the utf8_unicode_cs and utf8_bin collations).

https://stackoverflow.com/questions/3936967/mysql-case-insensitive-select

php sql_srv get last inserted id

$query = "INSERT INTO test (col1, col2) VALUES (?,?); SELECT SCOPE_IDENTITY()";

$arrParams[]="1";

$arrParams[]="2";

$resource=sqlsrv_query($conn, $query, $arrParams);
...

sqlsrv_next_result($query);

bool fetchStatus = sqlsrv_fetch($query);

if(fetchStatus === false) {
   die( print_r( sqlsrv_errors(), true));
}

if(fetchStatus === null) {
   // Some work when there are no results in the result set
} else {
   $id = sqlsrv_get_field($query, 0);
}

...

query needs to have SELECT SCOPE_IDENTITY() for get last insert ID to work

SCOPE_IDENTITY (Transact-SQL)

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope

https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-ver15

https://stackoverflow.com/questions/23491636/grabbing-last-insert-id-sqlsrv

https://stackoverflow.com/questions/23491636/grabbing-last-insert-id-sqlsrv

Laravel eloquent query if(when condition )

Conditional Clauses

Sometimes you may want clauses to apply to a query only when something else is true. For instance you may only want to apply a where statement if a given input value is present on the incoming request. You may accomplish this using the when method:

https://laravel.com/docs/5.5/queries#conditional-clauses

$role = $request->input('role');

$users = DB::table('users')
                ->when($role, function ($query) use ($role) {
                    return $query->where('role_id', $role);
                })
                ->get();

The when method only executes the given Closure when the first parameter is true. If the first parameter is false, the Closure will not be executed.

You may pass another Closure as the third parameter to the when method. This Closure will execute if the first parameter evaluates as false. To illustrate how this feature may be used, we will use it to configure the default sorting of a query:

$sortBy = null;

$users = DB::table('users')
                ->when($sortBy, function ($query) use ($sortBy) {
                    return $query->orderBy($sortBy);
                }, function ($query) {
                    return $query->orderBy('name');
                })
                ->get();

***** This when(param1, function($query) use ($param2) {

}). Param1 can be a condition that evaluates to True or False as well, I.E

when(isset($param_1)....)

If $param1 is a value, remeber 0 evaluates to false

SQL combine select column a, column b to a new column

SELECT *, (FieldA + FieldB) AS Sum
FROM Table

https://stackoverflow.com/questions/14877797/how-to-sum-two-fields-within-an-sql-query

SQL && Laravel eloquent order by multiple column

User::orderBy('name', 'DESC')
    ->orderBy('email', 'ASC')
    ->get();

Produces the following query:

SELECT * FROM `users` ORDER BY `name` DESC, `email` ASC

https://stackoverflow.com/questions/17006309/how-to-use-order-by-for-multiple-columns-in-laravel-4