Dynamic SQL Command

Question

I want to be able to get only values where there is a '1' in multiple columns. My column names are stored dynamically in a variable though. How would I go about this?

This is what I have. Columns are layed out like this
$Columns = "Computer, Science, Algebra, Biology, Networking";
//This is Dynamic so $Columns may be like this next time
$Columns = "Biology, Networking";
$SQL = "SELECT * FROM users WHERE '1' IN(".$Columns.")";

Right now, it selects any users that have a "1" in any of the columns

I only want to retrieve the users that have a "1" in ALL of the variables in the array $Columns not just one of them

I cant compare it like that, my column names come from one variable. Dynamically generated based on a drop down. So all columns are in one variable called $columns — TaLeNT, Jun 25 '18 at 01:48
It *can* be compared "like that". Generate the SQL appropriately. Start by creating an *array* of Column Names. Then appropriately join/iterate the array to build the SQL. — user2864740, Jun 25 '18 at 01:49
maybe using explode() i dont know. I've tried that with no success as well. — TaLeNT, Jun 25 '18 at 01:49
I wrote $Columns = "blah, blah blah"; up top for an example. My $columns is in an array though. — TaLeNT, Jun 25 '18 at 01:51
You have a column for every subject in the users table? That's no good design. Consider using a linking table. — sticky bit, Jun 25 '18 at 01:52
(Correction: I used 'OR', it should have been 'AND'.. for "all values") — user2864740, Jun 25 '18 at 01:53
I understand OR and AND statements... I cannot use them with this variable. — TaLeNT, Jun 25 '18 at 02:01
Refer to [this](https://stackoverflow.com/questions/9736284/mysql-where-in) question on how `IN` works. It will return true if it found a match. If you want the query to succeed only when all the columns contain your value then you need to use `AND` — hungrykoala, Jun 25 '18 at 02:06
@hungrykoala How do I use and with my above variable, since all the column names are inside one variable? — TaLeNT, Jun 25 '18 at 02:08
Well, you could start off by exploding your variable so that you can separate your columns and do a loop to generate a new string like Biology = '1' AND Networking = '1' — hungrykoala, Jun 25 '18 at 02:12

score 0 · Answer 1 · answered Jun 25 '18 at 02:38

Since your $columns variable can be different sizes, you will need to build your sql statement based on the number of columns in the array in a for loop:

// SET your first column IN
$sql = "SELECT * FROM users WHERE '1' IN (".$Columns(0).")"

//Run the for loop to build sql on all subsequent columns
// size of will give you the number of columns in your array
$max = sizeof($columns);
for($i = 1; $i < $max;$i++)
{
//Use the "AND" to make sure "1" is in all columns
$sql.=" AND '1' IN (".$Columns($i).")
}

$sql.=";"

My code may not be perfect, but should get you in the right direction.

Dynamic SQL Command

1 Answers1