How do you perform an IF…THEN in an SQL SELECT ?

How do you perform an IF…THEN in an SQL SELECT ?

How do we perform an IF...THEN in an SQL SELECT statement?

For example : 

SELECT IF(Obsolete = 'N' or InStock = 'Y';1;0) as Salable, * FROM Product



The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server

SELECT CAST( CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0 END AS bit) as Salable, * FROM Product




You only need to do the CAST if you want the result as a boolean value, if you are happy with an int, this works:



SELECT CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0 END as Salable, * FROM Product



CASE statements can be embedded in other CASE statements and even included in aggregates.

SQL Server Denali adds the IIF statement which is also available in access: 

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Selable, * from Product






Hope you have enjoyed to read this post,
let me know if you have more suitable suggestions.

you might be more interested in SQL, SQL Server, database


Good Day [/ Night] ! Happy Google + ing !

:D

How can I remove duplicate rows?

How can I remove duplicate rows?

Some Days ago , was searching for the best way to remove duplicate rows from a fairly large table (i.e. 300,000+ rows).
The rows of course will not be perfect duplicates because of the existence of the RowID identity field. and i found two good solutions.

Lets say a Table

MyTable
-----------
RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null

Solution 1 :

--Assuming no nulls, you GROUP BY the unique columns, and SELECT the MIN (or MAX) 
--RowId as the row to keep. Then, just delete everything that didn't have a row id:


DELETE MyTable 
FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

--Use
CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn))) 
--instead of MIN(RowId) if you have a GUID instead of an integer

Solution 2 :

delete from dupes
from MyTable dupes, MyTable fullTable
where dupes.dupField = fullTable.dupField 
  and dupes.secondDupField  = fullTable.secondDupField 
  and dupes.uniqueField > fullTable.uniqueField

Hope you have enjoyed to read this post,
let me know if you have more suitable suggestions.

you might be more interested in MSSQL, database, SQL, TIPS AND TRICS, MySQL

Good Day [/ Night] ! Happy Google + ing ! 

How to prevent SQL injection in PHP ?

How to prevent SQL injection in PHP?

Almost, Experienced Developers are knowing this, and also implementing its solution.

We can use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:



Using PDO:

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array(':name' => $name));

foreach ($stmt as $row) {
    // do something with $row
}

Using mysqli:

$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name);

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // do something with $row
}

Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In the above example the error mode isn't strictly necessary, but it is advised to add it. This way the script will not stop with a Fatal Error when something goes wrong. And gives the developer the chance to catch any error(s) which are thrown as PDOExceptions.

What is mandatory however is the setAttribute() line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it the the MySQL server (giving a possible attacker no chance to inject malicious SQL).

Although you can set the charset in the options of the constructor it's important to note that 'older' versions of PHP (< 5.3.6) silently ignored the charset parameter in the DSN.

Hope this post helped,
you might be more interested in SQL Injections, PHP Development, SQL, database.
you can write me for freelancing projects.

Good day ! Happy Google + ing !