Page 1 of 1
select statement with unknown number of columns to select
Posted: Tue Oct 12, 2010 12:12 am
by Loopy
Anyone know how to make a sql select statement accept a user determined number of columns?
For example, I have a form that allows end users to select the data they want to see and then run a query on a database. So maybe someone selects from the form that they would like to see name, address, age, gender, phone_number. That SQL statement is easy to write but what if someone else only wants name and gender?
I have about 15 different columns in this database, and some people only need access to some information, others might need access to all, but not at the same time (since it's a lot of data). So I'm trying to write a single function that accepts an unknown number of parameters (through an array of arguments passed to it).. what I can't figure out is how to modify the sql statement so that it might be looking up one item, and it might be looking up 15.
Re: select statement with unknown number of columns to select
Posted: Tue Oct 12, 2010 2:08 am
by Jackolantern
There may be a more efficient way to do this, but you can just use the wildcard for the columns to pull them all, and then add get the results into an associative array. Then you can use a two FOR-EACH loops, and within the body of those loops, compare the associative arrays, and have an IF structure that will only print the results of a column when the associative array key from the results matches an associative array key from what the user wants to see. It has been a little while since I have written some PHP, but I think it may look like this (may be a bit of pseudo-code depending on how rusty I have gotten):
Code: Select all
//the associative array containing the column names the user wants/is allowed to see
$userColumns;
//get results from database
$results = mysqli_fetch_assoc(mysqli_query($db, "SELECT * FROM players WHERE name='$name'"));
foreach ($results as $key1 => $value1) {
foreach ($userColumns as $key2 => $value2) {
if ($key1 == $key2) {
echo "".$key1." : ".$value1."<br />";
}
}
}
Re: select statement with unknown number of columns to select
Posted: Tue Oct 12, 2010 3:01 am
by Jackolantern
Ok, updated code. I realized when I was doing some testing that the array holding the values your user enters corresponding to the columns they want to view will probably not be an associative array. If it is a regular, numerically-keyed array, I have tested this code on my database and it is working:
Code: Select all
<?php
include_once 'connect.php';
//the name of a player in my db for testing
$name = "Pizza";
//the associative array containing the column names the user wants/is allowed to see.
//this would be taken from user input in production
$userColumns = array("playerid", "name", "email");
//get results from database
$results = mysqli_fetch_assoc(mysqli_query($db, "SELECT * FROM players WHERE name='Pizza'"));
if (!$results) {
echo "No results were found.";
exit();
}
//looping the results from the sql query
foreach ($results as $key => $value) {
//nested loop for the user-entered columns
for ($i = 0; $i < count($userColumns); $i++) {
//See if the column names match
if ($key == $userColumns[$i]) {
//...if they do, print the results
echo " ".$key." : ".$value."!<br />";
}
}
}
?>
If you need any more help with it or don't understand any part, let me know.
Re: select statement with unknown number of columns to select
Posted: Tue Oct 12, 2010 4:23 pm
by Loopy
Thanks Jack, I'll give this code a go and let you know.
Re: select statement with unknown number of columns to select
Posted: Tue Oct 12, 2010 5:04 pm
by Loopy
Works like a charm... I was working on this for a professional tool, but it works so nice that I'm going to update some obsolete code in my game with it too.
Thanks!
Re: select statement with unknown number of columns to select
Posted: Tue Oct 12, 2010 5:09 pm
by Jackolantern
Glad it worked out, and your most welcome
Re: select statement with unknown number of columns to select
Posted: Tue Oct 12, 2010 8:21 pm
by Loopy
I had it pointed out to me that I could accomplish the same thing but avoid using a select "all" by imploding the contents of the array that contain the column names, which seemed like a good idea, so I just plugged that into your code:
Code: Select all
$columns = array('item1','item2','item4'); // Build this from posted data
$cols = implode(',',$columns); // item1,item2,item4
$sql = "SELECT $cols FROM ...";
No need to check for column names matching the corresponding array element either, I don't think.
Re: select statement with unknown number of columns to select
Posted: Tue Oct 12, 2010 8:59 pm
by Jackolantern
Ahh, that was the "more efficient" way I was thinking of . I couldn't remember the oddly-named "implode()" function