代码之家  ›  专栏  ›  技术社区  ›  Rataiczak24

不使用循环显示查询中的PHP数据

  •  0
  • Rataiczak24  · 技术社区  · 7 年前

    我有一个查询,得到一些数据,我需要显示在我的网页上。我的问题是:

    $sql1 = "SELECT CAST([Series] AS INT) AS Series
          ,[Master Supplier Title]
          ,[Fund Name]
          ,CAST([Agreement_ID] AS INT) AS Agreement_ID
          ,CAST([Tier_ID] AS INT) AS Tier_ID
          ,[Retro_to_1]
          ,CAST([Payments per Year] AS INT) AS [Payments per Year]
          ,[Condition Unit of Measure]
          ,CAST([Condition Minimum] AS INT) AS [Condition Minimum]
          ,CAST([Condition Maximum] AS INT) AS [Condition Maximum]
          ,CAST([Incentive Multiplier] AS DEC(5,4)) AS [Incentive Multiplier]
      FROM [Test].[dbo].[vExample]
      WHERE [Master Supplier Title] = '$supp' AND [Series] = 1 AND [Fund Name] = '400P' AND [Agreement_ID] = 2
      ORDER BY [Master Supplier Title]";
    

    <?php foreach ($pdo->query($sql1) as $supp11) { ?>
                <label>Agreement ID:</label><input value="<?php echo $supp11['Agreement_ID'];?>" readonly><br><br><br>
                <table>
                    <tr>
                    <thead>
                        <th></th>
                        <th></th>
                        <th></th>
                        <th></th>
                        <th></th>
                        <th></th>
                        <th></th>
                    </thead>
                    </tr>
                    <?php foreach ($pdo->query($sql1) as $supp22) { ?>
                    <tr>
                    <tbody>
                        <td><?php echo $supp22['Tier_ID'];?></td>
                        <td><?php echo $supp22['Incentive Multiplier'];?></td>
                        <td><?php echo $supp22['Condition Minimum'];?></td>
                        <td><?php echo $supp22['Condition Maximum'];?></td>
                        <td><?php echo $supp22['Condition Unit of Measure'];?></td>
                        <td><?php echo $supp22['Retro_to_1'];?></td>
                        <td><?php echo $supp22['Payments per Year'];?></td>
                    </tbody>
                    </tr>
                    <?php } ?>
                </table>
                <?php } ?>
    

    变量 $supp 你们看到的是我在之前的一些代码中从下拉选择中得到的值。

    2 回复  |  直到 7 年前
        1
  •  1
  •   Justin Pearce    7 年前

    我认为问题是你有一个双循环,导致重复。如果您想将所有结果作为一个集合返回到进程,那么应该使用一个准备好的语句。

    <?php 
    $stmt = $pdo->prepare($sql1);
    $stmt->execute();
    $results = $stmt->fetchAll();
    ?>
    <label>Agreement ID:</label><input value="<?php echo $results[0]['Agreement_ID'];?>" readonly><br><br><br>
    <table>
    <tr>
    <thead>
        <th></th>
        <th></th>
        <th></th>
        <th></th>
        <th></th>
        <th></th>
        <th></th>
    </thead>
    </tr>
    <?php foreach ($results as $supp22) { ?>
    <tr>
    <tbody>
        <td><?php echo $supp22['Tier_ID'];?></td>
        <td><?php echo $supp22['Incentive Multiplier'];?></td>
        <td><?php echo $supp22['Condition Minimum'];?></td>
        <td><?php echo $supp22['Condition Maximum'];?></td>
        <td><?php echo $supp22['Condition Unit of Measure'];?></td>
        <td><?php echo $supp22['Retro_to_1'];?></td>
        <td><?php echo $supp22['Payments per Year'];?></td>
    </tbody>
    </tr>
    <?php } ?>
    </table>
    

    呼叫 fetchAll() 应该返回一个可以循环的结果数组。查看索引0处的第一条记录应该可以得到 Agreement_ID 顶部标签的参数。

        2
  •  0
  •   Typel    7 年前

    DISTINCT 在查询中确保每行只有一个副本,但这可能太慢了,具体取决于数据库的大小和结构。我可能会主张在你的周围添加一个IF语句 <tbody>

    <?php
    $shown = array(); // keeps track of which values were already displayed
    foreach ($pdo->query($sql1) as $supp22) { 
      // this just has to be a value (or combination of values) 
      // that you don't want repeated.
      // Since I don't know the exact structure of your tables, I've just 
      // appended all the variables together into one long string. If 'Tier_ID'
      // is a unique row identifier you'd want to exclude it from here.
      $dont_repeat = $supp22['Tier_ID'].$supp22['Incentive Multiplier'].$supp22['Condition Minimum'].$supp22['Condition Maximum'].$supp22['Condition Unit of Measure'].$supp22['Retro_to_1'].$supp22['Payments per Year'];
      // here we only display the row if it wasn't already shown
      if ( !in_array($dont_repeat, $shown) ) {
        // add it to our $shown array so we don't display the same data again
        $shown[] = $dont_repeat;
        ?>
        <tr>
        <tbody>
        <td><?php echo $supp22['Tier_ID'];?></td>
        <td><?php echo $supp22['Incentive Multiplier'];?></td>
        <td><?php echo $supp22['Condition Minimum'];?></td>
        <td><?php echo $supp22['Condition Maximum'];?></td>
        <td><?php echo $supp22['Condition Unit of Measure'];?></td>
        <td><?php echo $supp22['Retro_to_1'];?></td>
        <td><?php echo $supp22['Payments per Year'];?></td>
        </tbody>
        </tr>
        <?php 
        } 
      ?>
      </table>
      <?php 
      } 
    /* be tidy and */ unset($shown, $dont_repeat);
    ?>