How to Filter mysql/php table data using a form

Question

Here is the background of what I am doing:

A mySql table is being queried by PHP to present an NFL Datatable on a website.
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).
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>
 
 <div class='statsform'>
        <form action="<?php bloginfo('url'); ?>" method="get">
        <?php  
         $args = array(
          
          'include' => '1822, 1825, 1827, 1829'         
      );
      wp_dropdown_pages($args); ?>
        
        <input type="submit" name="submit" value="view" />
        </form>
      <script type="text/javascript">
                                    jQuery(function($) {
                      $('#teammultiselect').multiselect();
                                    });
                                </script>
        <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>
            </select>
            
            <input type="submit" name="search" value="Find">
      </div></div>
      </form>
    
<?php

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

try{
    
    $con = new PDO($dsn, $username, $password);
    $con->setAttribute(PO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
} catch (Exception $ex) {

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

$tableContent = '';
$start = '';
$selectStmt = $con->prepare('SELECT * FROM WR_TeamAnalyzer');
$selectStmt->execute();
$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>';
}

if(isset($_POST['search']))
{
$start = $_POST['start'];
$tableContent = '';
$selectStmt = $con->prepare('SELECT * FROM WR_TeamAnalyzer WHERE TEAM like :start');
$selectStmt->execute(array(
        
         ':start'=>$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>
                
                <?php
                
                echo $tableContent;
                
                ?>
                
            </table>

Your code formatting is so bad you are reducing the chance of getting any help. Take some time to edit your post to clean up your code formatting so it is readable. — jwpfox, Nov 05 '16 at 10:05
Moyom solved this question for me, thank you Moyom for the detailed write up. I feel like it is rare to come across people on stackoverflow like Moyom. — Rainman Conroy, Nov 05 '16 at 11:48
You will increase your chances of coming across good people like @Moyom if you put serous effort into making your question as well considered and formed as you can manage. If you show a lack of effort in constructing a question you cannot be surprised if people show a lack of effort in response. — jwpfox, Nov 05 '16 at 12:45
Well I am very new to PHP and coding in general, my "mess of code" was unintentional and actually I got that code from a tutorial that did everything but the multiselect, so as far as the tutorial, it was right. I thought this was a place known to help people, thats why I posted the question. Your negativity jwpfox is what makes peoples questions go cold. — Rainman Conroy, Nov 05 '16 at 22:22
If you see people providing 'this is how you could do this better' feedback as negativity then you are setting yourself up for a sad and unhappy life. Learning how to accepting constructive feedback is a key item to add to your life skills toolbox. Your attack on the SO population as 'negative' is not going to win you friends or help you get the most out of SO. Have a deep think about which of us is displaying a negative attitude here. End of convo. — jwpfox, Nov 05 '16 at 22:30

score 1 · Accepted Answer · edited May 23 '17 at 10:32

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:

SELECT * FROM WR_TeamAnalyzer WHERE TEAM NOT LIKE start[0]%
                                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);
$selectStmt->execute($array);

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.

Sources:

For multiple select

Thank you so much Moyom, everything works perfectly! And the way you walked through the reasons for all of your code, really helped me out. Thank you very much for your response. — Rainman Conroy, Nov 05 '16 at 11:45

How to Filter mysql/php table data using a form

1 Answers1