I am one of those relative php / mysql novices whose background has been in support rather than development. I've have however managed to put together a lot of things I'm trying to achieve in the most because of great resource sites like these, therefore thank you to all past, present and future in advance. However, I either can't construct a suitable google query to find the info I need or I'm missing a simple trick somewhere I reckon.
In short, I have a mysql db with a table that has around 15 columns (fields). I have a php front end that I can query the database with, either returning all rows or just those rows containing general searched for items, across all fields. All good, displays well, works well. However, I want to give the user the option of choosing just those columns (fields) that they want returned in their results as well. I therefore have a search page with columns name displayed with checkboxes and values (column names) assigned accordingly. Code as below:
<form action="result.php" method="post"> Search for* : <input type="text" name="searchterm" />
<input type="submit" name="submit" value="Search Database" /> <br /><br /><br />
<input type=button value="Select ALL" onclick="select_all( check, true ); return false;">
<input type=button value="Clear ALL" onclick="select_all( check, false ); return false;">
<table>
<tr><th>FirstName<br/><input type="checkbox" id="check" name="field[]" value="FirstName,"></th>
<th>LastName<br/><center><input type="checkbox" id="check" name="field[]" value="LastName,"></center></th>
<th>Age<br/><center><input type="checkbox" id="check" name="field[]" value="Age,"> </center></th>
<th>Instrument<br/><center><input type="checkbox" id="check" name="field[]" value="Instrument,"></center></th>
<th>Grade<br/><center><input type="checkbox" id="check" name="field[]" value="Grade,"></center></th>
<th>LandlineNo<br/><center><input type="checkbox" id="check" name="field[]" value="LandlineNo,"></center></th>
<th>MobileNo<br/><center><input type="checkbox" id="check" name="field[]" value="MobielNo,"></center></th>
<th>Email<br/><center><input type="checkbox" id="check" name="field[]" value="Email,"></center></th>
<th>AddressLine1<br/><center><input type="checkbox" id="check" name="field[]" value="AddressLine1,"></center></th>
<th>AddressLine2<br/><center><input type="checkbox" id="check" name="field[]" value="AddressLine2,"></center></th>
<th>Town<br/><center><input type="checkbox" id="check" name="field[]" value="Town,"></center></th>
<th>County<br/><center><input type="checkbox" id="check" name="field[]" value="County,"></center></th>
<th>Postcode<br/><center><input type="checkbox" id="check" name="field[]" value="Postcode,"></center></th>
<th>Price<br/><center><input type="checkbox" id="check" name="field[]" value="Price,"></center></th>
<th>Paid<br/><center><input type="checkbox" id="check" name="field[]" value="Paid,"></center></th>
<th>LastUpdated<br/><center><input type="checkbox" id="check" name="field[]" value="LastUpdated,"></center></th>
</tr>
</table>
</form>
All the checkboxes are assigned field[] as the name therefore outputting to array in result.php. After a little bit of further reading I can get the array to display (echo) the selected columns with a comma separation, per:
for ($i=0; $i<count($_POST['field']); $i++){
$columns = addslashes($_POST['field'][$i]);
echo $columns;
}
// output example
LastName,Age,Instrument,Email
However, I am now at a loss as to how to get only the selected columns to be retrieved in the resulting view. Could anyone kindly help and pint me in the right direction. I've got this but that doesn't work as all columns are returned.
$result = mysql_query("select * from TABLE where FirstName like '%$searchterm%' or LastName like '%$searchterm%' or Age like '%$searchterm%' or Instrument like '%$searchterm%' or Grade like '%$searchterm%' or LandlineNo like '%$searchterm%' or MobileNo like '%$searchterm%' or Email like '%$searchterm%' or AddressLine1 like '%$searchterm%' or AddressLine2 like '%$searchterm%' or Town like '%$searchterm%' or County like '%$searchterm%' or Postcode like '%$searchterm%' or Price like '%$searchterm%' or Paid like '%$searchterm%'");
echo '<table>';
while ($row = mysql_fetch_assoc($result)) {
if (empty($columns)) {
$columns = array_keys($row);
echo '<tr><th>'.implode('</th><th>', $columns).'</th></tr>';
}
$resultset[] = $row;
echo '<tr><td>'.implode('</td><td>', $row).'</td></tr>';
}
echo '</table>';
Final touch would also be to retrieve and populate the column headings to the table depending on the result set. Thanks in advance for any guidance, suggestions, improvements.
Jim
