How to echo results from a case switch mySQL query into a table within a span tag

Question

I need to take the users input from the select box for Allergen1 once an option is selected and the user clicks submit a query will run based upon the selected value of the select box. I hope to achieve this via the use of a case and switch to specify which query to run, from this i would like to then echo the results of the query into a table. the table being "tableleft"

-

<?php
 //create connection vars
 $dbhost = '-';
 $dbuser ='-';
 $dbpass = '-';
 $db = '-';
 //create connection
 $con=mysql_connect($dbhost, $dbuser, $dbpass);
 mysql_select_db($db);

 switch($_GET['Allergen1']){
    case 'Wheat':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Wheat = 0");
       break;
    case 'Spelt':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Spelt = 0");
       break;
    case 'Kamut':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Kamut = 0");
       break;
    case 'Rye':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Rye = 0");
       break;
    case 'Barley':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Barley = 0");
       break;
    case 'Oats':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Oats = 0");
       break; 
    case 'Fish':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Fish = 0");
       break;
    case 'Crustaceans':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Crustaceans = 0");
       break;
    case 'Molluscs':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Molluscs = 0");
       break;
    case 'Eggs':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Eggs = 0");
       break;
    case 'Soybeans':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Soybeans = 0");
       break;
    case 'Milk':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Milk = 0");
       break;
    case 'Almonds':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Almonds = 0");
       break;
    case 'Hazelnut':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Hazelnut = 0");
       break;
    case 'Walnut':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Walnut = 0");
       break;
    case 'CashewNut':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE CashewNut = 0");
       break;
    case 'PecanNut':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE PecanNut = 0");
       break;
    case 'BrazilNut':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE BrazilNut = 0");
       break;
    case 'Pistacio':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Pistacio = 0");
       break;
    case 'Macadamia':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Macadamia = 0");
       break;
    case 'Peanuts':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Peanuts = 0");
       break;
    case 'Celery':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Celery = 0");
       break;
    case 'Mustard':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Mustard = 0");
       break;
    case 'Sesame':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Sesame = 0");
       break;
    case 'Sulphites':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Sulphites = 0");
       break;
    case 'Lupin':
       mysql_query("SELECT DishName FROM AllergenDishMenu WHERE Lupin = 0");
       break;
    }
?>


 <!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="Styling.css">
<meta charset="utf-8">
<title>Allergen Menu</title>
</head>
<body>

  <h1>Allergen Menu</h1>


  <div id ="container">
    <span id ="tableleft">
        <!-- NEED TO ECHO RESULT OF THE QUERY HERE BASED UPON THE SELECT OPTION FROM THE FORM BELOW !-->
    </span>
    <span id ="tableright">
        <Form method="post">
            <SELECT name="Allergen1">
                <option value="Wheat">Wheat</option>
                <option value="Spelt">Spelt</option>
                <option value="Kamut">Kamut</option>
                <option value="Rye">Rye</option>
                <option value="Barley">Barley</option>
                <option value="Oats">Oats</option>
                <option value="Fish">Fish </option>
                <option value="Crustaceans">Crustaceans</option>
                <option value="Molluscs">Molluscs</option>
                <option value="Eggs">Eggs</option>
                <option value="Soybeans">Soybeans</option>
                <option value="Milk">Milk</option>
                <option value="Almonds">Almonds</option>
                <option value="Hazelnut">Hazelnut</option>
                <option value="Walnut">Walnut</option>
                <option value="CashewNut">Cashew Nut</option>
                <option value="PecanNut">Pecan Nut</option>
                <option value="BrazilNut">Brazil Nut</option>
                <option value="Pistacio">Pistacio</option>
                <option value="Macadamia">Macadamia</option>
                <option value="Peanuts">Peanuts</option>
                <option value="Celery">Celery</option>
                <option value="Mustard">Mustard</option>
                <option value="Sesame">Sesame</option>
                <option value="Sulphites">Sulphites</option>
                <option value="Lupin">Lupin</option>
            </SELECT>
            <input type="submit">
        </Form>
    </span>
  </div>
  </html> 
</body>

I expect the query once ran to output via an echo to the "table left" any help with this would be appreciated.

