Filtering Data on a browser

PHP
1

Hi
I’m wondering if this can be done, and if so how do I go about doing it

I’ve 50 fields in a MYSQL database and displaying in a table on a browser, there’s quite a bit of data in the table, and the user wants to be able to filter each field on the browser, just like in excel, can this be done for 50 fields using PHP? and if so any tips on how id get it started

Thanks
Damien

2

Very sound advice, although do bear in mind this was just a quick mock up. I’m sure the OP will do their best to accommodate their users accordingly. :wink:

3

good point Ryan
Thanks for that

4

I recommend NOT putting the filter as the sole element in the th tag as Anthony has done. People who are blind use the table headings to navigate the table. Instead of knowing “in this colum are people’s names”, they get a drop down.

5

That would be a horrendous page IMO.
Just build a drop down, build the options off that column, tthen have a button that runs a new query based off the select…


<select>
<?php
$sql = 'select distinct name from tbl';
$result = $mysqli-> query($sql);
while($row=$result->fetch_assoc();){
  echo '<option value="'.$row['name'].'">'.$row['name'].'</option>';
}?>
</select>
6

the user would click on the filter and select from the drop-down list
this list would be everything that would be in that column, and there would be 50 columns of data, with 50 filters

7

Do you want to have it done live, or after mouse click?

I assume you mean give me all names that begin with G.

8

Create a mock up with some dummy data, then gradually work out how to replace each section without breaking it.

You need to figure out what you need PHP to produce for you.

Something like…


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <title>demo</title>
    </head>
    <body>
        <form action="" method="get">
            Filter: <input type="submit" value="apply" />
            <table>
                <thead>
                    <tr>
                        <?php foreach(range('a', 'z') as $column): ?>
                            <th>
                                <select name="filter[<?php echo $column; ?>]">
                                    <option value="all" selected="selected">
                                        all
                                    </option>
                                    <?php foreach(range(1, 9) as $value): ?>
                                        <option value="<?php echo $value; ?>">
                                            <?php echo $value; ?>
                                        </option>
                                    <?php endforeach; ?>
                                </select>
                            </th>
                        <?php endforeach; ?>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach(range(1, 10) as $i): ?>
                        <tr>
                            <?php foreach(range(1, 26) as $j): ?>
                                <td>
                                    <?php echo rand(1, 9); ?>
                                </td>
                            <?php endforeach; ?>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </form>
    </body>
</html>

This will produce…

After that, you just need to read what is contained in the $_GET[‘filter’] array and apply it to your base query.

Good luck! :slight_smile:

9

Thanks Anthony

that’s just what im looking for, now if I can get it working with real data from my table

Thanks

10

You’re most welcome. :wink:

11

I updated my last post with sample code. So you need to list colum heads, a dropp down, then your code for the table.

12

but the user wants to see all 50 columns, but then want to narrow it down using the filters