Rainman Conroy Rainman Conroy - 1 year ago 68
SQL Question

How to Filter mysql/php table data using a form

Here is the background of what I am doing:

  1. A mySql table is being queried by PHP to present an NFL Datatable on a website.

  2. I want to filter out players that don't have their Teams checked

    • see picture below, in the picture only Players belonging to BAL, CIN and DEN would be shown in the Table).

  3. But I can only show 1 Team at a Time

    • if I had BAL, CIN, DEN checked in the dropdown, then I would only have a Datatable with one of the Teams Players.

Can someone please help me out?

<?php /*
Template Name: TestPHPFilter}
*/ ?>

<?php get_header(); ?>

<?php genesis_before_content_sidebar_wrap(); ?>
<div id="content-sidebar-wrap">

<?php genesis_before_content(); ?>
<div class='statsheader'>
<div class='statstitlelng'>
<h2><?php _e('DFS Trends'); ?></h2></div><div id="dpapos" class='statstitle'><h2>WRs</h2></div>

<div class='statsfddk'>

<div class='statsform'>
<form action="<?php bloginfo('url'); ?>" method="get">
$args = array(

'include' => '1822, 1825, 1827, 1829'
wp_dropdown_pages($args); ?>

<input type="submit" name="submit" value="view" />
<script type="text/javascript">
jQuery(function($) {
<form action="<?php get_permalink('1839'); ?>" method="POST">

<select id="teammultiselect" name="start" multiple="multiple">
<option value="">Team</option>
<option value="ARI" <?php if($start == 'ARI'){echo 'selected';}?>>ARI</option>
<option value="ATL" <?php if($start == 'ATL'){echo 'selected';}?>>ATL</option>
<option value="BAL" <?php if($start == 'BAL'){echo 'selected';}?>>BAL</option>
<option value="BUF" <?php if($start == 'BUF'){echo 'selected';}?>>BUF</option>
<option value="CAR" <?php if($start == 'CAR'){echo 'selected';}?>>CAR</option>
<option value="CHI" <?php if($start == 'CHI'){echo 'selected';}?>>CHI</option>
<option value="CIN" <?php if($start == 'CIN'){echo 'selected';}?>>CIN</option>
<option value="CLE" <?php if($start == 'CLE'){echo 'selected';}?>>CLE</option>
<option value="DAL" <?php if($start == 'DAL'){echo 'selected';}?>>DAL</option>
<option value="DEN" <?php if($start == 'DEN'){echo 'selected';}?>>DEN</option>
<option value="DET" <?php if($start == 'DET'){echo 'selected';}?>>DET</option>
<option value="GB" <?php if($start == 'GB'){echo 'selected';}?>>GB</option>
<option value="HOU" <?php if($start == 'HOU'){echo 'selected';}?>>HOU</option>
<option value="IND" <?php if($start == 'IND'){echo 'selected';}?>>IND</option>
<option value="JAX" <?php if($start == 'JAX'){echo 'selected';}?>>JAX</option>
<option value="KC" <?php if($start == 'KC'){echo 'selected';}?>>KC</option>
<option value="LA" <?php if($start == 'LA'){echo 'selected';}?>>LA</option>
<option value="MIA" <?php if($start == 'MIA'){echo 'selected';}?>>MIA</option>
<option value="MIN" <?php if($start == 'MIN'){echo 'selected';}?>>MIN</option>
<option value="NE" <?php if($start == 'NE'){echo 'selected';}?>>NE</option>
<option value="NO" <?php if($start == 'NO'){echo 'selected';}?>>NO</option>
<option value="NYG" <?php if($start == 'NYG'){echo 'selected';}?>>NYG</option>
<option value="NYJ" <?php if($start == 'NYJ'){echo 'selected';}?>>NYJ</option>
<option value="OAK" <?php if($start == 'OAK'){echo 'selected';}?>>OAK</option>
<option value="PHI" <?php if($start == 'PHI'){echo 'selected';}?>>PHI</option>
<option value="PIT" <?php if($start == 'PIT'){echo 'selected';}?>>PIT</option>
<option value="SD" <?php if($start == 'SD'){echo 'selected';}?>>SD</option>
<option value="SEA" <?php if($start == 'SEA'){echo 'selected';}?>>SEA</option>
<option value="SF" <?php if($start == 'SF'){echo 'selected';}?>>SF</option>
<option value="TB" <?php if($start == 'TB'){echo 'selected';}?>>TB</option>
<option value="TEN" <?php if($start == 'TEN'){echo 'selected';}?>>TEN</option>
<option value="WAS" <?php if($start == 'WAS'){echo 'selected';}?>>WAS</option>

<input type="submit" name="search" value="Find">


$dsn = 'mysql:host=localhost;dbname=press_10';
$username = 'xxx';
$password = 'yyy';


$con = new PDO($dsn, $username, $password);

} catch (Exception $ex) {

echo 'Not Connected '.$ex->getMessage();

$tableContent = '';
$start = '';
$selectStmt = $con->prepare('SELECT * FROM WR_TeamAnalyzer');
$rows = $selectStmt->fetchAll();

foreach ($rows as $row)
$tableContent = $tableContent.'<tr>'.
'<td>' . $row['WR'] .'</td>'
.'<td>' . $row['Team'] . '</td>'
.'<td>' . $row['WK'] . '</td>'
.'<td>' . $row['Opp'] . '</td>'
.'<td>' . $row['T'] . '</td>'
.'<td>' . $row['Yds'] . '</td>'
.'<td>' . $row['TDs'] . '</td>'
.'<td>' . $row['Fpts'] . '</td>';

$start = $_POST['start'];
$tableContent = '';
$selectStmt = $con->prepare('SELECT * FROM WR_TeamAnalyzer WHERE TEAM like :start');


$rows = $selectStmt->fetchAll();

foreach ($rows as $row)
$tableContent = $tableContent.'<tr>'.
'<td>' . $row['WR'] .'</td>'
.'<td>' . $row['Team'] . '</td>'
.'<td>' . $row['WK'] . '</td>'
.'<td>' . $row['Opp'] . '</td>'
.'<td>' . $row['T'] . '</td>'
.'<td>' . $row['Yds'] . '</td>'
.'<td>' . $row['TDs'] . '</td>'
.'<td>' . $row['Fpts'] . '</td>';



<div class='wpDataTablesWrapper'><table id='trend' class='wpDataTable dataTable'><thead><tr><th>WR</th><th>Tm</th><th>Wk</th><th>Opp</th><th>T</th><th>Yds</th><th>TDs</th><th>Fpts</th></tr></thead>


echo $tableContent;



Answer Source

I would recommend to change the select tag name to name="start[]" so then you can get those values in an array with $POST['start'].

Then, as I understand your question, you want to display Players of teams that are not selected so we need to structure the query to be something like this:

                                OR TEAM NOT LIKE start[1]%...

So to keep with your approach, we would make a query string and the array of parameters with a for:

$query = "SELECT * FROM WR_TeamAnalyzer WHERE TEAM LIKE 'nothing'";
$array = array();
foreach ($_POST['start'] as $st)
    $query .= " OR TEAM NOT LIKE :start" . $st;
    $array[':start' . $st] = $st . '%';

Then just run:

$selectStmt = $con->prepare($query);

And this should work.

I would also recommend you to read about the MVC architecture, it helps a lot while developing web applications. There are many tutorials and approaches that explain this very well, this is a good one designed for PHP.


For multiple select

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download