The use of `mysql` keyword has been depreciated, all replaced with `mysqli` — Jonathan K, Mar 24 '19 at 22:44
[**`mysql_query`** I hope you have read the warning in the red box there and that you use some other resource to learn from if you haven't.](https://www.php.net/manual/en/function.mysql-query.php) — Prix, Mar 24 '19 at 22:44
Thanks for the feedback I believe the PDO_mysql extension would be best in case my future storage method changes. — user11252056, Mar 25 '19 at 16:56

Hikafer tsamsiyu · Accepted Answer · 2019-03-27T23:38:21.507

First you should use MySQLi or PDO_MySQL extension.

And you don't need a switch case.

Here a full example:

<!DOCTYPE html>
    <html>
    <head>
      <title>Query</title>
      <!-- Bootstrap -->
      <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    </head>
    <body>  
      <div class="container p-2">

       <h2>Allergen Menu</h2><hr class="mb-4">

       <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
        <div class="form-group">
          <label for="exampleFormControlSelect1">Please Select</label>
          <select name="allergen" class="form-control" id="exampleFormControlSelect1">
            <option value="wheat">Wheat</option>
            <option value="Spelt">Spelt</option>
            <option value="kamut">Kamut</option>
            <option value="Rye">Rye</option>
            <option value="Barley">Barley</option>
            <option value="Oats">Oats</option>
            <option value="Fish">Fish </option>
            <option value="Crustaceans">Crustaceans</option>
            <option value="Molluscs">Molluscs</option>
            <option value="Eggs">Eggs</option>
            <option value="Soybeans">Soybeans</option>
            <option value="Milk">Milk</option>
            <option value="Almonds">Almonds</option>
            <option value="Hazelnut">Hazelnut</option>
            <option value="Walnut">Walnut</option>
            <option value="CashewNut">Cashew Nut</option>
            <option value="PecanNut">Pecan Nut</option>
            <option value="BrazilNut">Brazil Nut</option>
            <option value="Pistacio">Pistacio</option>
            <option value="Macadamia">Macadamia</option>
            <option value="Peanuts">Peanuts</option>
            <option value="Celery">Celery</option>
            <option value="Mustard">Mustard</option>
            <option value="Sesame">Sesame</option>
            <option value="Sulphites">Sulphites</option>
            <option value="Lupin">Lupin</option>
          </select>
        </div>
        <button type="submit" name="submit" class="btn btn-primary mb-2">Submit</button>
      </form>

      <h2 class="mt-5">Result</h2><hr class="mb-5">


      <?php
      if(isset($_POST['submit'])){

        // Connect to DB
        $dsn = 'mysql:host=localhost; dbname=Your_DB_Name; charset=utf8mb4';
        try{  
          $connection = new PDO($dsn, 'DB_username', 'DB_password');
          $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }catch(Exception $e){
          die($e->getMessage());
        }

        // Get Input data
        $allergen = htmlentities(addslashes($_POST['allergen']));

        // prepare query
        $sql = 'SELECT * FROM allergendishmenu WHERE ' . $allergen . ' = 0'; 
        $result = $connection->query($sql)->fetchAll(PDO::FETCH_OBJ);

      }
      ?>

      <table class="table table-bordered">
        <thead>
          <tr>
            <th scope="col">Dishname</th>
            <th scope="col">Allergens</th>
          </tr>
        </thead>
        <tbody>
          <?php 
          if(isset($result)){
            foreach($result as $data): ?>   
              <tr>          
                <td><?php echo $data->DishName; ?></td> 
                <td><?php echo $data->$allergen; ?></td>
              </tr>
              <?php 
            endforeach; 
          }else{
            echo '<tr><td colspan="2">No data loaded</td></tr>';
          }
          ?>  
        </tbody>
      </table>

    </div>


    </body>
    </html>

Hi there, could you please clarify where your recommendations would need inserting/applying within the code itself. Thanks very much. — user11252056, Mar 25 '19 at 00:29
@Hikafertsamsiyu you suggest him to use MySQLi/PDO but shows him an example injecting data directly to your query with no sanitization? :( what a bad example, please consider changing your sample to at least sanitize the input, before injecting it to your query. — Prix, Mar 27 '19 at 19:41
@Prix You are absolutely right. I edited my example and added `htmlentities(addslashes())` to the POST data. I didn't add a pdo prepared statement because in this "rare" case the var is for the table name and not the condition. — Hikafer tsamsiyu, Mar 27 '19 at 23:46
Yes, it is a special case and because of that I doubt those 2 functions would be of any help there, for example it wouldn't do anything to `--#`. Since the options are limited on what is acceptable for a column name, [either using a regex to validate the input](https://dev.mysql.com/doc/refman/8.0/en/identifiers.html) or [have an array with the valid options to compare to is preferred.](https://stackoverflow.com/a/13448443/342740) But the issue here is deeper within his database design being faulty. — Prix, Mar 28 '19 at 01:47

How to echo results from a case switch mySQL query into a table within a span tag

1 Answers1