SQL Conditional Formatting: Enhancing Your Database Queries

Question

SQL Conditional Formatting: Enhancing Your Database Queries

Is it possible to create a dynamic SQL statement based on user input using 3 textbox inputs?

var firstname = document.getElementById('firstname').value
var middle = document.getElementById('middle').value
var lastname = document.getElementById('lastname').value
var SQL

If all 3 fields are filled out:

SQL = "SELECT * FROM TABLE WHERE [Firstname] = '" + firstname + "' AND [Middle] = '" + middle + "' AND [Lastname] = '" + lastname + "'"

If 2 of the 3 fields are filled out:

SQL = "SELECT * FROM TABLE WHERE [Firstname] = '" + firstname + "' AND [Lastname] = '" + lastname + "'"

Is there a more concise way to handle this? Perhaps a shorter solution instead of evaluating each field and defining the SQL?

I was just curious, thought it wouldn't hurt to ask.

Jay

javascript sql mysqli adodb

Answer 1

Answer №1

Perhaps a solution along these lines could be effective...

SQL = "SELECT * FROM TABLE WHERE 

  1 =
  (
  case
     when LEN('" + first + "') > 0  and LEN('" + last + "') > 0 and LEN('" + middle_name + "') > 0 then ([First] = '" + first + "' AND [Last] = '" + last + "' AND [MiddleName] = '" + middle_name + "')
     when LEN('" + first + "') = 0  and LEN('" + last + "') > 0 and LEN('" + middle_name + "') > 0 then ([Last] = '" + last + "' AND [MiddleName] = '" + middle_name + "')
     ...
  end
  )

Alternatively:

SQL = "SELECT * FROM TABLE WHERE 
(
  (LEN('" + first + "') > 0 AND [First] = '" + first + "') 
 OR 
   LEN('" + first + "') = 0
) 
AND (similar for next)
AND (similar for middle)"

Answer 2

Perhaps a solution along these lines could be effective...

SQL = "SELECT * FROM TABLE WHERE 

  1 =
  (
  case
     when LEN('" + first + "') > 0  and LEN('" + last + "') > 0 and LEN('" + middle_name + "') > 0 then ([First] = '" + first + "' AND [Last] = '" + last + "' AND [MiddleName] = '" + middle_name + "')
     when LEN('" + first + "') = 0  and LEN('" + last + "') > 0 and LEN('" + middle_name + "') > 0 then ([Last] = '" + last + "' AND [MiddleName] = '" + middle_name + "')
     ...
  end
  )

Alternatively:

SQL = "SELECT * FROM TABLE WHERE 
(
  (LEN('" + first + "') > 0 AND [First] = '" + first + "') 
 OR 
   LEN('" + first + "') = 0
) 
AND (similar for next)
AND (similar for middle)"

Answer 3

Answer №2

If you pass the values as parameters into the query, it can help optimize performance and prevent spamming of the query cache.

SQL = " SELECT * 
    FROM TABLE 
    WHERE (@firstName is null OR [FirstName] = @firstName) 
      AND (@middle is null OR [Middle] = @middle)
          AND (@lastName is null OR [Lastname] = @lastName"

This method ensures that only one query plan is stored in the cache, improving efficiency.

Answer 4

If you pass the values as parameters into the query, it can help optimize performance and prevent spamming of the query cache.

SQL = " SELECT * 
    FROM TABLE 
    WHERE (@firstName is null OR [FirstName] = @firstName) 
      AND (@middle is null OR [Middle] = @middle)
          AND (@lastName is null OR [Lastname] = @lastName"

This method ensures that only one query plan is stored in the cache, improving efficiency.

Answer 5

Answer №3

Avoid making this mistake. Utilize parameterized queries as a safeguard against SQL injection for better security.

Answer 6

Avoid making this mistake. Utilize parameterized queries as a safeguard against SQL injection for better security.

SQL Conditional Formatting: Enhancing Your Database Queries

Answer №1

Answer №2

Answer №3

Similar questions

When using Express.static, the website functions properly but an error message "GET http://localhost:5000/index.js net::ERR_ABORTED 404 (Not Found)" is displayed

Retrieving data from an array in VueJS

`Switch up the backdrop upon selection`

Transferring a Query between Domains with the Help of JavaScript

Modifying button styles in Angular UI Datepicker

Delete the current code within the specified div element

Exploring the capabilities of the Contentful JavaScript SDK in conjunction with Native

What are the advantages of utilizing buffer geometries in Three.js?

Understanding the timing of records being returned via an Observable in Angular's ngOnInit

The player clicks once and the game is played two times

Search for elements once they have been dynamically loaded using AJAX with the

The validation process is functioning properly, however, the alert or message is failing to appear

Sending information to Bootstrap 4 modal

Is it possible to create a form inside a tooltip?

Discovering and sorting an array in Vue.js based on IDs

Can you explain the concept of a function value?

Rotating a cone in three.js using two points and a specified radius

Determining the availability of a remote source in React: A step-by-step guide

Modifying the value of a local variable results in a corresponding adjustment to the original global variable's value

I am currently working on a Node.js application generated with express-generator and am experimenting with integrating Primus websocket