Mysql select with unknown number of columns

PHP
1

Is there a way that I can use a single sql statement to select a variable number of columns and output those selections into an array?

For instance, I have a form that allows users to select from a database information that they may be interested in. They may be interested in one item, they may be interested in ten.

If the user were interested in two items, it might look like:


$sql="select item1, item2, from table where....

Whereas, if the user selected four items, that same select statement would look like:


$sql="select item1, item2, item3, item4 from table where...

I could load the selections into an array, but I don’t know how to dynamically change the SQL statement. I could count the number of items in the array, and then do an if/else statement to cover all the data that might be asked for (up to 12 different columns, so that seems like a lot of code for something like this).

Thanks in advance for the help.

2

Thanks! That’s actually exactly what I was looking for in the beginning. I plugged that into what I had since it fines-tunes my other approach and, like you said, eliminates the select *.

3

Just to answer the original question and perhaps avoid using SELECT *


$columns = array('item1','item2','item4');  // Build this from posted data
$cols = implode(',',$columns); // item1,item2,item4
$sql = "SELECT $cols FROM ...";
4

Thanks guys! Much like Guido suggested, I was able to find a solution to this problem by selecting all the columns in the passed table name, and then grabbing the data where the passed array containing the user columns matched the column selected from the table.

If someone needs to see how I did this let me know and I’ll post some code.

5

you’re asking a php question in the mysql forum :slight_smile:

i don’t do php but i believe the functionality you’re looking for is the blowup() function

oh, wait, i think it’s called explode()

you explode your array into the sql statement, yeah, that’s it

:smiley:

6

You’ve got a form with 20 checkboxes, all with the same name, for example ‘options’ (don’t forget the square brackets).
In your PHP script, use a foreach loop to loop through the $_POST[‘options’] array, and construct your SELECT clause dynamically. A bit like you already started doing, but you’ll need to use a loop to make it really dynamic.
And you’ll have to check if the user did actually select at least one option, otherwise the query will end with an error.

7

let’s ask a moderator to move this thread to the php forum

i’m sure all your concerns will evaporate

8

Say you have a form with 20 different criteria that someone can choose to query a database on. For example:

age
gender
race
date_of_birth
mailing addres
billing address
etc
etc

A user can use checkboxes to query the database on what they’re interested in… so maybe someone wants to query for age, gender, and race.

No problem, those items are loaded into a php array, and sent to the function:


$sql="select $array_item[0], $array_item[1], $array_item[2] from table where....

Assuming there are always 3 criteria to query on, that works great. But, what if someone comes along and wants to query on 5 items instead of 3? How can I dynamically change that SQL statement to now select 5 items instead of 3? I know how to hardcode 5 items in, or 10, or 15… but what I’m trying to do is dynamically build the SQL statement based on how many items the end user selects.

So in other words, a single sql statement that queries a database a single time that can dynamically accommodate selecting a variable number of columns.

Edit: The only way I can think of to do it like this, is to not pass the column names as elements in an array, but rather as a single string that contains all the column names delimited by comma, and then just plug it into sql statement after concatinating it.

For instance. If the user selects age, gender, and race, then you store that as a string:


$selection1="age"
$selection2="gender"
$selection3="race"

$final=$selection1 . "," . $selection2 . "," . $selection3 . " ";

Then pass $final to the sql statement:


$sql="select $final from table where";

Which would yield an SQL statement of:
$sql=select age,gender,race from table where

Seems to be a crappy way to do it, but maybe it works.

9

i’m not sure i understand what you’re asking

SELECT item1 FROM daTable …

SELECT item1,item2 FROM daTable …

SELECT item1,item2,item3 FROM daTable …

SELECT item1,item2,item3,item4 FROM daTable …

that’s the general pattern, and there’s nothing really complicated about it

10

Thanks for the help!

I asked in this particular forum because I can get the data into the function via php using an array. What I’m having trouble doing is writing the sql statement that can process a variable number of parameters. For instance, that array, full of column names that need to be selected, may contain 1 column name, and it may contain 50.

In other words, how do you write an SQL select statement that wiil accept a variable number of column names, without querying the database more than once?