Member Avatar for lewis92

Hi I populate a drop down menu like this:

<select name="mySelect"> 
        <?php $result= mysql_query('SELECT DISTINCT Year FROM MonthlySales'); ?> 
        <?php while($row= mysql_fetch_assoc($result)) { ?> 
            <option value="<?php echo htmlspecialchars($row['Year']);?>"> 
                <?php echo htmlspecialchars($row['Year']); ?> 
            </option> 
        <?php } ?> 
    </select>

Then build a HTML table like this:

<?php

    while( $row = mysql_fetch_row($results) )   {
        echo "<tr>";
        foreach($row as $cell)        //Display all data in this row of user data...
        echo "<td>$cell</td>";
        echo "</tr>\n";
    }

    echo "</table>";
    mysql_close($conn);

    ?>

My connection details look like so:

 <?PHP
        $conn = mysql_connect('localhost', 'root', 'root');
        @mysql_select_db('localDB');

        $query="SELECT * FROM MonthlySales";
        $results = mysql_query ( $query, $conn);

        echo "<table border='1'>";
        echo "<tr><td>ID</td><td>ProductCode</td><td>Month</td><td>Year</td><td>SalesVolume</td></tr>";
    ?>

Now I want to only display the rows in the table with the year that the user has selected from my drop down box, I'm not sure how to link both the drop down box and the table together, can someone explain please.

  • 2 Contributors
  • 5 Replies
  • 1K Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

Good direction from @diafol: submit the dropdown, read the chosen year server-side, and filter your query. One important update though: the old mysql_* API you are using was deprecated in PHP 5.5 and removed in PHP 7. Use mysqli or PDO with prepared statements so user input never gets concatenated into SQL. It is safer and future-proof. (php.net)

Here is a compact single-file example using PDO. It builds the dropdown from distinct years, keeps the selected value after submit, and renders only matching rows.

<?php
$pdo = new PDO(
  'mysql:host=localhost;dbname=localDB;charset=utf8mb4',
  'root', 'root',
  [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
   PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]
);

$years = $pdo->query('SELECT DISTINCT `Year` FROM MonthlySales ORDER BY `Year` DESC')
             ->fetchAll(PDO::FETCH_COLUMN);

$selected = filter_input(INPUT_POST, 'year', FILTER_VALIDATE_INT);
$rows = [];

if ($selected !== null && $selected !== false) {
  $stmt = $pdo->prepare(
    'SELECT ID, ProductCode, Month, `Year`, SalesVolume
     FROM MonthlySales
     WHERE `Year` = ?
     ORDER BY Month'
  );
  $stmt->execute([$selected]);
  $rows = $stmt->fetchAll();
}
?>
<form method="post">
  <label for="year">Choose year:</label>
  <select id="year" name="year">
    <?php foreach ($years as $y): ?>
      <option value="<?php echo htmlspecialchars($y); ?>"
              <?php echo ($selected == $y ? 'selected' : ''); ?>>
        <?php echo htmlspecialchars($y); ?>
      </option>
    <?php endforeach; ?>
  </select>
  <button type="submit">Filter Results</button>
</form>

<?php if ($rows): ?>
<table>
  <thead><tr><th>ID</th><th>ProductCode</th><th>Month</th><th>Year</th><th>SalesVolume</th></tr></thead>
  <tbody>
  <?php foreach ($rows as $r): ?>
    <tr>
      <td><?php echo htmlspecialchars($r['ID']); ?></td>
      <td><?php echo htmlspecialchars($r['ProductCode']); ?></td>
      <td><?php echo htmlspecialchars($r['Month']); ?></td>
      <td><?php echo htmlspecialchars($r['Year']); ?></td>
      <td><?php echo htmlspecialchars($r['SalesVolume']); ?></td>
    </tr>
  <?php endforeach; ?>
  </tbody>
</table>
<?php endif; ?>

Notes: use prepared statements instead of manual escaping, and validate the posted year as an integer as shown with filter_input. Keep the option selected by echoing the selected attribute for the matching <option>. This mirrors the approach @diafol described, but in a modern, safe way. (php.net)

Recommended Answers

All 5 Replies

Member Avatar for Member #120589

For the select:

<?php 
$result= mysql_query('SELECT DISTINCT Year FROM MonthlySales');
$sel = "\n<select name=\"mySelect\">"; 
while($row= mysql_fetch_assoc($result)) {
      $op = htmlspecialchars($row['Year']);
      $sel .= "\n\t<option value=\"$op\">$op</option>"; 
}
$sel .= "\n</select>\n"; 
?>

Then just place the $selvariable into your html table

Member Avatar for lewis92

Thank you for your response, it's appreciated.

I don't follow what I'm doing with that new variable though, does it go in my while loop?

edit: I'm having trouble implementing that, my drop down box is empty once I add that?

Edited by lewis92
Member Avatar for Member #120589

The dropdown will need to be submitted from its form. You pick this up in a different page (the form handler) or the same page if you must by the variable $_POST['mySelect'].

Clean this variable with mysql_real_escape_string() or intval() if you're passing an integer (year) and then use it in your SQL that retrieves the html table for that selected year.

Your form (e.g.):

<form method="post">
<label for="mySelect">Choose the year:</label>
<?php echo $sel;?>
<input type="submit" value="Filter Results" name="submityear" id="submityear" />
</form>
Edited by Member #120589
Member Avatar for lewis92

Thanks, I'm guessing I use the WHERE command in my sql to return the rows with that certain year. Can I assign

$_POST['mySelect']

to another value to be passed into that SQL, sorry for all the questions, I don't normally work with sql and php.

Member Avatar for Member #120589

Yes as I said you must clean the var first.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.