Show / Hide Table Content

Make Dynamic Table from MySQL

[Frank_Rizzo]

I can use javascript to show / hide items in a div etc. but how do I show hide upto a dozen table columns?

e.g.

Name State City Amount Weight Cost
----------------------------------
Joe   FL  Miami  100 20  $2000
Keren  MA  Boston 200 25  $5000

Anyone generating this report has previously configured the display by allowing the viewing of Name, State, City, Amount, Weight, Cost. They have chosen not to inlcude Tax, Refund, Status. This has been set via a form:

Display Options: Name [X] State [X] City [X] Amount [X] Weight [X] Tax [ ] Refund [ ] Cost [X] Status [ ]

I gather the checkbox info and store this in a database and $SESSION variable so that everytime the user generates the report it shows exactly the column fields they want to see.

That's the theory but in practice this is generating a huge file with hundreds of if() { print } statements

//Display Headings
print ($show_name == 1? "<TD>Name</TD>" : "");
print ($show_state == 1? "<TD>State</TD>" : "");
.
.
.
//Display Data
while ($row = @mysql_fetch_row($result)) {
   print ($show_name == 1? "<TD>$row[1]</TD>" : "");
   print ($show_state == 1? "<TD>$row[2]</TD>" : "");
.
.
.

I'm all for efficiency. Whilst this method works it's not that efficient. Is there an easier way? Ajax?

Basically I need to show part table data (multiple columns and rows) based on a users preferences. The table data is pulled from a database, the user preferences is pulled from a session variable.

[eelixduppy]

Hello Frank_Rizzo,

I've thrown together an example of a completely dynamic table script based on user input from a form taken from a mysql database. Here's the code, it's pretty much self explanatory with the comments.

---

form.html

<form action="index.php" method="post">
ID:<input type="checkbox" name="cols[]" value="id" /><br/>
ON:<input type="checkbox" name="cols[]" value="on" /><br/>
OFF:<input type="checkbox" name="cols[]" value="off" /><br/>
TITLE:<input type="checkbox" name="cols[]" value="title" /><br/>
PRICE:<input type="checkbox" name="cols[]" value="price" /><br/>
STATUS:<input type="checkbox" name="cols[]" value="status" /><br/>
CAT:<input type="checkbox" name="cols[]" value="cat" /><br/>
DESC:<input type="checkbox" name="cols[]" value="desc" /><br/>
<hr/>
Database:<input type="text" name="db" /><br/>
Table:<input type="text" name="table" /><br/>
<input type="submit" value="Generate Table!" />
</form>

---

index.php

<?php
#
if([url=http://www.php.net/isset]isset[/url]($_POST['cols'])) {
#
#get superglobals
$qDatabase = $_POST['db'];
$qTable = $_POST['table'];
$cols = $_POST['cols'];
#
#connect to db
$link = [url=http://www.php.net/mysql-connect]mysql_connect[/url]('localhost','username','password');
$qDatabase = [url=http://www.php.net/mysql-real-escape-string]mysql_real_escape_string[/url]($qDatabase);
[url=http://www.php.net/mysql-select-db]mysql_select_db[/url]($qDatabase);
#
#create/escape query variables
$qCols = '`'.[url=http://www.php.net/implode]implode[/url]('`,`',$cols).'`';
$qCols = mysql_real_escape_string($qCols);
$qTable = mysql_real_escape_string($qTable);
#
#define/run query
$query = 'SELECT '.$qCols.' FROM '.$qTable;
$result = [url=http://www.php.net/mysql-query]mysql_query[/url]($query) or [url=http://www.php.net/die]die[/url]([url=http://www.php.net/mysql-error]mysql_error[/url]());
#
#loop through results and print accordingly
echo '<table border="1" align="center">';
echo '<tr><td><b>'.implode('</b></td><td><b>',$cols).'</b></td></tr>';
#
while($row = mysql_fetch_row($result)) {
echo '<tr><td>'.implode('</td><td>',$row).'</td></tr>';
}
echo '</table>';
}
#
?>

---

Note: Improvements can be made to this script such as validating the database and table name before running the actually query, which I'd recommend on a production site; I just left those out to not complicate it any further ;)

Hope you can use this guide.

Best of luck! :)