select columns in table from array list

[Juarez]

Hi 

I have a task that a user provides a text file with a list of mysql column names from a table. I need to select and display the results from the selected columns. the column names vary each time they are provided. I have decided to load the column name list to an array and would like to use this to  echo out the columns. I have made an attempt at this below but I'm not sure how to make it work. 

Any help would be appreciated. 

//load patient list to array 

$file = fopen("upload/PatList.txt", "r");
$all_pats = array();
while (!feof($file)) {
   $all_pats[] = fgets($file);
   }
fclose($file);

//var_dump($all_pats);

}



// select columns from array elements
 $qry=mysql_query("SELECT *
FROM mytable", $con);

 while($row=mysql_fetch_array($qry))
 {
echo "<td>".@$row{$column_name}. "<br></td>";

}

[requinix]

One loop for the rows in the resultset, another loop inside for the different columns in $all_pats.

 

There is probably a cleverer way that I'm not thinking of.

 

[edit] I'm a little concerned about the whole "someone is providing the column names in an uploaded text file" bit. Can you elaborate on that?

[Juarez]

The table has  20 columns. id, column1, column2, column3... etc. The text file has a list with maybe 5 columns created by the user. I will echo out  the ID column by default  (sorry not shown in the code above)  and also echo out  the 5 columns named in the text file/ array ie column2, column4, column5, column8, column9. 

[Barand]

What are the typical contents of the text file? What format?

[Juarez]

header with a single value list. 

 

ColumnName

column2

column4

column5

column8

column9

[Barand]

$cols = file('PatList.txt');

array_shift($cols);  // remove header

$cols = array_map('trim', $cols);

$selection = join(',', $cols);


$query = "SELECT id,$selection FROM mytable";

[davidannis]

make sure that you sanitize the input from the text file so somebody can't upload:

Header

column1

column2 ; TRUNCATE mytable

[Juarez]

thanks for your help so far it works very well. Only I am having a problem displaying my selected columns listed in the array @cols in my output column. I am trying to use a for each loop within the while mysql_fetch_array  loop to echo each column and header , but without success. This is what I have so far..

 

// load column names from text file into array

$cols = file('upload/PatList.txt');
array_shift($cols);  // remove header
$cols = array_map('trim', $cols);
$selection = join(',', $cols);

// create query

$qry=mysql_query("SELECT ID, $selection
FROM mytable", $con);


// display table 
echo "<table border='3'  width = '50'>";

// show headers 
echo "<tr>  <th>ID</th>  <th>$cols</th> </tr>";


//display query results in table

 while($row=mysql_fetch_array($qry))
 {
echo "<tr>";
echo "<td>".$row{'ID'}. "<br></td>";

foreach($row as $cols) {
echo "<td>".$cols."<br></td>";
}



echo "</tr>";
}
echo "</table>";

[Barand]

  On 4/20/2013 at 1:51 AM, davidannis said:

make sure that you sanitize the input from the text file so somebody can't upload:

Header

column1

column2 ; TRUNCATE mytable

Good point.

 

Change to

$selection = '`' . join('`,`', $cols) . '`';

[Barand]

avoid fetch_array, it fetches the twice (with numeric keys and fieldname keys)

 

try

// load column names from text file into array

$cols = file('upload/PatList.txt');
array_shift($cols);  // remove header
$cols = array_map('trim', $cols);
$selection = join(',', $cols);

// create query

$qry=mysql_query("SELECT ID, $selection
FROM mytable", $con);


// display table 
echo "<table border='3'  width = '50'>";

// show headers 
echo "<tr>  <th>ID</th>  <th>" . join('</th><th>', $cols) . "</th> </tr>";


//display query results in table

while($row=mysql_fetch_row($qry))
{
    echo "<tr><td>" . join('</td><td>', $row) . "</td></tr>";
}
echo "</table>";

[Juarez]

Thats great works well with fetch_row. 

and thanks for the security tips 

 

thanks Barand