CODEWITHSUNDEEP
phpmysqlhtml
user147685
user147685

Reputation: 469

PHP - Filtering data in table

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

Upvotes: 3

Views: 17158

Answers (5)

ashawe
ashawe

Reputation: 359

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

There are basically 3 steps:

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

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

Upvotes: 0

Syno
Syno

Reputation: 1136

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%'
");

Upvotes: 1

dunos
dunos

Reputation: 755

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.

Upvotes: 2

Asteriskk
Asteriskk

Reputation: 387

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");

Upvotes: 4

Sascha Galley
Sascha Galley

Reputation: 16091

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

Upvotes: 7

Related Questions