PHP - Filtering data in table

Question

I need help on how to filter data in a database. I want a filter like in the excel spreadsheet.

For example, I have this sample code on how to get data from w3school on how to select data from database. Here is my sample code:

 <?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("TableTest", $con);

$result = mysql_query("SELECT * FROM Colors ");

echo "<table border='1'>
<tr>
<th>Colors</th>
<th>Type</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['colors'] . "</td>";
  echo "<td>" . $row['type'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

enter image description here

I also found this sample too in w3school, but I dont want to filter the database with a drop down.

I'd like to make it like an excel filter. When I select the column 'Colors' to filter on 'Red', it will display only the color red. So i was wondering if anyone could help me on how to start.

Thanks all

This article tells you all about using jQuery and it's plugins to add functionality to an HTML table on your website. tripwiremagazine.com/2009/12/… — GordyD
– GordyD, Commented Jun 10, 2011 at 13:19
Or use this plug-in. dev.sencha.com/deploy/ext-4.0.0/examples/grid-filtering/… — GordyD
– GordyD, Commented Jun 10, 2011 at 13:26

Sascha Galley · Accepted Answer · 2011-06-10 13:14:30Z

7

$result = mysql_query("SELECT * FROM Colors WHERE color='Red'");

answered Jun 10, 2011 at 13:14

Sascha Galley

16.2k5 gold badges39 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Asteriskk · Accepted Answer · 2011-06-10 13:19:19Z

4

When the user chooses a color, redirect him on the same page with a $_GET variable containing the color he has choosen. Then, check in your code if the $_GET variable containing the color exist :

if(isset($_GET['color']))
  result = mysql_query("SELECT * FROM Colors WHERE color='".htmlentities($_GET['color'])."'");
else
  result = mysql_query("SELECT * FROM Colors");

answered Jun 10, 2011 at 13:19

Asteriskk

3871 gold badge2 silver badges10 bronze badges

Comments

dunos · Accepted Answer · 2011-06-10 13:22:35Z

2

You will need to create a form containing a list box containing a list of colours that the user can pick from. In turn that form will need to post a variable back to the page that PHP will then give to MySQL to filter the result table.

Filtering the result table alone to just red would be done using :

$result = mysql_query("SELECT * FROM Colors WHERE color='Red'");

However to filter that based on a form posting to the page would require something like this:

$result = mysql_query("SELECT * FROM Colors WHERE color='".mysql_escape_string($_REQUEST['color'])."'");

Where "color" is the name of the variable that has been posted to the page containing the name of the colour you wish to filter by.

answered Jun 10, 2011 at 13:22

dunos

7554 silver badges8 bronze badges

Comments

Syno · Accepted Answer · 2017-03-07 14:30:45Z

1

I know I'm way too late but maybe there are still people looking for a solution. All i know about this case is that you simply

$result = mysql_query("
         SELECT * FROM database_name
         WHERE Color LIKE 'Red%'
");

answered Mar 7, 2017 at 14:30

Syno

1,1441 gold badge10 silver badges27 bronze badges

Comments

Pang · Accepted Answer · 2017-06-09 05:09:57Z

0

I am pretty sure you're looking for "this". You need JavaScript for that to work.

There are basically 3 steps:

Make an HTML table to display contents.
Use CSS to style that table.
Use JavaScript for searching and filtering the table.

See more on the link provided.
https://www.w3schools.com/howto/howto_js_filter_table.asp

edited Jun 9, 2017 at 5:09

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Jun 9, 2017 at 4:40

ashawe

3593 silver badges16 bronze badges

Collectives™ on Stack Overflow

PHP - Filtering data in table

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related