Validation with the MySQL IF function Jan29 '07

Validation is important when submitting to a database to ensure the integrity of the data.

It's important to validate using as many tools as possible, namely: JavaScript, and a server-side scripting language, such as PHP or ASP.

MySQL can also validate data, using simple IF functions within queries.

For example, here is a traditional approach to validating against blank strings, using PHP:

if ( $_REQUEST["name"] == "" )
{
    $name = "Matthom";
}
else
{
    $name = $_REQUEST["name"];
}

$query = "SELECT * FROM names WHERE name = '" . $name . "'";

Instead of writing all that code above, you can validate within the SQL query itself:

$query = "SELECT * FROM names WHERE name = IF( '" . $_REQUEST["name"] . "' = '', 'Matthom', '" . $_REQUEST["name"] . "' )";

Here we check to see if $_REQUEST["name"] is empty (equal to ''), and if so, we use the value 'Matthom'. If it's not empty, then we use whatever that value is.

The MySQL IF function uses similar syntax to Excel IF statements.

IF ( expression, value_if_true, value_if_false )

The MySQL IF function is not only limited to simple validation. You can truly empower your queries by using the IF function in many different ways.

Categories: MySQL , Programming

Add Feedback (view all)

Leave feedback

Feedback

Input format: The editor controls below will assist with Markdown syntax.

Status

Sub-status

Your info

matthom is published and produced by Matt Thommes - an independent publishing enthusiast, mobile blogger, content creator, informative writer, web developer from Chicago. Never one to conform, Matt intends to promote the effect the web has on our lives, in an effort to intensify, instruct, and clarify all that is happening around us.

Contact Matt

Similar Entries

Stats

517 unique visits since August 2008

Syndicate

Advertisements