du2986 2019-03-24 22:38
浏览 98
已采纳

如何将案例切换mySQL查询的结果回显到span标记内的表中

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.

  • 写回答

1条回答 默认 最新

  • douzhizao0270 2019-03-24 23:49
    关注

    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>
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题？

悬赏问题

  • ¥15 #MATLAB仿真#车辆换道路径规划
  • ¥15 java 操作 elasticsearch 8.1 实现 索引的重建
  • ¥15 数据可视化Python
  • ¥15 要给毕业设计添加扫码登录的功能！！有偿
  • ¥15 kafka 分区副本增加会导致消息丢失或者不可用吗？
  • ¥15 微信公众号自制会员卡没有收款渠道啊
  • ¥100 Jenkins自动化部署—悬赏100元
  • ¥15 关于#python#的问题：求帮写python代码
  • ¥20 MATLAB画图图形出现上下震荡的线条
  • ¥15 关于#windows#的问题：怎么用WIN 11系统的电脑 克隆WIN NT3.51-4.0系统的硬盘