Trying to create a dynamically generated mySQL select statement using PHP

Question

I have 5 variables which can either be true or false and I have to generate different sql SELECT statements for each different possible outcome. Right now I have a ton of if else statements but I'm wondering if theres a smarter way to do this?

so for example, i have

if (x=true AND y=false AND z=false AND a=false AND b =false) {
    $sql= "SELECT...."
} else if(x=true AND y=true AND z=false AND a=false AND b=false) {
    $sql= "SELECT...."
}

The first select statement is if the user entered text and didnt select anything else:

$sql="SELECT CompanyName, Keywords, Product, Industry, Link, region, hot FROM searchtest_tbl WHERE Keywords LIKE '%$formSearch%' OR CompanyName LIKE '%$formSearch%' OR Product LIKE '%$formSearch%' OR Industry LIKE '%$formSearch%' ORDER BY hot DESC, CompanyName";

this statement is if they only selected from the Industry dropdown:

$sql="SELECT CompanyName, Product, Industry, Link, hot, region FROM searchtest_tbl WHERE Industry='$formIndustry' ORDER BY hot DESC, CompanyName";

and here is one if they entered text, selected an industry, but didnt select anythign else:

$sql="SELECT CompanyName, Product, Industry, Link, hot, region FROM searchtest_tbl WHERE Industry='$formIndustry' AND (Keywords LIKE '%$formSearch%' OR CompanyName LIKE '%$formSearch%' OR Product LIKE '%$formSearch%') ORDER BY hot DESC, CompanyName";

The rest are basically like this, but if the other fields are selected it will say WHERE Product='$formProduct' AND.... etc

What are the Select statements? Maybe the Select statement itself can handle the cases but we need to see exactly what you are attempting to do in the Select — Veign
– Veign, Commented Jun 13, 2011 at 15:35
You'll want to post an example of the sql statement for best results. — Kzqai
– Kzqai, Commented Jun 13, 2011 at 15:36
Another thing I would suggest to make for better answers is to give the source of the various booleans. Are they parsed from an input form? From another database select? Where? Because there might be a better format for them that would facilitate a much cleaner creation of a sql statement (e.g. if they come in an array to begin with, or the like). I never create myself a list of boolean variables like that to start with if I can help it. — Kzqai
– Kzqai, Commented Jun 13, 2011 at 15:44

George Cummins · Accepted Answer · 2011-06-13 15:39:19Z

1

Perhaps you can break down your logic into smaller components. For example, if x=true corresponds to a specific part of your select statement, and y=false corresponds to a different part, build the select statement piece by piece:

$sql_statement = "SELECT ";

$sql_statement .= ($x) ? "`column_a` " : "`column_a`, `column_b` ";

$sql_statement .= "FROM ";

$sql_statement .= ($y) ? "`table_a` " : "`table_b` ";

If this approach is not suitable for you, can you post additional code so we can make a better judgement?

answered Jun 13, 2011 at 15:39

George Cummins

29k8 gold badges74 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SQLnoob Over a year ago

i like this approach. I'm going to break it down to one if/else statement that will write pieces of the sql statement as it goes. genius!

Tomas Reimers · Accepted Answer · 2011-06-13 15:47:26Z

I'm going to go right out and assume that your true and false statements don't relate to your SQL, because if they did you could simply replace them with variables that could be placed directly into your SQL. For Example

Instead of having a true $x variable mean ASC order and a false variable mean DESC order. You could create a new variable (we'll call it a) and put the value ASC or DESC in it. That way instead of having

if($x){
     //sql
}
else{
     //sql
}

You could have someting to the effect of

SELECT * FROM search $a;

Now if for some reason these variables are completely random and unrelated.

And you have to use ifs and else ifs, I would suggest changing your code for comprehensibility and logic. But I'll be able to help you mor if you post your original code, or at least the variables.

dqhendricks · Accepted Answer · 2011-06-13 15:47:46Z

0

You might want to consider using binary numbers and bitwise operators.

FLAG_1 = bindec('0001') = 1
FLAG_2 = bindec('0010') = 2
FLAG_3 = bindec('0100') = 4
FLAG_4 = bindec('1000') = 8

then you can combine all of the flags into one int using the bitwise operator "|"

so if flag 2 and 4 are on

$combined_flags = (FLAG_2 | FLAG_4) = bindec('1010') = 10;

you could then use the combined flag int to determine which SQL statement to generate

you can even test for individual flags using the bitwise & operator

if ($combined_flags & FLAG_2) // true
if ($combined_flags & FLAG_1) // false

answered Jun 13, 2011 at 15:47

dqhendricks

19.2k11 gold badges54 silver badges88 bronze badges

1 Comment

dqhendricks Over a year ago

you can also use bitwise operators in MySQL

Collectives™ on Stack Overflow

Trying to create a dynamically generated mySQL select statement using PHP

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related