MySQL(i?) Filter on an existing query

Question

I want to create a filter which filters my ouput from the database. I have no idea how to create this. I have searched some other topics but they were not so helpful, hopefully someone can help me out.

I have the following code which retrieves the information from my database and ouputs it into a table. (please ignore the table set-up and such, still need to clean the nesting and stuff..)

Code (retrieve.php):

<?php
// Connect to database server
mysql_connect("xx", "xx", "xx") or die (mysql_error ());

// Select database
mysql_select_db("xx") or die(mysql_error());

// SQL query
$strSQL = "SELECT * FROM informatie ORDER BY id DESC;";

// Execute the query (the recordset $rs contains the result)
$rs = mysql_query($strSQL);

//OUDE LOOP SPACE

   // Write the value of the column FirstName (which is now in the array $row)
  echo "<table width='100%' border='0' cellpadding='10' cellspacing='0'><tr style='background-color:#f04d44; color:#ffffff;'>";
  echo "<td style='border-right:1px solid #ff9a88;'><strong>Klant</strong></td><td style='border-right:1px solid #ff9a88;'><strong>Datum</strong></td><td style='border-right:1px solid #ff9a88;'><strong>Eventviewer Nagekeken</strong></td><td style='border-right:1px solid #ff9a88;'><strong>Eventviewer Opmerkingen</strong></td><td style='border-right:1px solid #ff9a88;'><strong>Services Nagekeken</strong></td><td style='border-right:1px solid #ff9a88;'><strong>Services Opmerkingen</strong></td><td style='border-right:1px solid #ff9a88;'><strong>Backup Nagekeken</strong></td><td style='border-right:1px solid #ff9a88;'><strong>Backup Opmerkingen</strong></td><td><strong>Check Gedaan Door</strong></td>";
  echo "</tr>";
  // Loop the recordset $rs
  // Each row will be made into an array ($row) using mysql_fetch_array
  while($row = mysql_fetch_array($rs)) {
  echo "<tr style='background-color:#ffffff;'>";
  echo "<td style='border-right:1px solid #cccccc; border-bottom:1px solid #cccccc;'>"; 
  echo $row['klant'];
  echo "</td>";
  echo "<td style='border-right:1px solid #cccccc; border-bottom:1px solid #cccccc;'>";
  echo $row['datum'];
  echo "</td>";
  echo "<td style='border-right:1px solid #cccccc; border-bottom:1px solid #cccccc;'>";
  echo $row['eventviewer_nagekeken'];
  echo "</td>";
  echo "<td style='border-right:1px solid #cccccc; border-bottom:1px solid #cccccc;'>";
  echo $row['eventviewer_opmerkingen'];
  echo "</td>";
  echo "<td style='border-right:1px solid #cccccc; border-bottom:1px solid #cccccc;'>";
  echo $row['services_nagekeken'];
  echo "</td>";
  echo "<td style='border-right:1px solid #cccccc; border-bottom:1px solid #cccccc;'>";
  echo $row['services_opmerkingen'];
  echo "</td>";
  echo "<td style='border-right:1px solid #cccccc; border-bottom:1px solid #cccccc;'>";
  echo $row['backup_nagekeken'];
  echo "</td>";
  echo "<td style='border-right:1px solid #cccccc; border-bottom:1px solid #cccccc;'>";
  echo $row['backup_opmerkingen'];
  echo "</td>";
  echo "<td style='border-bottom:1px solid #cccccc;'>";
  echo $row['check_door'];
  echo "</td>";
  }
  echo "</tr></table>";

// Close the database connection
mysql_close();

?>

Thanks for helping me out!

Glenn, please don't use mysql_* functions anymore and move to mysqli or pdo for [this](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) reason. — marijnz0r, May 28 '15 at 07:19

score 1 · Answer 1 · answered May 28 '15 at 07:24

1

For filtering, most of the time you can use your database. Right now your SQL statement is like this:

$strSQL = "SELECT * FROM informatie ORDER BY id DESC;";

To filter on information from the table informatie, you should alter the SQL statment, for example:

$strSQL = "SELECT * FROM informatie WHERE `eventviewer_nagekeken` = 1 ORDER BY id DESC;";

This will return all rows where eventviewer_nagekeken equals 1. This way you will be able to create various outputs from the pool of data you have in the informatie table. For documentation and examples, see the docs.

answered May 28 '15 at 07:24

marijnz0r

934
10
23

Hello! Thanks for replying. I understand the MySQL statement! However, how can I make a PHP button ( – Glenn May 28 '15 at 07:30
To add a filter on demand, you could use HTML, PHP and forms like [this](http://www.w3schools.com/php/php_forms.asp). But you've got multiple options, like [AJAX](http://www.w3schools.com/php/php_ajax_php.asp) – marijnz0r May 28 '15 at 07:51

score 0 · Answer 2 · answered May 28 '15 at 07:37

you can use where clause generated dynamically as bellow

    $where = ' 1=1 ';
    if(isset($_REQUEST['param1']))
    {
        $where .= " and db_field1 = ".mysql_real_escape_string($_REQUEST['param1']);
    }
    if(isset($_REQUEST['param2']))
    {
       $where .= " and db_field2 = ".mysql_real_escape_string($_REQUEST['param2']);
    }
    $strSQL = "SELECT * FROM informatie ".$where." ORDER BY id DESC;";

here mysql_real_escape_string will avoid mysql injection.

Hope this helps you.

MySQL(i?) Filter on an existing query

2 Answers2